Results 1 to 7 of 7

Thread: reorgchk

  1. #1
    Join Date
    Apr 2013
    Posts
    33

    Unanswered: reorgchk

    I would want to generate my reorg table script based on the output of the db2 reorgchk command.

    Question 1:
    Is there a way I can run reorgchk command for a given schema AND non-system tables in one shot..Can I use both "TABLE" and "SCHEMA " in one command.

    .-UPDATE STATISTICS--.
    >>-REORGCHK--+--------------------+----------------------------->
    '-CURRENT STATISTICS-'

    .-ON TABLE USER-----------------.
    >--+-------------------------------+---------------------------><
    '-ON--+-SCHEMA--schema-name---+-'
    | .-USER-------. |
    '-TABLE--+-SYSTEM-----+-'
    +-ALL--------+
    '-table-name-'

    Question 2:
    Is there a coded sql I can use to identify tables those need reorgs based on the criteria that I would want to choose rather than the default criteria from the db2 reorgchk command.

    Index statistics:

    F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
    F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
    F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
    F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
    F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20

    Table statistics:

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

    Thanks
    Mani
    Jr. DBA

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can use the Stored Procedure IBM DB2 9.5 Information Center for Linux, UNIX, and Windows (call sysproc.reorgchk_tb_stats('T','ALL')), then query the temporary table it creates (session.tb_stats) and filter it however you want.

    Andy

  3. #3
    Join Date
    Apr 2013
    Posts
    33
    Thanks..
    I couldn't find tb_stats table . what is the exact table name ? what should be the session?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    session.tb_stats

    Andy

  5. #5
    Join Date
    Apr 2013
    Posts
    33
    Thanks. You are awesome!!!

  6. #6
    Join Date
    Apr 2013
    Posts
    33
    Does any one know how long the session.tb_stats remain after sysproc.reorg_chk proc is run
    .. Also can we run reorg right after running sysproc.reorg_chk or do we need to give some time for tb_stats to be built with the list before for the actual reorgs are run

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The session table, like any other session table will remain around until either the connection issues a DROP for it, or the connection terminates. You can run REORG right after calling the stored procedure, but you can actually run it at any time. The two things are totally separate from each other.

    Andy

Posting Permissions

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