DB2 Reorg

[arun010101]

Hi Friends,

I wanted to know if there is any way in which we can test if the Reorg has actually improved the system performance. In my case, the concerned database is considerably small and the CPU time used has not changed much. In the sense, does this utility have a feature of creating a report of any sort which can tell the user:

1. that so much space has been reclaimed,
2. the clustering ratio has been improved from x to y

I would be glad if someone could explain how this is to be done.

Thanks,
Arun

 

[blom0344]

run this BEFORE and AFTER reorg:
reorgchk update statistics on table <schema>.<tablename>

from command window. It will give some details of no. of leafs and clusterratio.

The REORGCHK command will also give you an indication whether a table should be reorganized.

Do not forget that reorganization can be especially beneficial for large facttables. If you only have small tables with full table scans executed , the advantages may be small

T. Blom
Information analyst
tbl@shimano-eu.com

 

[kozlow]

FYI... Found if you run the reorgchk with update statistics, it took forever.... Came up with quering all the tables and then reading them into a runstats one at a time. This worked much faster. We then run a &quot;reorgchk current statistic on table all&quot;.

I then scan the output of the reorgchk to decide which tables and indexes need to be reorged..... We are at UDB 7.2, so the only way to reorg an index is to drop and recreate.... Which I do, using db2look. I run runstats again on all the tables that have been reorged (as well as tables with indexes that have been reorged).

We then do a second reorgchk to compare results. This is run weekly (Sat) after the offline backup completes.

Each Sunday morning I run 5 queries borrowed from the application and time them. I compare the results in a spreadsheet. Kind of gives us an idea on how the users will see it during the week...