How to Tell When a Table Reorg is Needed

You may also like...

15 Responses

  1. Chris Aldrich says:

    THANK YOU, Ember! This is the kind of stuff I was hoping to find on REORGCHK that I just was not finding!

  2. Great information and SQL examples, as usual, thank you Ember. But, I disagree. REORGCHK should be deleted from sqllib/bin and never used. It can give REORG recommendations for tables that haven’t been read/accessed in many months. Instead, successful and smart DBAs should base their REORG decisions on table performance data – specifically, Overflows and Rows Read. When (Overflows * 100)/Rows Read > 3%, REORG a table to mitigate double I/Os. Learn more and find SQL here: http://www.dbisoftware.com/blog/db2_performance.php?id=116

    In DB2 10.5, there’s a new REORG option that very quickly fixes the double I/O overhead of Overflows by correcting the pointers: INPLACE … CLEANUP OVERFLOWS.

    The clever DBA will write a query against SYSIBMADM.SNAPTAB to generate REORG commands for tables where Overflows/Rows Read > 3%. Smarter DBAs will use DBI’s Brother-Hawk to fully automate the REORGs.

    • Ember Crooks says:

      There are always different views on what requires a reorg, which is why I provide this information, so people can understand the IBM party line on it and what those formulas are really doing under the covers. Thanks for your additional details, Scott.

  3. Isaac Munoz says:

    Thanks Ember for your article.

    I quite don’t understand the meaning of wasting time in your sentence: “I eliminate tables with a cardinality of 0 from reorgs, because they are sometimes flagged on this formula, and it wastes time”.
    If a table is wiped completely F2 will be flagged for the table but because it has cardinality 0 would you not reorg it?. I’m just referring a generic situation. In a real situation you would need further analyze the future use of that table.

    Regards!

    • Ember Crooks says:

      In vended databases, there are often a number of tables that are always empty – for features you are not using. Obviously a newly deleted from table would not qualify, and would need a reorg.

  4. Somraj Chakrabarty says:

    Hi Ember,

    Very useful article.But I have a doubt.Is it at all possible that after REORG performance may get degraded.Will rebind can help here?

  5. Raf Mathijs says:

    I made a Korn shell script to check overflow access on tables over a period of time

    #!/usr/bin/ksh

    function Usage
    {
    echo “———————————————————————————————————”
    echo “Usage : $(basename $0) DATABASE INTERVAL_IN SECONDS”
    echo
    }
    # Load common functions
    #. /db2scripts/common_script/ksh_functions

    # Check unintialized variables
    set -u

    # Check input parameter
    readonly NbInputParmReq=2
    [[ $# != ${NbInputParmReq} ]] && Usage && FatalError “The script expects ${NbInputParmReq} input parameters and receives $#”

    # Initialisation and declaration of variables
    readonly BASEPATH=
    readonly LOGFILE=$(basename $0)_${DB2INSTANCE}_$(hostname)_$(date +%Y-%m-%d-%H.%M.%S).log
    integer RC_CMD=0
    readonly DATABASE=$1
    readonly INTERVAL=$2

    # Action
    {
    #MsgStart
    date

    db2 connect to ${DATABASE}
    print “create baseline tables”
    db2 -v “declare global temporary table session.mon_table_baseline as (select current timestamp as snapshot_timestamp, a.* from table(mon_get_table(NULL,NULL,-2)) a) with no data on commit preserve rows” >/dev/null
    print ” delete data from baseline table ( just in case )”
    db2 -x “delete from session.mon_table_baseline” > /dev/null
    print “insert baseline data into baseline table”
    db2 -v “insert into session.mon_table_baseline select current timestamp as snapshot_timestamp, a.* from table(mon_get_table(NULL,NULL,-2)) a” > /dev/null
    print ” wait the given interval ( ${INTERVAL} seconds )”
    sleep ${INTERVAL}
    print ” done sleeping”
    #print ” collect the delta information and return the wanted information”
    print ” Overflow Related Metrics ”
    print ” ———————— ”
    db2 -v ” select timestampdiff(2,char(timestamp(current timestamp)-b.snapshot_timestamp)) as snapshot_timestamp_diff_in_seconds,rtrim(substr(c.tabschema,1,25)) as TABSCHEMA,rtrim(substr(c.tabname,1,45)) as TABNAME,c.DATA_OBJECT_L_PAGES as Logical_pages_on_disk,c.OVERFLOW_ACCESSES-b.OVERFLOW_ACCESSES as OVERFLOW_ACCESSES, c.rows_read – b.rows_read as ROWS_READ, (c.OVERFLOW_ACCESSES-b.OVERFLOW_ACCESSES) *100 /( c.rows_read – b.rows_read) as PCT_OVERFLOW_ACCESSES_ROWS_READ from table(mon_get_table(NULL,NULL,-2)) c join session.mon_table_baseline b on c.tabschema=b.tabschema and c.tabname=b.tabname and c.MEMBER=b.MEMBER where c.rows_read-b.rows_read > 0 order by 5,6,7 desc fetch first 50 rows only”

    print “End of the script – `date`”
    #} > ${LOGFILE}
    } | tee ${LOGFILE}

  6. yaser arafath says:

    query is not running from the application side, it is not getting aborted also. and the last runstats for the table was ran on 2014. in this case as a db2dba what should i do ?

    thanks

    i appreciate your time in this.

    • Ember Crooks says:

      There are a lot of possible issues. You’ll want to do runstats – unless the data is truely unchanging, they should be done daily or weekly. It could also be a locking issue – you may want to look for lock-waits in db2top, and review your setting for LOCKTIMEOUT (-1 means “wait forever”). Then there’s a whole art to query tuning if it isn’t either of those. Perhaps an index is needed.

  7. Yaser says:

    Hello Ember,

    Recently on of the user is facing performance issue, in accessing the database. I’ve done the reorgchk and everything seems fine and doesn’t require any reorgs for the table. And also in the database there is no deadlocks. Can I know what might be the possible cause for this. Is there some problem with the db side or with the application. Kindly suggest. Thanks Much

    • Ember Crooks says:

      I could write a book on this topic – literally. I assume runstats are current? I would be looking at the access plan (using some method of explain) and if I could add indexes to help it (using db2advis and other methods).

  1. June 2, 2014

    […] How to Tell When a Table Reorg is Needed […]

  2. June 17, 2014

    […] My earlier post on table reorgs covered Reorg’s place in a database maintenance cycle and running reorgchk. If you haven’t already read it, check it out: How to Tell When a Table Reorg is Needed […]

  3. July 21, 2014

    […] can be found on the IBM Knowledge Center, but I would certainly advice you to have a look at Ember Crooks DB2Commerce website for an excellent article on […]

Leave a Reply

Your email address will not be published. Required fields are marked *