Seeking some planning info for DB2 UDB LUW REORG

[News]

I have 18 years of mainframe DB2, and I just starting working with UDB LUW
V8.1 FixPack 6 on Sun Solaris last week.

I have been dismayed on the lack of detailed info available in the manuals
on tuning and monitoring REORGS on large tables. I am used to the wealth
of usage notes in mainframeland.

I am recommending the implementation of clustering indexes and wholesale
reorging of several large tables (up to 37GB)/ 200 mill plus rows.

I am familiar with the doc on the "Classic Reorg" and "In Place Reorg" and
I am planning on requesting an outage and using the "Classic Reorg" for
this process, while is going to include the dropping and recreating of
indexes to implement clustering prior to the reorg.
I could really use some work arounds or deeper detail on the following
issues:

1. There is no CREATE INDEX DEFER YES, so I am assuming that the
DROP/RECREATE of index as a clustering index will actually go through the
laborious process of building the index prior to the reorg, and hold a
catalog lock the whole time. True ?? I hope I'm missing something here.

2. There is no -DISPLAY UTILITY command. Familiar with the DB2 LIST
HISTORY command, but this doesnt seem to display anything till the job is
done. Somebody please tell me there's a way to monitor this thing while
its unloading,sorting,reloading,building indexes, waiting on locks, etc..

3. All tablespaces currently exist in one DB on the instance. Does this
mean that all my reorg jobs will be fighting for temp tablespace and sort
resources if I attempt to run concurrently ?

4. WHere are the calculations to figure out the projected temp tabelespace
and sort memory requirements based on the table size and number of
indexes ?

I dont mean to slam UDB, but where the heck is all that information for
doing this sort of thing ?

Thanks, Jeff Kluth
Email: be*********@comcast.net

[Mark A]

"News" <je**@remulaksolutions.com> wrote in message
news:qqWdnZhh1YJ6gZPfRVn-

1. There is no CREATE INDEX DEFER YES, so I am assuming that the
DROP/RECREATE of index as a clustering index will actually go through the
laborious process of building the index prior to the reorg, and hold a
catalog lock the whole time. True ?? I hope I'm missing something here.
Not sure that how long the catalog is locked in that situation, but I doubt
the lock on the catalog is going to be a problem (I could be wrong). I
assume it does row locking.
2. There is no -DISPLAY UTILITY command. Familiar with the DB2 LIST
HISTORY command, but this doesnt seem to display anything till the job is
done. Somebody please tell me there's a way to monitor this thing while
its unloading,sorting,reloading,building indexes, waiting on locks, etc..
You can get various snapshots that will tell you the locking situation (and
other information at any given time). There is a LOAD QUERY command. Don't
recall any others, besides using a snapshot of the application thread doing
the reorg.
3. All tablespaces currently exist in one DB on the instance. Does this
mean that all my reorg jobs will be fighting for temp tablespace and sort
resources if I attempt to run concurrently ?
Yes, but you can specify what tempspace to use for each reorg. If you use
SMS temp space (recommended) then it should not be a problem if you have
enough space on the mount point.
4. WHere are the calculations to figure out the projected temp tabelespace
and sort memory requirements based on the table size and number of
indexes ?

I dont mean to slam UDB, but where the heck is all that information for
doing this sort of thing ?

For some of the other utilities besides reorg, check out this manual: "Data
Movement Utilities Guide and
Reference." But overall, I agree that usage doc is not as good.

[News]

THanks Mark. The LOAD QUERY command looks interesting. I'm starting to think
I may be best off just unloading all this data, doing an external sort,
droping/recreating the objects w new clustering and LOADing them. I seem to
recall doing it that way before in the old mainframe days...

"Mark A" <no****@nowhere.com> wrote in message
news:Uu********************@comcast.com...

"News" <je**@remulaksolutions.com> wrote in message
news:qqWdnZhh1YJ6gZPfRVn-

1. There is no CREATE INDEX DEFER YES, so I am assuming that the
DROP/RECREATE of index as a clustering index will actually go through the
laborious process of building the index prior to the reorg, and hold a
catalog lock the whole time. True ?? I hope I'm missing something here.

Not sure that how long the catalog is locked in that situation, but I
doubt
the lock on the catalog is going to be a problem (I could be wrong). I
assume it does row locking.

2. There is no -DISPLAY UTILITY command. Familiar with the DB2 LIST
HISTORY command, but this doesnt seem to display anything till the job is
done. Somebody please tell me there's a way to monitor this thing while
its unloading,sorting,reloading,building indexes, waiting on locks, etc..

You can get various snapshots that will tell you the locking situation
(and
other information at any given time). There is a LOAD QUERY command. Don't
recall any others, besides using a snapshot of the application thread
doing
the reorg.

3. All tablespaces currently exist in one DB on the instance. Does this
mean that all my reorg jobs will be fighting for temp tablespace and sort
resources if I attempt to run concurrently ?

Yes, but you can specify what tempspace to use for each reorg. If you use
SMS temp space (recommended) then it should not be a problem if you have
enough space on the mount point.

4. WHere are the calculations to figure out the projected temp
tabelespace
and sort memory requirements based on the table size and number of
indexes ?

I dont mean to slam UDB, but where the heck is all that information for
doing this sort of thing ?

For some of the other utilities besides reorg, check out this manual:
"Data
Movement Utilities Guide and
Reference." But overall, I agree that usage doc is not as good.

[Mark A]

"News" <je**@remulaksolutions.com> wrote in message
news:i9********************@comcast.com...

THanks Mark. The LOAD QUERY command looks interesting. I'm starting to think I may be best off just unloading all this data, doing an external sort,
droping/recreating the objects w new clustering and LOADing them. I seem to recall doing it that way before in the old mainframe days...

That would certainly work if you are concerned about the progress of a
reorg. Also comes in handy if you need to remove some old data from the
table (just select the data you do want loaded back). Not sure if you can
use an ORDER BY with the export utility, but that would make it even easier.

[phu]

Mark A <no****@nowhere.com> wrote:
[snip]

3. All tablespaces currently exist in one DB on the instance. Does this
mean that all my reorg jobs will be fighting for temp tablespace and sort
resources if I attempt to run concurrently ?

Yes, but you can specify what tempspace to use for each reorg. If you use
SMS temp space (recommended) then it should not be a problem if you have
enough space on the mount point.

I think that you can specify the temp tablespace for the DAT and INX
objects, but when you issue a REORG with LONGLOBDATA, the specified
tempspace doesn't really mean anything. I think what happens when
you do not specify a temp tablespace is that one is chosen in a round-
robin fashion, amongst those that can satisfy the requirements (e.g.
pagesize). So, in the case for the original poster, if you have
multiple concurrent CLASSIC REORGs, and multiple temporary tablespaces,
then there is less likelyhood of collision/competition for the same
tempspace.