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=)