This is not quite right.
A DB2 *offline* reorg always reclaim empty space (deleted rows/pages) and
always rebuilds all indexes of the table. It is ***very irrelevant*** if
there is a primary key or not. The reorgchk does not even consider if the
index is underlying a primary key or not. The reorgchk will just indicate
if the index needs to be reorg'ed.

If there is an index with the cluster clause defined then that index is used
to resequence the table.
If there is no index with a cluster clause then an index can be specified
with the INDEX clause.
If neither of those are specified, no resequencing of the table happens.
This is also true for *online* or INPLACE reorgs.

The physical sort of the table will happen if you do not specify INDEXSCAN.
In the case of INDEXSCAN then the table is not physically sorted but rebuilt
in the sequence of the keys of the index specified even if the row location
sequence does not match the key sequence.

The reorgchk will indicate 3 thresholds that deals with space management and
reclaiming of space. If any of these is reached, it is recommended to reorg.
This means that reorg/runstats/rebind *could* give you a better performing
access plan.

For an *online* INPLACE reorg the table is either resequenced because of
cluster index or index clause, or, the space in the table is reclaimed.
Never both. it ia always either/or. It is the presence of a cluster clause
or, if none, the specification of the INDEX clause that defines what
happens.

If reorgchk does not give you any '*' to indicate the need of a reorg then
it is highly doubtful that doing the reorg/runstats/rebind process would
improve performance. Remeber that reorgchk uses UPDATE STATISITICS as a
default so if it does not indicate aneed for reorg then runstats/rebind
would probably not help.

The reorgxchk command uses 5 thesholds for the index. On (F4) is for
sequencing, thee CLUSTERRATIO. The other 4 are for space management. Again,
it is completely irrelevant as to whether the index is the primary key.
What is important is if that index is the **clustering** index. For this
one and the others, the other formulae indicate to reorg the table (OFFLINE)
to reclaim space, levels and leaf page management issues.

Hope this helps, Pierre.



Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
----- Original Message -----
From: "Kylin" <db2udbdba-ezmlmshield-x63566171.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Sunday, August 27, 2006 9:45 AM
Subject: Is reorgchk necessary


>
> Hi,
>
> As my understanding, reorg will physically move records in a table
> according the primary index sequence. So that durning the query, less IO
> operations are needed.
>
> reorgchk will generate a report accoding some statistic informations. It
> seems there will be a threshold. When this threshold reaches, it will mark
> the table or index with a * to tell reorg is necessary.
>
> But from my point of view, doing reorg as frequently as possible is
> better. When disordered records accumulate to a certain degree, it will
> cost longer time to run reorg. It just likes if you sort your book once a
> week or once a month.
>
> So I think reorg should be perfrom in spite of the result of reorgchk. If
> a table is ordered, "reorg" won't move any records. It works only when a
> table is disordered. Besides, reorgchk itself costs time to generate the
> report.
>
> Following is my preferred Database Maintenance Process:
>
> reorg --> runstats -> rebind
>
> I'm also not clear about the reorg on index. Since there can be only one
> primary index for one table, so the records can sort according only one
> sequence. What will the reorg of index do? Sort the index itself but not
> records in the table?
>
> Please feel free to correct me if you find any issues. And any comments
> will be greatly appreciated.
>
> Best wishes,
> Qin
>
> **********************
> *A climber to DBA*
> **********************
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>



DB2 & UDB email list listserv db2-l LazyDBA home page