Shell-最近使用命令记录2-DB2

DB2相关命令

日志检查

db2diag -time 2023-02-02 -l severe,error,critical

查看Lock-wait状态连接

db2pd -db -apinfo | grep -p “-----” | grep Lock-wait

查看SEQUENCES增长

db2 “select substr(seqname,1,50),nextcachefirstvalue,cache from syscat.sequences with ur” > seq_1.out
sleep 10
db2 “select substr(seqname,1,50),nextcachefirstvalue,cache from syscat.sequences with ur” > seq_1.out
paste seq_1.out seq_2.out > seq_paste.out
awk ‘$2<$5 {print $1" “$2” “$5” "$5-$2""$NF}’ seq_paste.out |column -t

观察TABLESPACE MOVE进度

SELECT
char(TBSP_NAME,30) TBSP_NAME,
decimal(NUM_EXTENTS_MOVED100.0/(NUM_EXTENTS_MOVED+NUM_EXTENTS_LEFT),5,2) as percent_complete,
decimal(TOTAL_MOVE_TIME1.0/NUM_EXTENTS_MOVED,6,1) as ms_per_extent,
CURRENT TIMESTAMP + ((TOTAL_MOVE_TIME*1.0/NUM_EXTENTS_MOVED/1000)*NUM_EXTENTS_LEFT) seconds as est_completion_ts
FROM TABLE(SYSPROC.MON_GET_EXTENT_MOVEMENT_STATUS(‘’, -1))
WHERE
NUM_EXTENTS_LEFT <> -1;

查看CPU较多的动态语句

SELECT NUM_EXECUTIONS , dec(TOTAL_CPU_TIME/NUM_EXEC_WITH_METRICS,20,2) as AVG_CPU_TIME,STMT_EXEC_TIME,substr(STMT_TEXT,1,500) 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 20 ROWS ONLY;

查看Latch Wait时间

select substr(latch_name,1,32) as latch_name,TOTAL_EXTENDED_LATCH_WAITS,TOTAL_EXTENDED_LATCH_WAIT_TIME from table(mon_get_extended_latch_wait(null))

查看平均Latch Wait时间前十的语句

select substr(stmt_text,1,50) as stmt_text,decimal(float(total_extended_latch_wait_time)/num_executions,10,5)as avg_latch_time from table(mon_get_pkg_cache_stmt(null,null,null,null)) where num_executions > 0 order by avg_latch_time desc fetch first 10 rows only

查看长时间未提交事务

SELECT
con.application_handle,
con.application_id,
con.application_name,
con.client_pid,
uow.uow_start_time,
uow.uow_log_space_used
FROM
table(mon_get_connection(cast(null as bigint), -1)) as con,
table(mon_get_unit_of_work(null, -1)) as uow
WHERE
con.application_handle = uow.application_handle and
uow.uow_log_space_used != 0
ORDER BY uow.uow_start_time ;

查看预取比例

select DEC((POOL_ASYNC_DATA_READS + POOL_ASYNC_INDEX_READS)) / DEC ((POOL_DATA_P_READS + POOL_INDEX_P_READS + POOL_TEMP_DATA_P_READS + POOL_TEMP_INDEX_P_READS)) FROM TABLE (MON_GET_DATABASE(-2)) as t

设置长时间执行阈值

SET WORKLOAD TO SYSDEFAULTADMWORKLOAD;
CREATE SERVICE CLASS “DB2JCC” DISABLE;
CREATE WORKLOAD “JCC_WORKLOAD” APPLNAME(‘edwlong*’) SERVICE CLASS “DB2JCC” COLLECT ACTIVITY DATA ON ALL DATABASE PARTITIONS WITH DETAILS COLLECT AGGREGATE ACTIVITY DATA BASE COLLECT ACTIVITY METRICS NONE;
SET WORKLOAD TO AUTOMATIC;

CREATE THRESHOLD “LONG_RUNNING_THRESHOLD”
FOR WORKLOAD JCC_WORKLOAD ACTIVITIES
ENFORCEMENT DATABASE
WHEN ACTIVITYTOTALTIME > 2 HOURS
COLLECT ACTIVITY DATA
ON COORDINATOR DATABASE PARTITION
WITH DETAILS, SECTION AND VALUES
STOP EXECUTION;

ALTER THRESHOLD “LONG_RUNNING_THRESHOLD” ENABLE;

COMMIT;

SQL语句分析

db2expln语句执行计划分析

db2expln -z ‘;’ -d sampledb -stmtfile 1.sql -output 1.expln -g

db2avis索引建议

db2advis -d sampledb -i 1.sql -o 1.advis -g -t 5

从快照抓取SQL

db2 reset monitor for database cphapp
$ db2advis -d cphapp -g -p -o result.out

db2exfmt语法分析及建议

db2 SET CURRENT EXPLAIN MODE EXPLAIN
db2 –tvf 1.sql
db2exfmt –d sampledb -1 -o 1.exfmt
db2 SET CURRENT EXPLAIN MODE NO
db2 commit work
db2 connect reset
db2 terminate

分析前准备

cd $INSTHOME/sqllib/misc
db2 -tf EXPLAIN.DDL
否则可能抛出如下错误
SQL0219N The required Explain table “DB2INST1.EXPLAIN_INSTANCE” does not
exist. SQLSTATE=42704

EventMonitor事件监视器

收集前准备

db2 reset monitor for database database-alias
db2 -v update monitor switches using lock on sort on statement on table on timestamp on UOW on
db2 -v get monitor switches
db2 -v reset monitor all

创建语句Statemon事件监视器

db2 -v “CREATE EVENT MONITOR STATMON FOR STATEMENTS WRITE TO FILE ‘/tmp/db2evmon/stat/20230202’ maxfilesize 10240 buffersize 16 blocked append manualstart”

启用及停用

db2 -v “SET EVENT MONITOR STATMON STATE 1”
sleep 30
db2 -v “SET EVENT MONITOR STATMON STATE 0”

读取并输出文本你

db2evmon -path ‘/tmp/db2evmon/stat/20230202’ > statmon.20230202.log

检查状态及删除

db2 “select evmonname,event_mon_state(evmonname) from syscat.eventmonitors”

db2 “drop event monitor RTMON_EVMON_STATS”

筛选数据的命令

过滤启动及执行时间
awk ‘{ORS=“”}/Start/{print $“”} {ORS=“\n”}/Elapsed/{print $4}’ statmon.20230202.log
过滤执行时间及语句
awk ‘{if（NR % 2 == 0) {print $4" “} else {printf(”%s ",$0)}}’`cat statmon.20230202.log |egrep ‘Elapsed Execution Time|Text’

开启锁超时事件

set DB2_CAPTURE_LOCKTIMEOUT=ON
ls -l db2locktimeout.02023-02-02-12 | awk ‘{print $9;}’ |cut -d. -f4 |sort -k 1 |cut -d- -f1,2,3,4,5 |uniq -c