1.查看是否有失效的视图
db2 "select substr(routineschema,1,30) as routineschema,substr(routinename,1,30) as routinename,routinetype,valid from syscat.routines where valid !='Y' and VALID !='' with ur" 2.查看是否有失效的表
db2 "select substr(tabschema,1,10) as tabschema,substr(tabname,1,30) as tabname,status from syscat.tables where status != 'N' with ur" 3.查看是否有失效的包
db2 "select substr(pkgschema,1,10) as pkgschema,substr(pkgname,1,30) as pkgname,valid from syscat.packages where valid !='Y' with ur" 4.对表进行runstats
db2 "runstats on table <tabname> with distribution and detailed" 5.查看分区表
db2 "select TABSCHEMA,TABNAME,DATAPARTITIONNAME FROM syscat.datapartitions where tabschema not like '%SYS%' and tabname not like '%EXPLAIN%' and tabname not like '%ADVISE%'" 6.查看某分区表状态，状态为N，代表不可用：
db2 "describe data partitions for table <tabname> show details" 需要对分区表执行完整性检查
7.db2 "set integrity for sales immediate checked" 8.利用游标导数
db2 "declare c1 cursor for select * from TABLE_NAME"
db2 "load from c1 of cursor insert into TABLE_NAME NONRECOVERABLE" 9.分区重组
db2 "reorg table sales allow read access on data partition part_jan" 10.delete操作缓慢
db2 "load from /dev/null of del replace into <schema>.<table> nonrecoverable"
db2 "truncate table <schema>.<table> immediate" 11.离线重组
db2 "reorg table tablename" 12.使用表空间重组
db2 "reorg table <tabname> use mytemp" 13.要重组表并根据索引myindex对行进行索引排序
db2 "reorg table <tabname> index <indexname>" 14.要使用SQL调用语句重组表，使用ADMIN_CMD过程发出REORG TABLE
db2 "call sysproc.admin_cmd('reorg table <tabname> index <indexname>')" 15.监控表的物理大小
db2 "select fpages from syscat.tables where tabname='T1'" 这个结果的准确程度取决于统计信息的准确程度，在用此语句监控前，建议做一次runstats。
16.监控数据库实用工具的进度
db2 list tuilities show detail 17.监控数据库crash recovery进度
db2 -db <dbname> -recovery 18.监控catalog cache命中率
Catalog Cache Size (CATALOGCACHE_SZ)
配置：
update db cfg for using CATALOGCACHE_SZ 32
监控：
db2 get snapshot for db on testdb|grep -i “catalog”
Catalog database partition number = 0
Catalog network node name =
Catalog cache lookups = 17
Catalog cache inserts = 7
Catalog cache verflows = 0
Catalog cache high water mark = 0 如果命中率(1 – (Catalog cache inserts / Catalog cache lookups)) * 100 < 95％,增加该参数值。
如果Catalog cache overflows 不为0， 也需要增加该参数值，一般同时会增加dbheap参数值。
19.查看是否有死锁存在
#db2 get snapshot for all on <dbname> | grep -i "Deadlocks detected" | grep -v "= 0" |more
或者
#db2 get snapshot for all on <dbname> > /tmp/snap.out
#grep "Deadlocks detected" /tmp/snap.out | grep -v "= 0" 20.搜索执行最频繁的语句
#db2 get snapshot for all on <dbname> | grep -i "Number of executions" | grep -v "= 0" | sort -k 5,5rn
或者
#db2 get snapshot for all on <dbname> > /tmp/snap.out
#grep -n "Number of executions" /tmp/snap.out | grep -v "= 0" | sort -k 6rn 21.查看总的执行时间
db2 get snapshot for all on <dbname> | grep -i "Total execution time" | grep -v "= 0.0" | sort -k 5nr
或者
db2 get snapshot for all on <dbname> > /tmp/snap.out
grep -n "Total execution time" /tmp/snap.out | grep -v "= 0.0"| sort -k 6nr 22.查看Rows read
Rows read可以识别读取行数最多的Dynamic sql语句
#db2 get snapshot for all on <dbname> | grep -ni "Rows read" | grep -v "= 0" | sort -k 5rn
或者
#db2 get snapshot for all on <dbname> > /tmp/snap.out
#grep -ni "Rows Read" /tmp/snap.out | grep -v "= 0"| sort -nr -k 5 23.查看Total execution time
#db2 get snapshot for all on testdb | grep -in "Total execution time" | grep -v "= 0.0" | sort -k 5r
或者
#db2 get snapshot for all on <dbname> > /tmp/snap.out
#grep -ni "Total execution time" /tmp/snap.out | grep -v "= 0.0"|sort -k 5r 24.查看当前连接数
#db2 get snapshot for db on testdb | grep -i appl | grep -v "Application Heap"
Application connects = 113
Applications connected currently = 2
Appls. executing in db manager currently = 0
Agents associated with applications = 19
Maximum agents associated with applications= 21
File number of log being archived = Not applicable
Application section lookups = 1002114
Application section inserts = 61
Memory Pool Type = Applications Shared Heap 25.DB2查看运行时间最长的SQL
#db2 "select substr(num_executions,1,10) as "Num_Execs", substr(average_execution_time_s,1,10) as "AvgTime", substr(stmt_sorts,1,10) as "Num_Sorts", substr(sorts_per_execution,1,10) 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 10 rows only" 26.查看平均执行时间最长的SQL
#db2 "select substr(stmt_text,1,500) as stmt_text,average_execution_time_s,num_executions from sysibmadm.top_dynamic_sql order by average_execution_time_s desc fetch first 10 rows only"|grep -iv selected 27.查看读取行数最多的SQL
#db2 "select substr(stmt_text,1,50) as stmt_text,substr(NUM_EXECUTIONS,1,5) as NUM_EXECUTIONS,substr(ROWS_READ,15) as ROWS_READ,substr(ROWS_WRITTEN,1,5) as ROWS_WRITTEN,substr(STMT_SORTS,1,5) as STMT_SORTS,substr(SORT_OVERFLOWS,1,5) as SORT_OVERFLOWS ,substr(TOTAL_SORT_TIME,1,5) as TOTAL_SORT_TIME,substr(TOTAL_USR_CPU_TIME,1,5) as TOTAL_USR_CPU_TIME,substr(TOTAL_EXEC_TIME,1,5) from sysibmadm.snapdyn_sql order by ROWS_READ desc fetch first 10 rows only"|grep -iv selected 28.查看执行次数最多的SQL
#db2 "select substr(stmt_text,1,100)as stmt_text,NUM_EXECUTIONS,TOTAL_USR_CPU_TIME,TOTAL_EXEC_TIME,SORT_OVERFLOWS from sysibmadm.snapdyn_sql order by NUM_EXECUTIONS desc fetch first 10 rows only"|grep -iv selected 29.查看排序最多的SQL
#db2 "selectsubstr(stmt_text,1,500) as stmt_text,stmt_sorts,SORT_OVERFLOWS,TOTAL_USR_CPU_TIME,NUM_EXECUTIONS from sysibmadm.snapdyn_sql order bystmt_sorts desc fetch first 10 rows only"|grep -iv selected 30.找到读写IO最频繁的表
#db2 "select substr(tabname,1,20)as tabname,rows_read,rows_written from sysibmadm.snaptab order by rows_read desc" 31.找到占用CPU最多的前10条SQL
#db2 "SELECT MEMBER,SECTION_TYPE ,varchar(stmt_text,200) as statement,num_exec_with_metrics as numExec, TOTAL_CPU_TIME/NUM_EXEC_WITH_METRICS as AVG_CPU_TIME,TOTAL_CPU_TIME FROM TABLE(MON_GET_PKG_CACHE_STMT( 'D', NULL, NULL, -2)) as T WHERE T.NUM_EXEC_WITH_METRICS <> 0 ORDER BY AVG_CPU_TIME desc fetch first 10 rows only" 32.查看bufferpool命中率
管理视图返回缓冲池命中率，包括总命中率、数据命中率、XDA 命中率，以及所有缓冲池和当前连接的数据库中的所有数据库分区的索引命中率，如清单 3 所示。缓冲池命中率是一个最基本的度量指标，为系统利用内存来避免磁盘 I/O 的有效性提供了一种重要的总体衡量方式。
#db2 "SELECT SUBSTR (DB_NAME,1,8) AS DB_NAME, SUBSTR(BP_NAME, 1, 14) AS BP_NAME,TOTAL_HIT_RATIO_PERCENT, DATA_HIT_RATIO_PERCENT, INDEX_HIT_RATIO_PERCENT FROM SYSIBMADM.BP_HITRATIO ORDER BY DBPARTITIONNUM"
DB_NAME BP_NAME TOTAL_HIT_RATIO_PERCENT DATA_HIT_RATIO_PERCENT INDEX_HIT_RATIO_PERCENT
-------- -------------- ----------------------- ---------------------- -----------------------
TESTDB IBMDEFAULTBP 99.94 99.98 99.92
TESTDB BPF32 - - -
TESTDB IBMSYSTEMBP4K - - -
TESTDB IBMSYSTEMBP8K - - -
TESTDB IBMSYSTEMBP16K - - -
TESTDB IBMSYSTEMBP32K - - -
6 record(s) selected. 33.查看内存使用情况
[db2inst1@node01 ~]$ db2 "SELECT POOL_ID,POOL_CUR_SIZE,POOL_WATERMARK,POOL_CONFIG_SIZE
FROM SYSIBMADM.SNAPDBM_MEMORY_POOL"
POOL_ID POOL_CUR_SIZE POOL_WATERMARK POOL_CONFIG_SIZE
-------------- -------------------- -------------------- --------------------
OTHER 63045632 63045632 168886272
FCMBP 4456448 4456448 4456448
MONITOR 720896 720896 393216
3 record(s) selected. 效果和db2mtrk -i -d看到的消息一样
[db2inst1@node01 ~]$ db2mtrk -i -d
Tracking Memory on: 2019/12/22 at 15:26:50
Memory for instance
other fcmbp monh
60.1M 4.2M 704.0K
Memory for database: TESTDB
utilh pckcacheh other catcacheh bph (2) bph (1)
64.0K 13.4M 192.0K 768.0K 521.5M 904.2M
bph (S32K) bph (S16K) bph (S8K) bph (S4K) shsorth lockh
1.8M 1.5M 1.4M 1.3M 2.2M 484.6M
dbh apph (263) apph (210) apph (199) apph (26) apph (24)
144.4M 128.0K 256.0K 128.0K 64.0K 64.0K
apph (23) apph (22) apph (21) apph (20) apph (19) apph (18)
64.0K 64.0K 64.0K 64.0K 64.0K 64.0K
apph (17) apph (16) apph (15) apph (14) apph (13) apph (12)
64.0K 64.0K 64.0K 64.0K 64.0K 192.0K
apph (11) apph (10) apph (9) apph (8) appshrh
64.0K 64.0K 64.0K 128.0K 2.0M
#db2 "SELECT SUBSTR(DB_NAME, 1, 8) AS DB_NAME, POOL_ID,POOL_CUR_SIZE, POOL_CONFIG_SIZE,
POOL_WATERMARK FROM SYSIBMADM.SNAPDB_MEMORY_POOL 看到的效果和db2mtrk -i -d -v看到的效果一样。
34.DB2全库检查
db2 "inspect check database results keep dbcheck.out"
cd /home/db2inst1/sqllib/db2dump
grep -i error dbcheck.out 35.查找无效对象
db2 "select tabname from syscat.tables where status='X'"
db2 "select trigname from syscat.triggers where VALID='N'"
db2 "select viewname from syscat.views where valid='N'"
db2 "select substr(routineschema,1,30) as routineschema,substr(routinename,1,30) as routinename,routinetype,valid from syscat.routines where valid !='Y' and VALID !='' with ur" 36.查看表状态
[db2inst1@db04 db2dump]$ db2 "select status from syscat.tables where tabname='T1'"
[db2inst1@db04 db2dump]$ db2 "load query table t1" 37.查看是否需要reorg
[db2inst1@db04 db2dump]$ db2 "reorgchk update statistics on table t1"
Doing RUNSTATS ....
Table statistics:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80
SCHEMA.NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: DB2INST1.T1
6674414 0 17296 17296 - 1.40e+08 0 99 100 ---
----------------------------------------------------------------------------------------
Index statistics:
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20
SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD PCT_PAGES_SAVED F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table: DB2INST1.T1
Index: DB2INST1.IDX1911131345250
6674453 12383 0 3 0 1934235 9 9 1048 1048 0 30 79 2 0 0 *----
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
for indexes that are not in the same sequence as the base table. When multiple
indexes are defined on a table, one or more indexes may be flagged as needing
REORG. Specify the most important index for REORG sequencing.
Tables defined using the ORGANIZE BY clause and the corresponding dimension
indexes have a '*' suffix to their names. The cardinality of a dimension index
is equal to the Active blocks statistic of the table. db2expln -d <dbname> -f top1.sql(语句末不带;) -t -z @ -g
db2expln -d <dbname> -t -g -q "sql statement"
db2expln -d <dbname> -statement "sql statement" -g -o /tmp/expln.txt
db2 "select r.routineschema,r.routinename,d.bname,d.bschema from syscat.routines r,syscat.routinedep d where r.routineschema='DB2INST1'
and r.routinename='SP' and r.specificname=d.specificname and d.btype='K'"
db2expln -d <dbname> -c <schema name: db2inst1> -p <package name: SP> -g -o /tmp/sp.expln
db2advis命令
cd /home/db2inst1/sqllib/misc/
db2 connect to testdb
db2 -tvf EXPLAIN.DDL
1) method 1
db2advis -d <dbname> -i top1.sql -noxml
2) method 2
db2advis -d <dbname> -n cicpol -q cicpol -s "statement"
db2advis -d <dbname> -i top1.sql (语句末需要有;) -noxml
db2advis -d <dbname> -n <schema name> -q <schema name> -s "sql statement"
db2 “rollforward database 数据库名称 to 时间戳 using local time and stop overflow log path (归档日志所在的目录)” ![AOP编程_Android优雅权限框架(1)概念基础，2021金三银四前言正文大纲正文[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)