SYSIBM: 基本系统编目,不建议直接访问
SYSCAT: 默认授权给Public组.只读编目视图,一般通过这个来获取编目信息
SYSSTAT: 可更新编目视图,会影响优化器的优化策略
SYSFUN: 用户定义函数
SYSPROC: 存放一组系统的存储过程
如果你需要知道具体的每个编目表所产生的作用,可以去info center里面去搜索关键字: Road map to the catalog views, 可以得到所有以syscat.开头的编目视图(一般情况下用的也是这个).当然我还自己mark了一些牛大师的书里头提到的常用的对系统编目表的SQL语句,仅供参考(飞鹏大牛的书看完了还没做笔记...他的书里面也有许多有用的SQL语句):
1. 监控缓冲池命中率:
select substr(db_name,1,8) as DB_NAME, substr(bp_name,1,8) as BP_NAME, total_hit_ratio_percent as TOTAL_HR, data_hit_ratio_percent as DATA_HR, index_hit_ratio_percent as INDEX_HR, xda_hit_ratio_percent as XDA_HR from SYSIBMADN.BP_HITRATIO where bp_name not like ‘%IBMSYSTEM%’;
2. 监控PACKAGE CACHE大小:
with dbcfg1 as (select int(value) as pckcachesz from sysibmadm.dbcfg where name ‘pckcachesz’ ) select pckcachesz as “Package Cache Size”, pkg_cache_lookups as “Lookups”, pkg_cache_inserts as “Inserts”, pkg_cache_num_overflows as “Overflows”, 100 * pkg_cache_size_top / (pckcachesz * 4096) as “%PKG Cache alloc” from dbcfg1, sysibmadm.snapdb;
3. 监控执行成本最高的SQL语句
select AGENT_ID, ROWS_SELECTED, ROWS_READ from sysibmadm.SNAPAPPL fetch first 10 rows only;
通过对比SELECT 和READ的值来进行判断.
4. 监控运行最长的SQL语句:
select substr(appl_name,1,15) as Appl_name, elapsed_time_min as Elapsed_Min, appl_status as Status, substr(auth,1,10) as auth_id, substr(inbound_comm_address,1,15) as IP_Address, substr(stmt_text,1,30) as SQL_Statement from sysibmadm.long_running_sql order by 2 desc;
5. 监控SQL准备和预编译时间最长的SQL语句:
select num_executions, average_execution_time_s(SQL语句平均执行时间), prep_time_ms(最长的SQL预编译时间), prep_time_percent(预编译时间占整个执行时间的百分比), substr(stmt_text,1,40) as SQL_TEXT from sysibmadm.query_prep_cost where average_execution_time_s > 0 order by prep_time_percent desc;
6. 监控执行次数最多的SQL语句:
select num_executions as Num_Excus, average_execution_time_s as Avg_times_s, stmt_sorts as Num_Sorts, sorts_per_execution as Sort_per_stmt, substr(stmt_text,1,35) as SQL_stmt from sysibmadm.top_dynamic_sql where num_execution > 0 order by 1 desc fetch first 5 rows only;
7. 监控排序次数最多的SQL语句:
select STMT_SORTS, SORTS_PER_EXECUTION, substr(stmt_text,1,60) as STMT_TEXT from sysibmadm.top_dynamic_sql by STMT_SORTS desc fetch first 5 rows only;
8. 监控LOCK WAIT时间:
select substr(ai.appl_name,1,20) as appl_name, substr(ai.primary_auth_id,1,10) as auth_id, ap.lock_waits as lock_waits, ap.lock_waits_time / 1000 as Total_waits_s, ap.lock_waits_time / ap.lock_waits as Avg_waits_ms from sysibmadm.snapappl_info ai, sysibmadm.snapappl ap where ai.agent_id = ap.agent_id and ap.lock_waits > 0;
9. 监控LOCK CHAIN:
select substr(ai_h.appl_name,1,0) as Hold_App, substr(ai_h.primary_auth_id,1,10) as Holder, substr(lw.appl_name,1,10) as Wait_app, substr(lw.authid,1,10) as Waiter, lw.lock_mode, lw.lock_object_type, substr(lw.tabname,1,10) as TabName, substr(lw.tabschema,1,10) as Schema, timestampdiff(2,char(lw.snapshot_timestamp - lw.lock_wait_start_time)) as waiting_s from sysibmadm.lockwaits lw, sysibmadm.snapappl_info ai.h where lw.agent_id_holding_lk = ai_h.agent_id;
10. 监控锁内存使用:
with dbcfg1 as (select float(int(value) * 4096) as locklist from sysibmadm.dbcfg where name = ‘locklist’), dbcfg2 as (select float (int(value) ) as maxlock from sysibmadm.dbcfg where name = ‘maxlock’) select dec ((lock_list_in_use/locklist) * 100,4,1) as % Lock_List, dec((lock_list_in_use/(locklist*(maxlocks/100))*100),4,1) as %_to_Maxlock(Maxlock的配置大小是多少), appls_cur_cons as Number_of_cons, lock_list_in_use/appl_cur_cons as Avg_Lock_Mem_per_Con_bytes from dbcfg1, dbcfg2, sysibmadm.snapdb;
11. 监控锁升级,死锁和锁超时:
select substr(ai.appl_name,1,10) as Application, substr(ai.primary_auth_id,1,10) as AuthID, int(ap.locks_held) as Locks, int(ap.lock_escals) as Escalations, int(ap.lock_timeouts) as Lock_timeouts, int(ap.deadlocks) as Deadlocks, int(ap.int_deadlock_rollbacks) as Dlock_Victim, substr(inbound_comm_address,1,15) as IP_Address from sysibmadm.snapappl ap, sysibmadm.snapappl_info ai where ap.agent_id = ai.agent_id;
12. 监控全表扫描的SQL:
select substr(authid,1,10) as authid, substr(appl_name,1,20) as appl_name, percent_rows_selected from sysibmadm.appl_performance;
13. 检查page cleaner是否足够:
wuth db_snap as (select float(pool_drty_pg_steal_clns) as pg_steal, float(pool_drty_pg_thrsh_clns) as chg_pg_thrsh, float(pool_lsn_gap_clns) as softmax, float(pool_drty_pg_steal_clns + pool_drty_pg_thrsh_cls + pool_lsn_gap_clns) as total_clns from sysibmadm.snapdb) select dec(( pg_steal/toal_clns) * 100,4,1) as %steals, dec(( chg_pg_thrsh/total_clns) * 100,4,1) as %Threshold, dec(( softmax / total_clns) * 100,4,1) as %Softmax from db_snap;
14. 监控PREFETCHER是否足够:
with bp_snap as (select substr(br_name,1,30) as bp_name, unread_prefetch_pages, pool_async_data_reads + pool_async_index_reads as async_reads, pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads as Total_Reads from sysibmadm.snapbp where bp_name not like ‘IBMSYSTEM%’) select bp_name, unread_prefetch_pages, dec (100 *(Total_reads * Async_reads ) / Total_reads,5,2) as %Synch_Reads, dec (100 * unread_prefetch_pages /Total_reads, 5,2) as %Unread_Pages(异步I/O服务器预存取到缓冲池没有使用的页) from bp_snap;
15. 监控数据库内存使用(未启用STMM情况下):
select pool_id, pool_secondary_id, pool_current_size, pool_watermark from sysibmadm.snapdb_memory_pool;
16. 监控日志使用情况:
select int (total_log_used/1024/1024) as Log_used_Meg, int(total_log_available/1024/1024) as Log_Space_Free_meg, int((float(total_log_used) / float(total_log_used + total_log_available)) * 100) as Pct_Used, int (tot_log_used_top/1024/1024) as Max_Log_Used_Meg, int(sec_log_used_top/1024/1024) as Max_Sec_Used_Meg(日志使用的高水位), int(sec_logs_allocated) as Secondaries from sysibmadm.snapdb;
17. 监控占用日志空间最旧的交易:
select substr(ai.appl_status,1,20) as Status, substr(ai.primary_auth_id,1,10) as Authid, substr(ai.appl_name,1,15) as Appl_name, int(ap.UOW_LOG_SPAC_USED/1024/1024) as Log_Used_MB, Int(ap.appl_idle_time/60) as Idle_for_min, ap.appl_con_time as Connected_Since from sysibmadm.snapdb db, sysibmadm.snapappl ap, sysibmadm.snapappl_info ai where ai.agent_id = db.APPL_ID_OLDEST_XACT and ap.agent_id = ai.agent_id;
18. 监控存储路径:
select substr(type,1,20) as type, substr(path,1,50) as path from sysibmadm.dbpaths order by type;
19. 监控表空间使用情况:
select substr(tbsp_name,1,18), tbsp_type, tbsp_free_size_kb, tbsp_utilization_percent from sysibmadm.tbsp_utilization;
20. 利用系统编目表写脚本:
例1: 以表名为名称大批量导出表:
select ‘export to ‘||tabname||’.ixf of ixf select * from ‘||rtrim(tabschema)||’.’||tabname||’;’ from syscat.tables where type=’T’ and tabschema not in (‘SYSIBM’, ‘SYSCAT’, ‘SYSSTAT’, ‘SYSTOOLS’)”
例2: 大批量授权:
select ‘grant select on table ‘||rtrim(tabschema)||’.’||tabname||’ to user username; ‘ from syscat.tables where type = ‘T’ and tabeschema not in (‘SYSIBM’, ‘SYSCAT’, ‘SYSSTAT’, ‘SYSTOOLS’)”