DB2 Utility Commands


RUNSTATS:
=>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