监控缓冲池命中率

select substr(db_name, 1, 10)as db_name, substr(bp_name, 1, 20)as

      bp_name, data_hit_ratio_percent, index_hit_ratio_percent,

      total_hit_ratio_percent

from sysibmadm.bp_hitratio

where bp_name not like 'IBMSYSTEM%'

select * from sysibmadm.bp_hitratio

监控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;

select DECIMAL(1 - ( PKG_CACHE_INSERTS / PKG_CACHE_LOOKUPS ), 3, 2)

      as PKG_CACHE_HIT, PKG_CACHE_NUM_OVERFLOWS,

      PKG_CACHE_SIZE_TOP

from SYSIBMADM.SNAPDB;

监控执行成本最高的sql语句

select agent_id,rows_selected,rows_read from sysibmadm.snapappl fetch first 10 rows only;

监控运行最长的sql语句

select substr(appl_name, 1, 15)as Appl_name, elapsed_time_min as

      "Elapsed Min.", appl_status as "Status ",

      substr(authid, 1,10)as auth_id,

      substr(inbound_comm_address, 1, 15)as "IP Address",

      substr(stmt_text, 1, 300)as "SQL Statement"

from sysibmadm.long_running_sql

order by 2 desc;

select * from sysibmadm.long_running_sql order by 2 desc;

监控sql准备和预编译时间最长的sql语句

average_execution_time_s sql语句平均执行时间

prep_time_ms 最长的sql预编译时间

prep_time_precent 预编译时间占整个执行时间的百分比

select NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S, PREP_TIME_MS ,

      PREP_TIME_PERCENT, substr(STMT_TEXT, 1, 40)

from sysibmadm.query_prep_cost

where AVERAGE_EXECUTION_TIME_S >0

order by PREP_TIME_PERCENT desc;

监控执行次数最多的sql语句

Select num_executions "Num Execs", average_execution_time_s as "Avg

      Time(sec)", stmt_sorts as "Num Sorts", sorts_per_execution

      as "Sorts Per Stmt", substr(stmt_text, 1, 35)as "SQL Stmt"

from sysibmadm.top_dynamic_sql

where num_executions>0

order by 2 desc

fetch first 5 rows only;

监控排序次数最多的sql语句

select stmt_sorts, sorts_per_execution, substr(stmt_text, 1, 60)as

      stmt_text

from SYSIBMADM.TOP_DYNAMIC_SQL

order by stmt_sorts

fetch first 5 rows only;

监控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_wait_time /1000 as "Total Wait(s)",

(ap.lock_wait_time/ap.lock_waits) as "Avg Wait(ms)" from sysibmadm.snapappl_info ai,sysibmadm.snapappl ap

where ai.agent_id=ap.agent_id and ap.lock_waits>0;

监控LOCK chain 可以定位谁持有锁，谁在等待锁

select substr(ai_h.appl_name, 1, 10)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;

select substr(ai_h.appl_name,1,10) as "Hold App", substr(ai_h.primary_auth_id,1,10) as "Holder", lw.agent_id_holding_lk as "Holder Agent id", substr(lw.authid,1,10) as "Waiter", lw.AGENT_ID as "Waiter Agent id", 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 "Waitting (s)"

from sysibmadm.lockwaits lw, sysibmadm.snapappl_info ai_h where lw.agent_id_holding_lk = ai_h.agent_id;

显示waiter和holder的SQL,同时可以按照等待时间排序

select

substr(ai_h.appl_name,1,10) as hold_app,

substr(ai_h.primary_auth_id,1,10) as holder,

lw.agent_id_holding_lk as holder_agent_id,

substr(lw.authid,1,10) as waiter,

lw.AGENT_ID as waiter_agent_id,

lw.lock_mode,

lw.lock_object_type,

substr(lw.tabname,1,10) as tabname,

substr(lw.tabschema,1,10) as schema,

substr(s2.STMT_TEXT, 1, 100) as holder_stmt,

substr(s1.STMT_TEXT, 1, 100) as waiter_stmt,

timestampdiff(2,char(lw.snapshot_timestamp - lw.lock_wait_start_time)) as waitting_s

from sysibmadm.lockwaits lw,

sysibmadm.snapappl_info ai_h,

SYSIBMADM.SNAPSTMT s1,

SYSIBMADM.SNAPSTMT s2

where lw.agent_id_holding_lk = ai_h.agent_id and s1.agent_id = lw.AGENT_ID and s2.agent_id = lw.agent_id_holding_lk

order by waitting_s desc;

监控锁内存使用

with dbcfg1 as

 (select float(int(value)*4096)as locklist

 from sysibmadm.dbcfg

 where name='locklist' ), dbcfg2 as

 (select float(int(value)*4096)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*(maxlock/100))*100), 4,

         1)as "% to Maxlock", appls_cur_cons as "Number of Cons",

         lock_list_in_use/appls_cur_cons as "Avg Lock Mem Per Con (bytes)"

 from dbcfg1, dbcfg2, sysibmadm.snapdb;

监控锁升级、死锁和锁超时

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;

监控全表扫描的SQL

select AGENT_ID,substr(authid,1,10) as authid,substr(appl_name,1,20) as appl_name,percent_rows_selected from sysibmadm.appl_performance;

检查PAGE CLEANERS 是否足够

with 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_clns

         +pool_lsn_gap_clns)as total_clns

 from sysibmadm.snapdb)

select dec((pg_steal/total_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;

检查PREFETCHER 是否足够

with bp_snap as

 (select substr(bp_name, 1, 30)as bp_name, unread_prefetch_pages,

         pool_async_data_reads+pool_async_index_reads as

         async_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"

from bp_snap;

WITH BPMETRICS AS (

 SELECT bp_name, unread_prefetch_pages,

         pool_async_data_reads+pool_async_index_reads as

         sync_reads,

         pool_temp_data_p_reads+pool_temp_index_p_reads as

         total_reads

 FROM TABLE(MON_GET_BUFFERPOOL('', -2))AS METRICS)

SELECT VARCHAR(bp_name, 20)AS bp_name, unread_prefetch_pages,

CASE

WHEN total_reads > 0

THEN dec(100*(total_reads - sync_reads)/ total_reads, 5, 2)

ELSE NULL END AS "% Synch Reads",

CASE

WHEN total_reads > 0

THEN dec(100* unread_prefetch_pages/total_reads , 5, 2)

ELSE NULL END AS "% unread pages"

FROM BPMETRICS;

监控数据库内存使用

select pool_id,pool_secondary_id,pool_cur_size,pool_watermark from sysibmadm.snapdb_memory_pool

监控日志使用情况

select DBPARTITIONNUM,int(total_log_used/1024/1024)as "Log Used (Mb)", int(

      total_log_available/1024/1024)as "Log Space Free(Mb)", 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 (Mb)", int(

      sec_log_used_top/1024/1024)as "Max Sec. Used (Mb)", int(

      sec_logs_allocated)as "Secondaries"

from sysibmadm.snapdb;

监控占有日志空间最旧的交易

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_SPACE_USED/1024/1024)

      as "Log Used (M)", 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;

查看占用日志超过10分钟的事务

select UOW_START_TIME, UOW_STOP_TIME, AGENT_ID

from sysibmadm.snapappl

where UOW_STOP_TIME is NULL and UOW_START_TIME is not NULL and

      UOW_START_TIME < (current timestamp - 10 minutes)and

      UOW_LOG_SPACE_USED > 0

order by UOW_START_TIME asc;

查看数据库中日志占用量大的前10个agentid

db2 "select DBPARTITIONNUM,AGENT_ID,int(UOW_LOG_SPACE_USED/1024/1024) as Log_Used_MB from sysibmadm.snapappl order by Log_Used_MB desc fetch first 10 rows only"

监控存储路径

select substr(type,1,20) as type ,substr(path,1,50) as path from sysibmadm.dbpaths order by type;

监控表空间使用情况

db2 "select substr(tbsp_name,1,18),tbsp_type,tbsp_free_size_kb/1024 as "tbsp_free_size(MB)",tbsp_utilization_percent from sysibmadm.tbsp_utilization;

db2 "select substr(tablespace_name,1,30) as TBSPC_NAME,used_pages, free_pages from table (snapshot_tbs_cfg ('PR0', -1)) as snapshot_tbs_cfg"

select DBPARTITIONNUM, TBSP_ID , substr(tbsp_name, 1, 18),

   TBSP_NUM_CONTAINERS, TBSP_TOTAL_SIZE_KB/1024/1024 as

   "totalsize(G)", tbsp_type, tbsp_free_size_kb/1024 as

   "tbsp_free_size(MB)", tbsp_utilization_percent

from sysibmadm.tbsp_utilization

where TBSP_TYPE not like 'SMS'

order by tbsp_utilization_percent;

查看表空间高水位标记和已用表空间的差距：

db2 "select substr(a.tbsp_name,1,18) as "name",(b.TBSP_PAGE_SIZEa.TBSP_PAGE_TOP)/1024/1024 as "HWM_MB",(b.TBSP_PAGE_SIZEa.TBSP_USED_PAGES)/1024/1024 as "USED_MB", ((b.TBSP_PAGE_SIZEa.TBSP_PAGE_TOP)/1024/1024-(b.TBSP_PAGE_SIZEa.TBSP_USED_PAGES)/1024/1024) as "diff_MB",b.TBSP_USING_AUTO_STORAGE as "AUTO" from sysibmadm.SNAPTBSP_PART a,sysibmadm.SNAPTBSP b where a.tbsp_id=b.tbsp_id order by 4 desc"

容器大小：

SELECT varchar(container_name, 65) as container_name,

   fs_id,

   fs_used_size,

   fs_total_size,

   CASE WHEN fs_total_size > 0

        THEN DEC(100*(FLOAT(fs_used_size)/FLOAT(fs_total_size)),5,2)

        ELSE DEC(-1,5,2)

   END as utilization

FROM TABLE(MON_GET_CONTAINER('',-1)) AS t

ORDER BY utilization DESC;

表空间使用率统计

db2 "select substr(a.tbsp_name,1,18) as name,substr(a.tbsp_type,1,10) as tbstype,a.TBSP_USING_AUTO_STORAGE as AUTO_STORAGE,substr(a.tbsp_state,1,8) as state,a.tbsp_total_size_kb/1024 as TotalMB ,\

a.TBSP_PAGE_TOP*a.TBSP_PAGE_SIZE/1024/1024 as top_size_mb,a.tbsp_used_size_kb/1024 as UsedMB,\

a.TBSP_UTILIZATION_PERCENT as UsedPer ,b.CONTAINER_NAME from sysibmadm.\

tbsp_utilization a, sysibmadm.SNAPCONTAINER b where tbsp_type='DMS' and a.TBSP_ID=b.TBSP_ID order by 3,8 desc"

DB2中如果想查询一个表的分布键（比如表MK. TM_NS_SMS_RETN_D）多分区

select TBCREATOR,TBNAME,NAME,PARTKEYSEQ

from sysIBM.SYSCOLUMNS t

where t.TBNAME='TM_NS_SMS_RETN_D' and PARTKEYSEQ>0

order by PARTKEYSEQ

select substr(TBCREATOR,1,30),substr(TBNAME,1,50),substr(NAME,1,30),PARTKEYSEQ from SYSIBM.SYSCOLUMNS where PARTKEYSEQ>0 order by PARTKEYSEQ;

DB2中如果想查询一个表的分区键（比如表MK. TM_NS_SMS_RETN_D）

db2 "select substr(TABSCHEMA,1,15) as TABSCHEMA,substr(TABNAME,1,50) as TABNAME,substr(DATAPARTITIONEXPRESSION,1,40) as DATAPARTITIONEXPRESSION from SYSCAT.DATAPARTITIONEXPRESSION "

9.7 查询分区表信息:

db2 describe data partitions for table en_srvlog show detail

db2 "select trim(substr(tabschema,1,10)) schema,trim(substr(tabname,1,35)) tabname,decimal(a.tbspaceid,3,0) tspid,trim(substr(b.tbspace,1,20)) tbspace,decimal(a.INDEX_TBSPACEID,3,0) itspid,trim(substr(d.tbspace,1,20)) itbspace,decimal(long_tbspaceid,3,0) ltspid,trim(substr(c.tbspace,1,10)) ltbspace,decimal(datapartitionid,3,0) pid,trim(substr(datapartitionname,1,12)) pname,decimal(partitionobjectid,4,0) poid,access_mode acc,trim(substr(status,1,3)) sta,decimal(seqno,3,0) seq,lowinclusive lin,trim(substr(lowvalue,1,15)) lval,highinclusive hin,trim(substr(highvalue,1,15)) hva from syscat.datapartitions a,syscat.tablespaces b,syscat.tablespaces c,syscat.tablespaces d where a.tbspaceid=b.tbspaceid and a.long_tbspaceid=c.tbspaceid and a.index_tbspaceid=d.tbspaceid and lowvalue<>'' AND highvalue<>'' /and tabname='AG_ACC_TRANS_INFO_SP'/ order by tabname,seqno,datapartitionid,partitionobjectid"|more

如果是9.5，查询分区表语句如下

db2 "select trim(substr(a.tabschema,1,10)) schema,trim(substr(a.tabname,1,30)) tabname,decimal(a.tbspaceid,3,0) tspid,trim(substr(b.tbspace,1,20)) tbspace,trim(substr(e.index_tbspace,1,20)) itbspace,decimal(long_tbspaceid,3,0) ltspid,trim(substr(c.tbspace,1,20)) ltbspace,decimal(datapartitionid,3,0) pid,trim(substr(datapartitionname,1,12)) pname,decimal(partitionobjectid,4,0) poid,a.access_mode acc,trim(substr(a.status,1,3)) sta,decimal(seqno,3,0) seq,lowinclusive lin,trim(substr(lowvalue,1,15)) lval,highinclusive hin,trim(substr(highvalue,1,15)) hva from syscat.datapartitions a,syscat.tablespaces b,syscat.tablespaces c,syscat.tables e where a.tabschema=e.tabschema and e.tabname=a.tabname and a.tbspaceid=b.tbspaceid and a.long_tbspaceid=c.tbspaceid and

lowvalue<>'' AND highvalue<>'' /and tabname='AG_ACC_TRANS_INFO_SP'/ order by tabname,seqno,datapartitionid,partitionobjectid"|more

查看数据库中的授权情况

SELECT DISTINCT GRANTEE, GRANTEETYPE, 'DATABASE' FROM SYSCAT.DBAUTH

UNION

SELECT DISTINCT GRANTEE, GRANTEETYPE, 'TABLE ' FROM SYSCAT.TABAUTH

UNION

SELECT DISTINCT GRANTEE, GRANTEETYPE, 'PACKAGE ' FROM SYSCAT.PACKAGEAUTH

UNION

SELECT DISTINCT GRANTEE, GRANTEETYPE, 'INDEX ' FROM SYSCAT.INDEXAUTH

UNION

SELECT DISTINCT GRANTEE, GRANTEETYPE, 'COLUMN ' FROM SYSCAT.COLAUTH

UNION

SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SCHEMA ' FROM SYSCAT.SCHEMAAUTH

UNION

SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SERVER ' FROM SYSCAT.PASSTHRUAUTH

ORDER BY GRANTEE, GRANTEETYPE, 3;

查看某个用户有哪些表授权:

select substr(GRANTEE,1,20) as grantee,GRANTEETYPE,substr(TABSCHEMA,1,30) as tabschema,substr(TABNAME,1,40) as tabname from SYSCAT.TABAUTH where GRANTEE='DI0VW';

查看数据库中前20个最大的表

select substr(a.tabschema,1,20) as schema, substr(a.tabname,1,30)as table, sum(a.DATA_OBJECT_P_SIZE) as data_p_size,

SUM(a.INDEX_OBJECT_P_SIZE) as index_p_size, SUM(a.LONG_OBJECT_P_SIZE) as long_p_size, SUM(a.LOB_OBJECT_P_SIZE) as lob_p_size,

SUM (a.XML_OBJECT_P_SIZE) as xml_p_size from sysibmadm.admintabinfo as a, syscat.tables as b where a.tabname=b.tabname and

b.tabschema not like 'SYS%' group by a.tabschema,a.tabname order by data_p_size desc fetch first 20 rows only;

查看每个索引的大小信息

db2 "select substr(indschema,1,6) ischema,substr(indname,1,20) iname,iid,substr(tabschema,1,6) tschema,substr(tabname,1,20) tname,compress_attr c_attr,index_compressed i_compressed,decimal(index_object_l_size/1024,6,0) lsz_mb,decimal(index_object_p_size/1024,6,0) psz_mb,index_requires_rebuild rebuild,large_rids from table(admin_get_index_info('','SAPBGP','/BIC/B0002788000')) AS X"

db2 "select trim(substr(tabschema,1,20)) as tabschema,trim(substr(tabname,1,30)) as tabname,trim(substr(indschema,1,20)) as indschema,trim(substr(indname,1,30)) as indname,nleaf*32/1024 as sizeMB from syscat.indexes where tbspaceid=24 order by nleaf desc" > idxsize.out

主从表

db2 "select substr(REFTABSCHEMA,1,20) as par_schema,substr(REFTABNAME,1,30) as par_tab,substr(TABSCHEMA,1,20) as acc_schema,substr(TABNAME,1,30) as acc_tab from SYSCAT.REFERENCES"

db2 "SET INTEGRITY FOR RUNFE.AL_CEN_USERS IMMEDIATE CHECKED"

db2 "select substr(a.constname,1,15) constname,substr(a.tabschema,1,5) schema,substr(a.tabname,1,15) tabname,substr(b.colname,1,15) fcolname,substr(a.owner,1,6) owner/,ownertype type/,substr(a.refkeyname,1,15) refkeyname,substr(a.reftabschema,1,10) reftabschema,substr(a.reftabname,1,10) reftabname,substr(c.colname,1,15) pcolname,colcount,deleterule Del,updaterule upd/,create_time/ from syscat.references a, syscat.keycoluse b,syscat.keycoluse c where a.constname=b.constname and a.refkeyname=c.constname and b.colseq=c.colseq order by 1,2,3,4"

查看语句执行情况

db2 "SELECT (TOTAL_EXEC_TIME + TOTAL_EXEC_TIME_MS / 1000000.0)/NUM_EXECUTIONS AS AVG_CPU,NUM_EXECUTIONS,SUBSTR(STMT_TEXT, 1, 1000) as SQL FROM SYSIBMADM.SNAPDYN_SQL\

where NUM_EXECUTIONS>0 ORDER BY AVG_CPU desc fetch first 100 rows only"|more

SELECT SUBSTR(DETMETRICS.STMT_TEXT, 1, 40) STMT_TEXT,
DETMETRICS.ROWS_RETURNED,
DETMETRICS.STMT_EXEC_TIME
FROM TABLE(MON_GET_PKG_CACHE_STMT_DETAILS(CAST(NULL AS CHAR(1)),
CAST(NULL AS VARCHAR(32) FOR BIT DATA),
CAST(NULL AS CLOB(1K)), -1)) AS STMT_METRICS,
XMLTABLE (XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'),
'$DETMETRICS/db2_pkg_cache_stmt_details' PASSING
XMLPARSE(DOCUMENT STMT_METRICS.DETAILS) as "DETMETRICS"
COLUMNS "STMT_TEXT" CLOB PATH 'stmt_text',
"ROWS_RETURNED" BIGINT PATH 'activity_metrics/rows_returned',
"STMT_EXEC_TIME" BIGINT PATH 'activity_metrics/stmt_exec_time'
) AS DETMETRICS
ORDER BY rows_returned DESC
FETCH FIRST 10 ROWS ONLY;

查看实例用户权限

SELECT char(authority, 26) authority, d_user, d_group, d_public FROM TABLE (AUTH_LIST_AUTHORITIES_FOR_AUTHID ('DB2QA2', 'U')) AS t;

查看DBADM用户有哪些:

SELECT DISTINCT GRANTEE, GRANTEETYPE FROM SYSCAT.DBAUTH WHERE DBADMAUTH = 'Y'

查看当前那些表在进行reorg操作

db2 "select substr(tbspace,1,10) tbspace,substr(t.tabname,1,20) tabname,substr(char(t.reorg_tbspc_id),1,2) sid,substr(char(t.reorg_index_id),1,2) xid,t.reorg_start,substr(char(x.reorg_max_phase),1,1) M,substr(char(x.reorg_phase),1,1) C,case when t.reorg_phase is null then 'online' else t.reorg_phase end as reorg_phase,t.reorg_status ,substr(char(t.reorg_current_counter),1,8) curr,substr(char(t.reorg_max_counter),1,8) max_counter,substr(char(t.reorg_rowscompressed),1,10) comp_num

,substr(char(t.reorg_current_counter*100/(t.reorg_max_counter+1)),1,2) per FROM TABLE( SNAP_GET_TAB_REORG('', -1)) AS T,table(snapshot_tbreorg('',-1)) as x ,syscat.tables y WHERE t.REORG_END IS NULL and t.tabname = x.table_name and y.tabname=t.tabname"

select

   substr(tabname, 1, 35) as tab_name,

   substr(tabschema, 1, 20) as tab_schema,

   reorg_phase,

   substr(reorg_type, 1, 30) as reorg_type,

   reorg_status,

   reorg_completion,

   dbpartitionnum

 from sysibmadm.snaptab_reorg

 order by dbpartitionnum;

查看临时表空间的使用情况

db2 "Select A.DBPARTITIONNUM,substr(char(tbsp_id),1,2)||substr(tbspace,1,13) tbspace,substr(tabschema,1,17) tabschema,substr(tabname,1,20) tabname,decimal(data_object_pagespagesize/1024,12,0) data_KB,decimal(index_object_pagespagesize/1024,12,0) index_kb,decimal(lob_object_pagespagesize/1024,12,0) lob_kb,decimal(long_object_pagespagesize/1024,12,0) long_kb,decimal(xda_object_pages*pagesize/1024,12,0) xda_kb,substr(char(rows_read),1,10) rows_read,substr(char(rows_written),1,10) rows_written from sysibmadm.SNAPTAB a,syscat.tablespaces b where tab_type='TEMP_TABLE'

and b.tbspaceid = a.tbsp_id order by 5 desc"|more

统一考核查看临时表空间使用情况:

db2 "Select A.DBPARTITIONNUM dbnum,trim(substr(char(tbsp_id),1,3)) tbspid,trim(substr(tbspace,1,13)) tbspace,trim(substr(tabschema,1,25)) tabschema,trim(substr(tabname,1,20)) tabname,decimal(data_object_pagespagesize/1024,12,0) data_KB,decimal(index_object_pagespagesize/1024,12,0) index_kb,decimal(lob_object_pagespagesize/1024,12,0) lob_kb,decimal(long_object_pagespagesize/1024,12,0) long_kb,decimal(xda_object_pages*pagesize/1024,12,0) xda_kb,trim(substr(char(rows_read),1,10)) rows_read,trim(substr(char(rows_written),1,10)) rows_written from sysibmadm.SNAPTAB a,syscat.tablespaces b where tab_type='TEMP_TABLE' and b.tbspaceid = a.tbsp_id order by 6 desc"

查看热点表和索引的扫描情况

SELECT substr(tabschema,1,20) as tabschema,

   substr(tabname,1,30) as tabname,

   sum(rows_read) as total_rows_read,

   sum(rows_inserted) as total_rows_inserted,

   sum(rows_updated) as total_rows_updated,

   sum(rows_deleted) as total_rows_deleted

FROM TABLE(MON_GET_TABLE('','',-2)) AS t

GROUP BY tabschema, tabname

ORDER BY total_rows_read DESC;

SELECT substr(S.INDSCHEMA,1,20) AS INDSCHEMA,

substr(S.INDNAME,1,30) AS INDNAME,

T.DATA_PARTITION_ID,

T.MEMBER,

T.INDEX_SCANS,

T.INDEX_ONLY_SCANS

FROM TABLE(MON_GET_INDEX('MDSUSER','M_DEP_PRIV_FIXE_ACCT_INFO', -2)) as T, SYSCAT.INDEXES AS S

WHERE T.TABSCHEMA = S.TABSCHEMA AND

T.TABNAME = S.TABNAME AND

T.IID = S.IID  

ORDER BY INDEX_SCANS DESC;

语句执行监控：

Select *

from (

 select MEMBER, TOTAL_ACT_TIME, TOTAL_CPU_TIME,

         (TOTAL_CPU_TIME+500)/ 1000 as "TOTAL_CPU_TIME (ms)",

         TOTAL_act_TIME/1.0/NUM_EXECUTIONS as AVG_EXECUTION_TIME,

         (TOTAL_CPU_TIME+500)/ 1000 / NUM_EXECUTIONS as

         "AVG_CPU_TIME (ms)", NUM_EXECUTIONS, substr(CAST(

         STMT_TEXT AS VARCHAR(2000)), 1, 2000)as STMT_TEXT,

         LOCK_WAITS, LOCK_WAIT_TIME_GLOBAL, LOCK_WAIT_TIME,

         POOL_DATA_L_READS, POOL_DATA_P_READS, POOL_INDEX_L_READS,

         POOL_INDEX_P_READS, RECLAIM_WAIT_TIME, CF_WAITS,

         CF_WAIT_TIME, POOL_DATA_GBP_INVALID_PAGES,

         POOL_INDEX_GBP_INVALID_PAGES, POOL_INDEX_GBP_P_READS,

         POOL_INDEX_GBP_L_READS, POOL_DATA_GBP_P_READS,

         POOL_DATA_GBP_L_READS

 from table(mon_get_pkg_cache_stmt(null, null, null, -2))as t )a

where STMT_TEXT not like '%MEMBER%' AND STMT_TEXT NOT LIKE

      '%SYSIBM%' and member=1 and NUM_EXECUTIONS > 1

order by TOTAL_CPU_TIME desc, NUM_EXECUTIONS desc,

      AVG_EXECUTION_TIME desc

fetch first 1200 rows only

with ur;

查看reorg状态

db2 "select DATA_PARTITION_ID,REORG_PHASE,REORG_STATUS, REORG_CURRENT_COUNTER, REORG_MAX_COUNTER,REORG_PHASE_START,substr(tabname,1,20) from sysibmadm.SNAPTAB_REORG where tabname in ('<tabname>') order by REORG_PHASE_START asc"

MQT表的依赖关系：

select substr(tabname,1,30) as tabname, dtype, substr(bname,1,30) as bname, btype from syscat.tabdep where dtype = 'S';

查看分区表分区状态：

db2 "select substr(DATAPARTITIONNAME,1,30) as DATAPARTITIONNAME ,substr(TABSCHEMA,1,20) as TABSCHEMA, substr(TABNAME,1,40) as TABNAME,STATUS from SYSCAT.DATAPARTITIONS where STATUS!='' with ur"

查看index的扫描使用情况：

SELECT VARCHAR(S.INDSCHEMA, 20) AS INDSCHEMA,

VARCHAR(S.INDNAME, 30) AS INDNAME,

T.DATA_PARTITION_ID,

T.MEMBER,

T.INDEX_SCANS,

T.INDEX_ONLY_SCANS

FROM TABLE(MON_GET_INDEX('PE','SL_YW_DM_ZHXX_DS ', -2)) as T, SYSCAT.INDEXES AS S

WHERE T.TABSCHEMA = S.TABSCHEMA AND

T.TABNAME = S.TABNAME AND

T.IID = S.IID  

ORDER BY INDEX_SCANS DESC;

查看数据库日志使用情况

SELECT DBPARTITIONNUM,substr(DB_NAME,1,15) as DB_NAME,LOG_UTILIZATION_PERCENT,TOTAL_LOG_USED_KB / 1024 as TOTAL_LOG_USED_MB, TOTAL_LOG_AVAILABLE_KB / 1024 as TOTAL_LOG_AVAILABLE_MB , TOTAL_LOG_USED_TOP_KB / 1024 as TOTAL_LOG_USED_TOP_MB FROM SYSIBMADM.LOG_UTILIZATION order by DBPARTITIONNUM;

查看事务运行过程中活动和等待的时间信息和百分比

SELECT application_handle,

   activity_id,

   uow_id,

   local_start_time

FROM TABLE(

WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES(

  cast(NULL as bigint), -1)

) AS T;

SELECT actmetrics.application_handle,

actmetrics.activity_id,

actmetrics.uow_id,

varchar(actmetrics.stmt_text, 50) as stmt_text,

actmetrics.total_act_time,

actmetrics.total_act_wait_time,

CASE WHEN actmetrics.total_act_time > 0

THEN DEC((

  FLOAT(actmetrics.total_act_wait_time) /

  FLOAT(actmetrics.total_act_time)) * 100, 5, 2)

ELSE NULL

END AS PERCENTAGE_WAIT_TIME

FROM TABLE(MON_GET_ACTIVITY_DETAILS(3270, 6472, 1, -2)) AS ACTDETAILS,

XMLTABLE (XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'),

'$actmetrics/db2_activity_details'

PASSING XMLPARSE(DOCUMENT ACTDETAILS.DETAILS) as "actmetrics"

COLUMNS "APPLICATION_HANDLE" INTEGER PATH 'application_handle',

"ACTIVITY_ID" INTEGER PATH 'activity_id',

"UOW_ID" INTEGER PATH 'uow_id',

"STMT_TEXT" VARCHAR(1024) PATH 'stmt_text',

"TOTAL_ACT_TIME" INTEGER PATH 'activity_metrics/total_act_time',

"TOTAL_ACT_WAIT_TIME" INTEGER PATH 'activity_metrics/total_act_wait_time'

) AS ACTMETRICS;

查看数据库读取最多的10张表

SELECT varchar(tabschema,20) as tabschema,

   varchar(tabname,20) as tabname,

   sum(rows_read) as total_rows_read,

   sum(rows_inserted) as total_rows_inserted,

   sum(rows_updated) as total_rows_updated,

   sum(rows_deleted) as total_rows_deleted

FROM TABLE(MON_GET_TABLE('','',-2)) AS t

GROUP BY tabschema, tabname

ORDER BY total_rows_read DESC fetch first 10 rows only;

表空间物理读取排序：

SELECT varchar(tbsp_name, 30) as tbsp_name,

              member,

              tbsp_type,

              pool_data_p_reads

FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t

ORDER BY pool_data_p_reads DESC;

查看当前util事务的执行状态：

SELECT UTILITY_TYPE, UTILITY_PRIORITY, SUBSTR(UTILITY_DESCRIPTION, 1, 72)

AS UTILITY_DESCRIPTION, SUBSTR(UTILITY_DBNAME, 1, 17) AS

UTILITY_DBNAME, UTILITY_STATE, UTILITY_INVOKER_TYPE, DBPARTITIONNUM

FROM SYSIBMADM.SNAPUTIL ORDER BY DBPARTITIONNUM;

查看表的压缩情况

SELECT SUBSTR(TABSCHEMA, 1, 10) AS TABSCHEMA, SUBSTR(TABNAME, 1, 10) AS TABNAME,

   DBPARTITIONNUM, DATAPARTITIONID, OBJECT_TYPE, ROWCOMPMODE,

   PCTPAGESSAVED_CURRENT, AVGROWSIZE_CURRENT,

   PCTPAGESSAVED_STATIC, AVGROWSIZE_STATIC,

   PCTPAGESSAVED_ADAPTIVE, AVGROWSIZE_ADAPTIVE

FROM TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO('SCHEMA1', 'TABLE1'));

SELECT SUBSTR(TABSCHEMA, 1, 10) AS TABSCHEMA, SUBSTR(TABNAME, 1, 10) AS TABNAME,

   DBPARTITIONNUM, DATAPARTITIONID, OBJECT_TYPE, ROWCOMPMODE, BUILDER,

   BUILD_TIMESTAMP, SIZE, HISTORICAL_DICTIONARY, ROWS_SAMPLED,

   PCTPAGESSAVED, AVGCOMPRESSEDROWSIZE

FROM TABLE( SYSPROC.ADMIN_GET_TAB_DICTIONARY_INFO( 'PAGECOMP', 'ADMIN_VIEW' ));

查看当前每个分区的automatic maintenance queue任务情况：

SELECT MEMBER,

   QUEUE_POSITION,

   JOB_STATUS,

   JOB_TYPE,

   VARCHAR(DB_NAME, 10) AS DB_NAME,

   OBJECT_TYPE,

   VARCHAR(OBJECT_SCHEMA, 20) AS OBJECT_SCHEMA,

   VARCHAR(OBJECT_NAME, 30) AS OBJECT_NAME

FROM TABLE(MON_GET_AUTO_MAINT_QUEUE()) AS T

ORDER BY MEMBER, QUEUE_POSITION ASC;

查看当前各个分区的rebalancer任务情况

select varchar(tbsp_name, 30) as tbsp_name, dbpartitionnum, member, rebalancer_mode, rebalancer_status, rebalancer_extents_remaining, rebalancer_extents_processed, rebalancer_start_time from table(mon_get_rebalance_status(NULL,-2)) as t;

查看当前数据库的归档日志使用情况:

SELECT SUBSTR(DB_NAME, 1, 8) AS DB_NAME, FIRST_ACTIVE_LOG,

LAST_ACTIVE_LOG, CURRENT_ACTIVE_LOG, CURRENT_ARCHIVE_LOG,

DBPARTITIONNUM

FROM SYSIBMADM.SNAPDETAILLOG ORDER BY DBPARTITIONNUM;

查看latch等待次数和时间

select MEMBER,substr(LATCH_NAME,1,50) as LATCH_NAME,TOTAL_EXTENDED_LATCH_WAITS,TOTAL_EXTENDED_LATCH_WAIT_TIME

   from TABLE(MON_GET_EXTENDED_LATCH_WAIT(-2)) AS T order by TOTAL_EXTENDED_LATCH_WAIT_TIME;

查看应用当前执行状态

SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, AGENT_ID,

SUBSTR(APPL_NAME,1,10) AS APPL_NAME, APPL_STATUS

FROM TABLE(SNAP_GET_APPL_INFO(CAST(NULL AS VARCHAR(128)),-1)) AS T;

查看oldeast 和 indoubt trans的信息

SELECT MEMBER, TOTAL_LOG_AVAILABLE / 1024 /1024 as TOTAL_LOG_AVAILABLE ,

   TOTAL_LOG_USED / 1024 /1024 as TOTAL_LOG_USED,

   SEC_LOG_USED_TOP / 1024 /1024 as SEC_LOG_USED_TOP,

   TOT_LOG_USED_TOP / 1024 /1024 as TOT_LOG_USED_TOP,

   SEC_LOGS_ALLOCATED,

   APPLID_HOLDING_OLDEST_XACT,

   substr(NUM_INDOUBT_TRANS,1,5) as IND_TRANS

   FROM TABLE(MON_GET_TRANSACTION_LOG(-2)) AS T ORDER BY MEMBER ASC;

查询运行状态改变后超过1小时闲置的DECOUPLED事务

select DBPARTITIONNUM,PRIMARY_AUTH_ID,AGENT_ID,APPL_STATUS,STATUS_CHANGE_TIME

from TABLE(SNAP_GET_APPL_INFO(CAST(NULL AS VARCHAR(128)),-1)) as T

where STATUS_CHANGE_TIME is not NULL

and STATUS_CHANGE_TIME < (current timestamp - 1 hours)

and APPL_STATUS='DECOUPLED'

order by STATUS_CHANGE_TIME asc;

查看事务日志使用信息

select UOW_START_TIME,UOW_STOP_TIME,AGENT_ID, int(UOW_LOG_SPACE_USED/1024/1024) as "Log Used (M)" FROM TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) AS t where int(UOW_LOG_SPACE_USED/1024/1024/1024)> 5