Execute runstats in a stored procedure

[sansaniwal12]

Hi,
we want to execute runstats for all database tables in one go.In db2 v9, there is no such option to run runstats schema vise or for complete database.So,can we execute runstats from a stored procedure.

[raimee]

I use a shell script to accomplish what you want. You'll have to modify a bit
for you environment.

#!/usr/bin/ksh

################################################## ##

DBNAME="DBNAME"
TABSCHEMA="MYSCHEMA"

db2 activate db $DBNAME
export DB2DBDFT=$DBNAME

echo "Running..."

db2 connect to $DBNAME user user using pass

db2 -x "select tabname from syscat.tables where tabschema='MYSCHEMA' and TYPE='T'" |
while read TABNAME
do
db2 -v -x "runstats on table $TABSCHEMA.\"$TABNAME\" and indexes all"
done

db2 terminate

[dewa81]

If you want to runstats on all the tables in the database we can accomplish this with just a single command

Connect to the database and run the follwing command
reorgchk update statistics