DB2 Utility Commands

DB2 Utility Commands


=>db2 runstats on table TABSCHEMA.TABNAME on all columns with distribution and sampled detailed indexes all tablesample bernoulli(10) set profile only; (runstats doesn't execute but only the profile is set)

=>db2 runstats on table TABSCHEMA.TABNAME use profile.

Check Progress of a runstats job:

=>db2pd -d dbname -runstats | grep -i -p progress

REORG EXAMPLE...This will run a reorg on tables in a schema with a certain table name or you can take the grep TABNAME out and do all tables in a schema. this is an OFFLINE reorg.

=>db2 list tables for schema SCHEMANAME show detail | grep TABNAME | grep " T " | while read T S x;do db2 -v "reorg table $S.$T " ; done

Create a REORG Script

=>db2 "select 'REORG TABLE '||rtrim(tabschema)||'.'||rtrim(tabname)||' inplace; ' from syscat.tables where type in ('T') order by tabschema, tabname" > reorg.txt

=>db2 -tvf reorg.txt | tee reorg.out

###Reorg monitor query

db2 "select substr(tabname,1,30) as TABNAME, reorg_phase,reorg_current_counter, reorg_max_counter,dbpartitionnum from sysibmadm.snaptab_reorg where reorg_status != 'COMPLETED'"

Create a RUNSTATS Script:

db2 -x "select 'runstats on table '||tabschema||'.'||tabname||' with distribution and detailed indexes all;' from syscat.tables " > runstats.txt

=>db2 -tvf runstats.txt | tee runstats.out

List the running utilities and their progress

=>db2 list utilities show detail

Throttle a running utility.

First find the Utility ID.

=>db2 list utilities show detail

Throttle the utility to a value of 1 to 10. 10 being the most throttled.

=>db2 set UTIL_IMPACT_PRIORITY for <util_id> TO 5