Is there any parameters that can be modified to help with reorg performance. We are running UDB Ver 7.2.

It has taken as much as 4 hours to reorg a 4Gig table......

I will tell u abt a parameter which comes in handy in OS/390. Check to see if there is a corresponding parameter for UDB.

REUSE - When this parameter is used, it does not delete define the underlying file while doing a Reorg. It just reuses the file and it saves a lot of time because a lot of time spend in deleting and reallocating a large file is saved.

You may also want to implement 'Conditional Reorgs' to see if running a Reorg is neccessary in the first place.

Specify the USE tablespace-name clause on the reorg (a system temporary table space in which to store a temporary copy of the table being reorganized). For a 8KB, 16KB, or 32KB table object, the page size of any system temporary table space explicitly specified by the user must match the page size of the tablespace being reorged.

The temporary tablespace should be larger (preferably several times the size) than the table being reorged (if defined as DMS). A SMS temporary tablespace might take a little longer because it must constantly be increased as needed (but at least it will not run out of space if there is sufficient space on the volume).

I wrote a script that first runs a db2 runstats on all the tables. Then a reorgchk with current stats (this works faster then running a reorgchk with update..... Then I decide if a table or index is up for reorg..... I then run:

db2 -v reorg table $CREATOR.$TABLE use tempspace1
or
db2 -v reorg table $CREATOR.$TABLE INDEX $CREATOR.$INDEX use tempspace1

and of course, a runstat after it completes.

I recreate indexes if marked and then reorg the associated table.

My script handles not reorging any table twice.

Our tempspace1 is SMS, as recommended, and my data and index tablespaces are DMS. They are on seperate RAID devices with plenty of room.

I will look into the other suggestions, Thanks for all the input.......