Deear all,

i am presently working on db2 8.2 , 9.1 on AIX,

i need to do runstats and Reorg daily for so many databases from different servers.
what are the best ways to do runstat and reorg for all tables at a time for a database

if you use
db2 reorgchk update statistics on table all,
in above command wat is command used for runstats?? , by using above command runstat is done for only tables , or indexes also???

There is no hard and fast rule, but, for most environments I know, runstats on a daily basis and reorg on a weekly basis will work well.
You should take into account the impact of these utilities on your normal operations. If yours is a real 24X7 system(ie you have similar levels of workload throughout the day/night), I suggest that you pick and choose the tables to runstats on instead of a blanket stats collection.
For reorg, use reorgchk to identify the tables that needs reorg and reorganize only those tables.
I personally prefer doing runstats followed by 'reorgchk current statistics' as this gives you have full control over your runstats command options.

The question you asked is - how to do for all tables ..

db2 -x "select 'runstats on table '||'.'||rtrim(tabschema)||'.'||rtrim(tabname)||' with distribution and detailed indexes all ; ' from syscat.tables where type='T' > stats.db2

db2 -tvf stats.db2

Thanks for ur reply satyaram,
By using,
db2 -x "select 'runstats on table '||'.'||rtrim(tabschema)||'.'||rtrim(tabname)||' with distribution and detailed indexes all ; ' from syscat.tables where type='T' " > stats.db2
While executing stats.db2 file
we are getting the following error for all tables,

runstats on table .F_SW.VWQUEUE2_608 with distribution and detailed indexes all
SQL0104N An unexpected token ".F_SW.VWQUEUE2_608" was found following
"TABLE". Expected tokens may include: "<valid-table-name>". SQLSTATE=42601

runstats on table .F_SW.VWQUEUE2_618 with distribution and detailed indexes all
SQL0104N An unexpected token ".F_SW.VWQUEUE2_618" was found following
"TABLE". Expected tokens may include: "<valid-table-name>". SQLSTATE=42601

runstats on table .F_SW.VWQUEUE2_620 with distribution and detailed indexes all
SQL0104N An unexpected token ".F_SW.VWQUEUE2_620" was found following
"TABLE". Expected tokens may include: "<valid-table-name>". SQLSTATE=42601



please find the following syntax, its working succssefully.
db2 -x "SELECT 'RUNSTATS ON TABLE '||RTRIM(TABSCHEMA)||'.'||TABNAME||' WITH DISTRIBUTION AND INDEXES ALL;' FROM SYSCAT.TABLES WHERE TYPE='T' " > stats.db2

can u explain wat is the purpose of "-x" in above command & "RTRIM" stands for what??

Instead of above command can't i use,
db2 "reorgchk update statistics on table all" for runstat purpose???


i done following procedure for a table for reorg:

Step1:
$ db2 reorgchk update statistics on table TEST.EV_VERSION

Doing RUNSTATS ....


Table statistics:

F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: TEST.EV_VERSION
TEST EV_VERSION 3570 0 84 84 - 332010 0 99 100 ---
----------------------------------------------------------------------------------------

Index statistics:

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS -1) * (INDEXPAGESIZE - 96) > MIN(50, (100- PCTFREE))
F6: (100 - PCTFREE) * ( FLOOR[ (100 - min(10, pctfree)) / 100 * (indexPageSize - 96) / (ISIZE + 12) ] ** (NLEVELS - 2) ) * (indexPageSize - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20

SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: TEST.EV_VERSION
TEST IDX_VERSION_1 3570 17 0 2 64 0 397 51 70 8 0 0 *----
TEST KY_VERSION 3570 109 0 3 72 0 3570 51 66 52 0 0 *----
-------------------------------------------------------------------------------------------------

CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
for indexes that are not in the same sequence as the base table. When multiple
indexes are defined on a table, one or more indexes may be flagged as needing
REORG. Specify the most important index for REORG sequencing.

Tables defined using the ORGANIZE BY clause and the corresponding dimension
indexes have a '*' suffix to their names. The cardinality of a dimension index
is equal to the Active blocks statistic of the table.


Step2:
$ db2 reorg table TEST.EV_VERSION
DB20000I The REORG command completed successfully.
Step3:
$ db2 runstats on table TEST.EV_VERSION
DB20000I The RUNSTATS command completed successfully.
Step4:
$ db2 reorg indexes all for table TEST.EV_VERSION
DB20000I The REORG command completed successfully.
Step5:
db2 reorgchk update statistics on table TEST.EV_VERSION

Doing RUNSTATS ....


Table statistics:

F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: TEST.EV_VERSION
TEST EV_VERSION 3570 0 84 84 - 332010 0 99 100 ---
----------------------------------------------------------------------------------------

Index statistics:

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS -1) * (INDEXPAGESIZE - 96) > MIN(50, (100- PCTFREE))
F6: (100 - PCTFREE) * ( FLOOR[ (100 - min(10, pctfree)) / 100 * (indexPageSize - 96) / (ISIZE + 12) ] ** (NLEVELS - 2) ) * (indexPageSize - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20

SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: TEST.EV_VERSION
TEST IDX_VERSION_1 3570 13 0 2 64 0 397 51 93 8 0 0 *----
TEST KY_VERSION 3570 82 0 3 72 0 3570 51 89 52 0 0 *----
-------------------------------------------------------------------------------------------------

CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
for indexes that are not in the same sequence as the base table. When multiple
indexes are defined on a table, one or more indexes may be flagged as needing
REORG. Specify the most important index for REORG sequencing.

Tables defined using the ORGANIZE BY clause and the corresponding dimension
indexes have a '*' suffix to their names. The cardinality of a dimension index
is equal to the Active blocks statistic of the table.


My doubt is
After successful exicution of reorg command, again why it is showing *(reorg column in reorgchk command) i.e. need for reorg in Step5?? whether my reorg is successful or not???????, if not what procedure i have to follow to reorg indexes of above table , pls let me know,
Thnx in advance............

-x will not display the header and footer - ie column names and 'x rows selected' messages.. you get only the query results.
RTIM is 'trim spaces at the end of the string, ie right trim'

I prefer using the the 'generate commands and execute' approach as it gives you the flexibility to choose tables you want (or donot want) to runstats and also can use runstats options as you see fit.

hth

#!/bin/ksh


DB=$1
i=/home/$DB2INSTANCE/scripts/getout_$DB.out
var=/home/$DB2INSTANCE/scripts/log_runstasts.log
if [[ -e $var && -e $i ]]; then
rm $var $i
fi
db2 connect to $DB > /dev/null 2>&1
db2 -x "select (rtrim(tabschema)) concat '.' concat (substr(tabname,1,32)) from syscat.tables where type = 'T'" > $i
db2 terminate > /dev/null 2>&1
for mvar in $(<$i); do
/home/$DB2INSTANCE/scripts/runsts.ksh $mvar $DB $var &
while (($(db2 list utilities | grep -i runstats | wc -l) > 15)); do
sleep 1
done
done



cat runsts.ksh


. /home/$DB2INSTANCE/sqllib/db2profile

var=$3
db2 connect to $2 > /dev/null 2>&1
echo " ----------------------- run_stats ---------------- Table : " $1 >> $var

date >> $var
db2 runstats on table $1 on all columns with distribution on all columns and indexes all allow write access >> $var
db2 terminate > /dev/null 2>&1
date >> $var
echo " ----------------------- end run_stats ----------- Table : " $1 >> $var