IBM Support

Monitor the progress of DB2 commands

Technical Blog Post


Abstract

Monitor the progress of DB2 commands

Body

Unavoidably some commands are time-consuming. This blog summarizes the commands that you can use to monitor the progress of DB2 commands.

DB2 Commands/Activities Monitoring Commands
Crash Recovery

db2pd -rec -db <dbname>

db2pd -util

Further reading: /support/pages/node/501343

Perf tuning: UTIL_HEAP_SZ, adjust parallelism by  setting number of redo workers, eg, set to 5 below(the below number 6 = 5 workers + 1 redo master):
db2set DB2BPVARS=<path to buffer pool config file>
In the config file, put this line:
PREC_NUM_AGENTS=6

Rollforward

db2pd -rec -db <dbname>

db2 list utilities show detail or db2pd -util

Perf tuning: same as Crash Recovery

Backup and Restore

db2 list utilities show detail or db2pd -util

Perf tuning: UTIL_HEAP_SZ, parallelism, buffer

Reorg

db2pd -db <dbname> -reorg
db2pd -db <dbname> -reorg index

Perf tuning: Dropping unnecessary indexes, sortheap & sheapthres(>SORTHEAP*#of_processors), number of page cleaner(NUM_IOCLEANERS)

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0024852.html

Rebalance

db2 list utilities show detail or db2pd -util
db2 get snapshot for tablespaces on <dbname>

MON_GET_REBALANCE_STATUS table function

Application rollback db2 GET SNAPSHOT FOR ALL APPLICATIONS
Load

db2pd -util

db2pd -load -alldbs

Perf tuning: Load parallelism and data buffer(>1/4*util_heap_sz), UTIL_HEAP_SZ

Further reading:

/support/pages/node/3286593

Indexing

/support/pages/node/280633

Perf tuning: UTIL_HEAP_SZ, SHEAPTHRES

Runstats

db2 list utilities show detail or db2pd -util

db2pd -db <dbname> -runstats

Further reading:

/support/pages/node/1140442

Partition Detach db2 list utilities show detail or db2pd -util

 

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm13286329