OS Version:Welcome to SuSE Linux 9.3
DB2 UDB Version:"DB2 v220.127.116.11" FixPak "11".
On what factors does REORG of a table depend on in terms of time.
A table (TABLEA) is taking less time for REORG now (about 4 min) than before (7 min) inspite of more number of rows. And no new indexes were created/dropped on this table.
The only thing that has changed was db2 restart. So are there any variables/registry settings that get reset and affect REORG time.
Thanks for your quick response. But this table gets refreshed every day (export/load). And for couple of months it has been taking 7 min but after the restart its taking 4 min and the number of rows exported/loaded keep increasing day by day.
How often are you doing the reorg? Daily after export/load
What is the tablespace (sms or dms)? DMS
Is data ever deleted, or just added to the table? Load runs with replace option
What about updates? Not sure how often if any
The source table for the export is on AS400 and the target is on AIX. I do not have access to the source database so I am not sure if it was recently reorged , if it has same indexes as target. But the table layout should be same as we are doing a select * in the export and performing the load.
But do you think if the source has already been reorged then it might impact the reorg time of target.
What I am implying is that the data in the load is already in the order of the clustering index (if you have one) on TABLEA. If you do not have a clustering index, then the order does not matter. Anyway, TABLEA has been reorged daily, so the data pages are laid out so well that subsequent reorgs will be fast.