What is the use of reorg command in ibm db2 db ? What does Reorg do internally ? Is it necessary to run reorg if new indexes are created on the table ?
+1
A:
I believe it works the same as the DBCC DBREINDEX command in SQL Server. It's akin to disk defragmentaiton in that the indices get fragmented over time, as records get deleted, etc. it certainly speeds things up...
You may already have this link, but it's described here...
Added
I'm trying to answer your question on how it works and why it's necessary, and I'm not finding anything specific to DB2. The best article I've found is related to SQL Server, but the concept is exactly the same. This article explains what index fragmentation is and why it slows things down.
Carrying this over to the IBM DB2, the files are subject to the same defragmentation in much the same way.
David Stratton
2010-08-05 17:50:49
Just goes to show that documentation of other db vendors is better compared to that of IBM DB2
Cshah
2010-08-06 09:19:06
+1
A:
When you delete a record (or row) in (at least for the iSeries) DB2, it marks the record for deletion but does not physically delete the record. When reorg is run, it takes those records marked for deletion and then physically deletes them. I believe it also moves all of the data records around for optimal performance in this process as well. On the iSeries, this need can be rendered obsolete by telling the file (or table) to reuse delete records.
As I hinted at, I know this is the case on DB2 for iSeries (or IBM i). I can only presume that this process is similar on DB2.
Mike Wills
2010-08-05 18:34:11
A:
What it actually does is put the physical records back into primary key order, with the correct amount of free space (specified by PCTFREE) in the right places.
Obviously this also requires the indexes are rebuilt as well which results in nicely balanced btrees.
James Anderson
2010-08-18 01:04:19