Running concurrent reorg / runstats on DB2 ESE for Aix V8.2

[db2udb]

Hi, I have just taken over as the DBA for a database that has not had any reorgs/runstats run against it for the last three years. As a first step, I have just run reorg/runstats against the system catalog tables & rebound packages.

I next intend to reorg/runstats all the application tables that I have identified as needing this and due to the remote possibility of this causing an adverse affect on performance, it has been decided that I should perform this during an application outage after having first taken a full offline backup in case I should need to backout. (i.e. I don't have the option of running reorgs/runstats while the application is running)

My question is that I have an outage window of 4 hours but reorgs/runstats if run in sequence will take in excess of 10 hours. Is it possible to run reorgs and runstats (against different tables) concurrently ? The tables are not split over nodes - there is just a single node. All the tables reside in one tablespace and all the indexes are in another. Does anyone have any experience of this. I can't find anything in the DB2 for LUW manuals that cover this.

Many thanks, David

[sakumar9]

ya sure, you can run RUNSTATS in parallel.

-- Regards
Sanjay

[docdiesel]

Hi,

yes, you can run Reorg and Runstats parallel. But another question is resulting from here: Are your tables (or tablespaces) which you want to do reorg on the same or different hard disks?

Running such processes parallel on the same filesystem may slow down everything, depending on the structure of your db, the physical parameters of your storage etc., because in such cases the r/w heads usually will need to do a lot of seeks across your hard disk(s).

Regarding the fact that your db passed years without the aid of an db2 admin, I assume it's not part of a online system with high frequent write actions.You could think about offloading the reorg/runstats to another, similar system (backup/restore of db or tablespaces to similar system, reorg there, backup/restore back) while the main system stays accessable.

Besides, I'm used to nightly online reorgs and runstats actions on my webapp databases, and never experienced any trouble with DB2.

Regards,

Bernd

[db2udb]

Hi, thanks for your replies.

In answer, I have established that runstats appear to run fine in parallel.
In terms of disks, our database is on a SAN so should be properly striped across many hard disks. Unfortunately, this database (25 GB in size) IS actually an OLTP database !

As regards REORG's, I have found the following SQL useful

select *
from table (SNAPSHOT_TBREO RG ('databasename' , -2) ) as snapshot where reorg_end is null with ur

The columns REORG_PHASE and REORG_CURRENT_C OUNTER have helped me identify when reorgs submitted in parallel seem to be contending, but I have found precious little information as regards this from IBM or on the WWW.

What I have noticed is that if I try to reorg a table by an index using Tempspace, REORG_PHASE 1 (which I believe is the copy of the table to tempspace) seems to be able to run in parallel. However, REORG_PHASE 2 (whatever that is ?!) seems to allow only one reorg to run at a time if they are both defined to use the same tempspace. If they use seperate tempspace, then they seem to run fine in parallel. When a reorg runs without tempspace defined for use, then the reorgs seem to be running in parallel ok. One thing I'm not sure about is that there are sometimes gaps (at the beginning of phase 2 and 3 and especially during phase 4) when the REORG_CURRENT_C OUNTER stays at zero for a long time - not sure if this is contention or not.

Performance is a seperate issue ... these reorgs take 4 hours for the largest tables (35 million rows) and I'm looking to split up tables into seperate tablespaces and to define more than one bufferpool and utilize 8, 16 and 32 k tablespace/bufferpool pages to see how this helps.

Thanks, David

[cburnett]

Usually find the following approach works effectively for reorgs:

1. Turn on Autorunstats
2. Reorgchk current statistics
3. Any tables that need reorgs (any *'s) kick off an online table reorg
4. Use SNAPSHOT_TABREO RG to limit the number of concurrent reorgs (say, 5 max)
5. For index reorgs, any *'s except the first one
6. Check their is enough space in the index tablespace for the shadow copy
7. Before running backups, pause or stop online table reorgs

Autoreorg can only do offline table reorgs which usually isn't very practical fore most customers. With online table reorgs, there can be contention issues with backups hence the need to stop the reorgs.