IMHO, the most important time to run REORG is when there are a significant
number of Overflow_Accesses compared to Rows_Read on tables with significant
activity. You may only need to run REORGCHK a few times a year if you base
your REORG decisions based on Table Snapshot information.

To illustrate, here is a DB2 V9 SQL query that generates online (INPLACE)
table reorgs for tables having > 3% Overflows and with "significant" Read
activity.

db2 "select 'REORG TABLE ' || TABSCHEMA || '.' || TABNAME || ' INPLACE ALLOW
WRITE ACCESS;' from sysibmadm.snaptab where (ROWS_READ > 999) AND
(((OVERFLOW_ACCESSES * 100) / (ROWS_READ + 1) > 3)"

If a table hasn't been accessed, it won't show up in the Table Snapshot.
This query defines "significant" access as 1000 or more Rows_Read. After
the Table REORG completes, you will usually want to REORG the INDEXES on the
table as well. If you don't have V9 yet, V8 provides a table function that
could get you to the same result - or write a script that parses "GET
SNAPSHOT FOR TABLES on DBNAME" output.

REORGCHK may encourage you to REORG tables that are never or rarely
accessed, and that's a potential waste of resources. What's more, REORGCHK
with UPDATE STATISTICS will modify your catalog statistics which may cause
optimizer plans to change (for better or WORSE).

Just a thought for your consideration, individual resets may vary, and all
the usual disclaimers.

Best regards,
Scott Hayes
IBM DB2 GOLD Consultant
Visit my blog: http://www.database-brothers.com/blog/Scott_Hayes.php
FREE: Get Brother-Eagle for DB2 V8.2 and V9:
http://www.database-brothers.com/brother-eagle.php



-----Original Message-----
From: Paul Le Claire
[mailto:db2udbdba-ezmlmshield-x62321640.[Email address protected]
Sent: Sunday, August 27, 2006 12:10 PM
To: LazyDBA Discussion
Subject: Fw: Is reorgchk necessary

Qin,

That works wonderful if you have all the spare CPU cycles in the world and
are not afraid of competing with your application for tables. Why not use
the hybrid approach? Run the reorg check as frequently as you need to and
then if tables require a reorg, take that table from the reorg check and put
it into a script that gets run off hours? That way you are not directly
competing with the application for exclusive control of the tables most of
the time and you are only burning reorg cycles when a reorg is truly
needed...

Just my .02 cents worth...

Been there done that DBA.

PL
----- Original Message -----
From: "Kylin " <db2udbdba-ezmlmshield-x63566171.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Sunday, August 27, 2006 8: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
>



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