Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2004
    Posts
    48

    Smile Unanswered: How Do I determine whether a table needs reorg or not ? (OS390 DB2 V7.2)

    Hi ,

    How do I determine whether a table needs reorg or not on mainframe ?
    And what are the changes we should expect after a successfull Reorg ?


    Thanks
    Mahe

  2. #2
    Join Date
    May 2002
    Posts
    43
    Not sure what your asking, but we run runstats on all tables then run a "db2 reorgchk current statistics on table ALL"

    It will then give you all tables and indexes that need reorg.

    The only answer I can give you on why is performance...... But I believe I am missing what you are truly looking for.......

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don’t believe that reorgchk is available on the mainframe. The most important stat is the cluster ratio, which should be at least 90%. There are some other stats regarding faroff pages that are important.

    Many times the indexes may need reorging, without reorging the tables.

    The reorg frequency should be estimated in conjunction with setting of percent free on the tablespaces for the data and indexes. Sometimes it is better to just do the reorg on a scheduled basis during weekly or monthly maintenance periods.

    BMC has a product that automatically triggers a reorg (during a specified window) based on several parameters you set up. I am not sure if IBM's new utilities (which are sold separately) have this feature.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The outcome of a reorg is to place the data in the correct physical sequence and re-create the defined percent free and freepages. This is also true of indexes, although indexes are always kept in the correct sequence as they are updated.

    The "correct physical sequence" of the data is determined by the clustering index, or the first index created if no clustering index is defined.

    Choosing the correct clustering index is important, and often done incorrectly by novice DBA's (and even some experienced DBA's). You must work with the application developers to understand what the clustering index on each table should be.

    Having data in the correct physical sequence can cut down of the amount of physical I/O required by a SELECT SQL statement. Having the proper amount of freespace on each page can cut down on the amount of physical I/O required by a INSERT or UPDATE SQL statement.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Apr 2004
    Posts
    48
    where should I look for cluster ratio and where should I look for dataset extents ?

    Thanks

  6. #6
    Join Date
    Apr 2004
    Posts
    64

    reply

    cluster ratio, you can find in SYSINDEXES and extents in SYSTABLEPART and SYSINDEXPART catalog tables if you have done the runstats recently.

    Otherwise you can see the DB2 VSAM dataset for the tablespace to findout the extents used.

    xamar

  7. #7
    Join Date
    Apr 2004
    Posts
    48

    Thumbs up

    Thanks you very much sir.


    clusterratio can say whether to REORG index or not but It doesnt say anything about reorging Table .... Right ? So what else ( apart from dataset extents) will tell us to do reorg on a table ?

    thanks
    Last edited by MahendraSetty; 05-25-04 at 10:07.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No, the cluster ratio of the clustering index tells you whether the table is in the same sequence as the index. So you would use it to determine whether to reorg the table. Note that this only applies to the clustering index (or first index created if there is no clustering index defined).

    Indexes are always in the correct sequence, although they may need to be reorged because there is too much freespace (due to page splits), or too little freespace (due to inserts), or the leaf pages are not contiguous on disk.

    An index page split occurs when there is no room for a new index entry, and DB2 splits the existing leaf page into 2 pages, each 50% full (this is not good where high performance is needed). Additionally, the new index page is not contiguous to the old page on disk, as it would be when reorged.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Apr 2004
    Location
    Jeddah-Saudi Arabia
    Posts
    7
    Hi,
    you can use Real-Time Statistics (RTS) to decide when tablespace need to Reorg or Copy or Runstats. ( DB2 v7 )

    other way,
    first collect all information before check by using RUNSTATS utility
    and run the following queries to check which table space need to reorg :

    from SYSIBM.SYSTABLEPART :
    (FARINDREF + NEARINDREF)*100 / CARD > 10

    from SYSIBM.SYSINDEXES :
    LEAFDIST > 200
    or
    CLUSTERRATIO <95 for Cluster index

    and Extends

    Best Regards
    Jeddah Rock

  10. #10
    Join Date
    Apr 2004
    Posts
    64

    reply

    I think LEAFDIST tells you whether to reorg index and not tablespace.

    I have some confusion in (FARINDREF+NEARINDREF) *100/CARD. What does this formula mean?

    I understand that clusterratio is more like (1-FARINDREF/CARD )*100 approximately.

    Besides the definition of FARINDREF and NEARINDREF is confusing to me. How far is far and how near is near?

    xamar

  11. #11
    Join Date
    Apr 2004
    Location
    Jeddah-Saudi Arabia
    Posts
    7

    Wink

    FARINDREF : number of active pages which far from original pages
    NEARINDREF : number of active pages which near from original pages

    that's happen when tablepsace or indexspace take extents

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Out of sequence index pages can occur when DB2 does an index page split and adds a new page because the existing page is full (all index entries are kept in exact sequence). When a page split occurs, 50% of the existing index entries are left on the current page, and 50% are put on the new page. The new page is obviously not next to the old page as it should be.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The most important stat is the cluster ratio
    What is the formula used to calculate the cluster ratio?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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