Yes, you understood me correctly regarding the UNLOAD/RELOAD logic. The Index used isn't always the CLUSTER index; however, it can be explicitly overridden to be a different index, and if there is no CLUSTER index, it will use the INDEX with the lowest id number.
My warning about the inline REORG deferring to other jobs is that if you have a long running RR type job, it can hold up the REORG considerably. Of course the good news is that you have concurrent READ/WRITE access during the REORG. Another drawback is that your indexes aren't perfectly reorganized in an online reorg as they would be in an offline. By the way in addition to suspending and restarting an online reorg, it can be throttled down.
I believe the answer to your question about what if you paused the online REORG and then inserted 100 rows then continued it would depend on how far you can progressed prior to the pause.
And, finally, yes, going MDC is a major consideration with several tradeoffs. I was just mentioning that this type of table needs less frequent REORGs.
Good luck,
Hal Steiner
-----Original Message-----
From: db2udbdba-ezmlmshield-x16600200.[Email address protected]
To: [Email address protected]
Sent: Mon, 28 Aug 2006 11:47 AM
Subject: Re: Is reorgchk necessary
Hi,
Thank you. I didn't realize that offline REORG does an "UNLOAD" and "RELOAD"
previously. I thought it only moves the neccessary data.
So I can understand the offline REORG as: SELECT * FROM tabname ORDER BY columns
in cluster index > $workfile and then reload from $workfile, right?
That makes sense that it is time consuming.
I can't understand what you said by: Beware that the REORG will defer to
concurrent apps running against the table
The online REORG, which certainly can't work as well as offline reorg, but can
allow the other applications read/write the table at the same time as reorging,
may be paused and restarted. Suppose I have 100 records in the table and now I
perform an online REORG, then I pause the reorg, and then I insert 200 more
records, then I restart the process. Will the 200 new records be taken into
consideration?
I think MDC can be used in only special cases. Depend on the detailed value of
the columns, right?
Best wishes,
Qin
**********************
*A climber to DBA*
**********************
----- Original Message -----
From: "hsteiner" <db2udbdba-ezmlmshield-x5474751.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Monday, August 28, 2006 11:23 PM
Subject: Re: Is reorgchk necessary
>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.
>
>
> ---------------------------------------------------------------------
> 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