Results 1 to 7 of 7

Thread: Reorg & runstat

  1. #1
    Join Date
    Dec 2010
    Posts
    123

    Unanswered: Reorg & runstat

    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???

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Dec 2010
    Posts
    123
    Quote Originally Posted by sathyaram_s View Post
    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............

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    -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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Mar 2011
    Posts
    1
    Quote Originally Posted by ravichandrapratap View Post
    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???

    #!/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

  7. #7
    Join Date
    Aug 2013
    Posts
    3

    Run runstat on all tables using admin_cmd?

    I am using DB2 9.7 and Data Studio/ Web Console 4.1 ... I am running a runstats utility on all tables using admin_cmd procedure.. But I am not able to run it as it has mixture of both SQL statement and CLP statements...

    CALL SYSPROC.ADMIN_CMD(db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname ),1,50),' and indexes all;'from \
    syscat.tables where type = 'T' " > runstats.out);

    I am running as sql script not DB2CLP script through web console..

    I know CALL SYSPROC.ADMIN_CMD ('RUNSTATS ON TABLE db2user.employee ON KEY COLUMNS and INDEXES ALL') work fines as it is on single table .And I want to have script for all tables on systables which has type T.

    Please help..I need it urgently

    Thanks In Advance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •