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

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

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.

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.

where should I look for cluster ratio and where should I look for dataset extents ?

Thanks

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

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

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.

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

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

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

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.

>The most important stat is the cluster ratio
What is the formula used to calculate the cluster ratio?