What is the use of reorg command in IBM DB2?
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 ?
Answer
The REORG operation performs the following functions:
Checks referential integrity, if applicable for the target table, and either deletes rows that violate it or invalidates any affected indexes. (Referential integrity is the relational property that each foreign-key value in a table exists as a primary-key value in the referenced table.
Performs an internal reorganization of one or more of the indexes for the table (all types) to improve the internal storage of this information and thereby the performance when the index is used to access data. It can rebuild all indexes, selectively rebuild one or more named indexes, or selectively rebuild one or more segments of one or more named indexes.
Populates a DEFERRED index that is created with a CREATE INDEX statement. A DEFERRED index is an empty index structure that can be populated at a later date.
Rebuilds precomputed views. For example, if precomputed view maintenance is set to OFF in your application, you can use the REORG command to rebuild precomputed views only, without touching the indexes on the target table. Alternatively, you can use the REORG command to rebuild both indexes and views.
In addition to rebuilding aggregate table data, the REORG command rebuilds indexes on aggregate tables.
A REORG operation is necessary in the following cases:
To rebuild the affected indexes if you use a database restore operation to restore individual segments of a table or index.
Whenever modifications to a database affect more than about 30 percent of the data, run the TMU with a REORG statement for any tables directly modified. Periodically rebuilding such tables and indexes with a REORG statement ensures referential integrity and optimal performance.
To reorganize invalid STAR indexes. Certain operations can invalidate STAR indexes. For example, increasing the MAXROWS PER SEGMENT or the MAXSEGMENTS parameter on a table, or using an ALTER statement to expand a segment, can invalidate STAR indexes on tables that reference the altered table. These operations always generate a warning message that says STAR indexes based on the altered table might be invalid, in which case the affected STAR indexes need to be reorganized. You can either reorganize affected indexes when the message is issued or schedule the REORG operation for a more convenient time. However, any non-query (INSERT, UPDATE, or DELETE) operation against a table that has an invalid index results in an error message that says the index must be reorganized. You must perform a REORG operation before the table can be accessed for an INSERT, UPDATE, DELETE, or LOAD operation.
REORG is unnecessary in the following cases:
If no changes are made to the database except by complete loads of data.
If the table and indexes are segmented alike and new index data is loaded into new index segments corresponding to new table segments.