Reorg table/index questions

[TomHorner]

I have several quick questions about reorg's, and a request (favor).

1. The documentation says that reorg "Reorganizes an index or a table"
I cannot see how one would reorg ONE particular index - is there a way
to do this? More importantly, would you WANT to do this?

2. We plan on doing inplace reorg's due to the 24x7 nature of our
database servers. Are there any "undocumented" issues with these for
V8.1, FP2 or FP6?

3. When you do a table reorg, the manual says that one needs to
"complete all database operations and release all locks before invoking
REORG TABLE." I thought the whole idea of in inplace reorg was that
the application could go on without interruption. Can I start an
inplace reorg without interrupting the users? Is this statement in the
manual true for inplace reorgs?

Request:

Does anyone have a "canned" script for running reorg's? I hate to
reinvent the wheel :-) ! I checked the IDUG and IBM sites, but could
not find any "sample" scripts. I would prefer to tweak an existing
script rather than create one from scratch.

Design:

I plan to use the output of the reorgchk command to drive the reorg
script, doing only those tables that are marked as needing a reorg. If
possible, I also hope to do this for those individual indexes that are
marked as needing a reorg. I understand that it is best to do an
"index all" reorg after a table reorg, so I may add this step after
each table reorg to the script.

Of course, this design depends on how long all this takes. Our
databases are not "huge" (they average about 2 GB), but I have no idea
how long these reorgs will take until I test them out.

Thanks in advance!

[Pierre Saint-Jacques]

Reading of your Command Ref. will help, I guarantee it!

Point-1) This refers to the ability to address the reclaiming of space
in the index leaf pages or the pages themselves where "pseudo deletes"
are present.
V8 indexes are Type 2 and when you do deletes, the key+rid combo is
"marked" to be deleted. This can be done at reorg time (reorgchk will
indicate it), on one or all indexes for CLEANUP option.
It has absolutely no bearing on reorging (cleaning up, sorting or
whatever). It's only to deal with cleaning up pages for pseudo deletes
and reclaiming empty pages or pages full of pseudo deletes.
The last option on the cmd. is to convert ALL indexes from Type1 to
Type2. You'd use this after a migration from V7 (Type1) indexes to V8
and you need to do an online reorg of the table. This requires all
indexes of the table to be Type2. So convert.... reorg ... INPLACE....

Point 2) Hopefully someone will respond to this as I'm not experienced
enough in "gotchas" for that command at V8 level.
HTH, Pierre.
TomHorner wrote:

I have several quick questions about reorg's, and a request (favor).

1. The documentation says that reorg "Reorganizes an index or a table"
I cannot see how one would reorg ONE particular index - is there a way
to do this? More importantly, would you WANT to do this?

2. We plan on doing inplace reorg's due to the 24x7 nature of our
database servers. Are there any "undocumented" issues with these for
V8.1, FP2 or FP6?

3. When you do a table reorg, the manual says that one needs to
"complete all database operations and release all locks before invoking
REORG TABLE." I thought the whole idea of in inplace reorg was that
the application could go on without interruption. Can I start an
inplace reorg without interrupting the users? Is this statement in the
manual true for inplace reorgs?

Request:

Does anyone have a "canned" script for running reorg's? I hate to
reinvent the wheel :-) ! I checked the IDUG and IBM sites, but could
not find any "sample" scripts. I would prefer to tweak an existing
script rather than create one from scratch.

Design:

I plan to use the output of the reorgchk command to drive the reorg
script, doing only those tables that are marked as needing a reorg. If
possible, I also hope to do this for those individual indexes that are
marked as needing a reorg. I understand that it is best to do an
"index all" reorg after a table reorg, so I may add this step after
each table reorg to the script.

Of course, this design depends on how long all this takes. Our
databases are not "huge" (they average about 2 GB), but I have no idea
how long these reorgs will take until I test them out.

Thanks in advance!

--
Pierre Saint-Jacques - Reply to: sescons at attglobal dot net
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

[Sean McKeough]

For point 3, you can kick off the inplace reorg while users are active
on the table...however it will not begin processing until it can get
exclusive access to the table (exclusive access is only held/needed for
a brief period of time).

TomHorner wrote:

I have several quick questions about reorg's, and a request (favor).

1. The documentation says that reorg "Reorganizes an index or a table"
I cannot see how one would reorg ONE particular index - is there a way
to do this? More importantly, would you WANT to do this?

2. We plan on doing inplace reorg's due to the 24x7 nature of our
database servers. Are there any "undocumented" issues with these for
V8.1, FP2 or FP6?

3. When you do a table reorg, the manual says that one needs to
"complete all database operations and release all locks before invoking
REORG TABLE." I thought the whole idea of in inplace reorg was that
the application could go on without interruption. Can I start an
inplace reorg without interrupting the users? Is this statement in the
manual true for inplace reorgs?

Request:

Does anyone have a "canned" script for running reorg's? I hate to
reinvent the wheel :-) ! I checked the IDUG and IBM sites, but could
not find any "sample" scripts. I would prefer to tweak an existing
script rather than create one from scratch.

Design:

I plan to use the output of the reorgchk command to drive the reorg
script, doing only those tables that are marked as needing a reorg. If
possible, I also hope to do this for those individual indexes that are
marked as needing a reorg. I understand that it is best to do an
"index all" reorg after a table reorg, so I may add this step after
each table reorg to the script.

Of course, this design depends on how long all this takes. Our
databases are not "huge" (they average about 2 GB), but I have no idea
how long these reorgs will take until I test them out.

Thanks in advance!