I am afraid you are being overly optimistic about REORG run times. You'll need to experiment in your own environment of course, but REORG can be a headache for several reasons.
First, there are two types of REORGS, classic and inplace.
The classic REORG basically does an "UNLOAD" of all the data to a work file (this UNLOAD is done in the order of one index which can be directly or indirectly specified), it then does a RELOAD. The classic REORG also creates a workfile of all the index keys, sorts them, and REBUILDs the indexes (they will be in perfect 100% physical/logical order after the table REORG). During the classic REORG, you have a large disk space requirement (for the "shadow" copy of the table") and limited table access (read only/no updates during the final phase). Most of us schedule such reorgs in an evening or weekend maintenance window.
The inplace REORG is typically slower, and does not ensure perfectly ordered data - but it has the advantage of allowing concurrent access and doesn't need a large workfile (however it requires more log space, because this type of REORG is restartable). Beware that the REORG will defer to concurrent apps running against the table and might take a long time to complete.
This is why people run REORGCHK - to avoid unnecessary REORGs, especially in a 24/7 where maintenance windows are tight.
If you set PCTFREE appropriately (for both data tables and indexes) you can minimize the frequency of REORGs. A clustered index or an MDC also reduces need for REORGs.
Best, Hal Steiner
-----Original Message-----
From: db2udbdba-ezmlmshield-x7356844.[Email address protected]
To: [Email address protected]
Sent: Mon, 28 Aug 2006 11:02 AM
Subject: Re: Is reorgchk necessary
Hi,
Thanks for reply.
But I still think, if a large table is ordered, even I run reorg, it won't take
a lot of time, right?
Maybe I should search how reorg works in fact. :)
Best wishes,
Qin
**********************
*A climber to DBA*
**********************
----- Original Message -----
From: "hsteiner" <db2udbdba-ezmlmshield-x45578342.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Monday, August 28, 2006 10:55 PM
Subject: Re: Is reorgchk necessary
> REORG can be a time and resource consuming job on a large table, so doing it
selectively (based on REORGCHK results) makes sense. It will reorganize based
on the "first" index created (typically the PK index) unless you specify a
different index. I strongly recommend looking into REORGing by an index other
than the PK - pick the index you do the most "set" processing on. For example,
REORGing an employee table by deptcode will make deptcode set queries run much
faster. When you are retrieving individual employee rows by a unique index, the
index clustering doesn't really matter.
>
> RUNSTATS should *ALWAYS* follow a REORG, so your statistics accurately reflect
the reorganized result.
>
> REBIND is a good idea so that your plans take advantage of the optimal
strategy based on the REORG. For example, before the REORG the clusterratio of a
certain index might have been poor, discouraging its use, but after the REORG
your clusterratio might be 100% again, and the rebind would optimize
accordingly.
>
> My 2 cents.
>
> Best, Hal Steiner
>
>
>
> -----Original Message-----
> From: db2udbdba-ezmlmshield-x63566171.[Email address protected]
> To: [Email address protected]
> Sent: Sun, 27 Aug 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
> ________________________________________________________________________
> Check out AOL.com today. Breaking news, video search, pictures, email and IM.
All on demand. Always Free.
>
>
> ---------------------------------------------------------------------
> 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
________________________________________________________________________
Check out AOL.com today. Breaking news, video search, pictures, email and IM. All on demand. Always Free.
DB2 & UDB email list listserv db2-l LazyDBA home page