reorg

A few weeks ago Natasha (my girlfriend) told me about a post she wrote on her blog (Reorg) which described some basecamp best practices.

You need to monitor the system (ideally in an automated fashion) to determine when a REORG needs to be run.

COMPACT ON is the default for the redistribute utility, where it performs space compaction much like an offline table reorg does.

Conversations with folks at IBM reveal a viewpoint within the company that DBAs may reduce REORG frequency.

REORG will restore a disorganized index to a well-organized state, at a cost of some CPU consumption.

Word disappears, and Outlook 2003 appears, showing an e-mail message addressed to Andrew Fuller with a subject of "department reorg. "

Taken together, they represented the biggest reorg of the Pentagon in a generation.

Each of these operations: redistribute, table reorg, index create, and runstats require scanning the table data once.

For later versions of DB2 you can remove or ignore the REORG statements or their generation SQL, which are not required to be run.

This is also why maintenance operations like REORG cannot be performed on an individual local index.

The table dictionary is built only when a REORG is performed, after which the data in the table can be compressed.

The reorg command reorganizes the tables by reconstructing the rows to eliminate fragmented data.

Also, there should not be any need to run the REORG utility.

Before you execute the generated script reorg. sql, you need to create the ALTER TABLE statements to activate value compression.

The script below will scan for AW fact tables named using the wildcard expression %_MEASURES and scripts ALTER TABLE and REORG commands.

Use this utility after massive changes to the data and possibly after running REORG.

In DB2 9. 1, the compression takes effect only after the table dictionary is built, which is usually during the table REORG phase.

In addition, maintenance commands such as reorg and rebind are also included to restore the state of the database after command deployment.

However, reserving free space only trades disk space for time, potentially letting you go longer between REORGs.

A third problem can occur when performing RUNSTATS or REORG on your table.

Before you apply new stats to the database, I recommend you reorg the data first.

The REORG TABLE step between two scenarios will establish the clustering order.

When a table has been reorganized with the REORG utility.

Thus the UPDATE statement fails to find the row with the ROW CHANGE TOKEN that was retrieved before the REORG occurred.

Both the RUNSTATS and REORG utilities use log files when moving table data around.

In addition to the RUNSTATS, you can automate database backups and REORGs (defragmentation of table and index data).

The REORG uses a sampling technique to build the dictionary.

And since they are written in physical clustering sequence, there should be no need to REORG.

In most cases, ALTER SET DATA TYPE requires table reorganization (reorg) because it changes the physical row format.

If you use the offline table reorg or rowcompestimate option in the inspect tool, data in the table is fully compressed.

Therefore, REORG usually produces a dictionary that is more representative of the data in the entire table space or partition.

In order to synchronize with the new ADC feature, the behavior of KEEPDICTIONARY for the offline table reorg has changed.

Based on the data and trends you observe , adjust your REORG frequency and PCTFREE number accordingly .

Deploying table spaces on SSDs can reduce the need to REORG.

In DB2 LUW, the REORG INDEX utility can perform both functions.

For more information, see the " 2. 4 Behavior changes in row compression using offline reorg utility" section.

Additionally, DSNACCOX with DB2 10 has specific code to reduce the REORG requirements for table spaces residing on SSDs.

Extra indexes also add to the CPU consumption of certain DB2 utilities, such as the REORG utility.

They can increase logging during the REORG process.

We all know the mantra: reorg, runstats, and rebind.

The following examples illustrate partition-level REORG on a table that has no global indexes

Behavior changes in row compression using the offline reorg utility

Save personnel time managing the REORGs

You should run reorg and runstats after creating new indexes, as shown below

Reorg in Progress 0x400 This is a transient state that is only in effect during a reorg operation. Issue a REORG TABLE command