Database maintainance utilities(2)

The REORG Utility:

This utility command can be used to re-organize tables and indexes either in online or in offline mode to improve storage efficiency and reduce the I/O access cost. This utility can also be used to implement various compression algorithms for tables and indexes. The basic command syntax for REORG is this:

  REORG TABLE [TableName]

<INDEX [IndexName]>

<ALLOW READ ACCESS | ALLOW NO ACCESS>

<USE [TmpTSName]>

<INDEXSCAN>

<LONGLOBDATA <USE [LongTSName]>>

<KEEPDICTIONARY | RESETDICTIONARY>

<RECLAIM EXTENTS [ALLOW [WRITE | READ | NO] ACCESS]>

Or

 

REORG TABLE [TableName]

<INDEX [IndexName]>

INPLACE

[ALLOW READ ACCESS | ALLOW NO ACCESS]

[<TRUNCATE | NOTRUNCATE TABLE> | <CLEANUP OVERFLOWS>]

[START | RESUME] | [STOP | PAUSE]

 

Or

 

REORG TABLE [TableName]

<INDEX [IndexName]>

INPLACE

[STOP | PAUSE]

 

Or

 

REORG [INDEXES ALL FOR TABLE [SrcTableName] |

       INDEX [SrcIndexName] <FOR TABLE [SrcTableName]>]

<ALLOW READ ACCESS | ALLOW WRITE ACCESS | ALLOW NO ACCESS>

<CLEANUP ONLY ALL | CLEANUP ONLY PAGES | CONVERT>

Consider the following:

 

TableName: Identifies the name of the table whose data is to be re-organized

IndexName: Identifies the name of index to use to order the data stored in the table that is to be re-organized

TmpTSName: Identifies the system temporary table space in which the database manager is to temporarily store a copy of the table to be re-organized

LongTSName: Identifies the temporary table space that the database manager is to use for rebuilding long data, such as LOB and CLOB

SrcTableName: Identifies the name of the table whose associated indexes are to be re-organized

SrcIndexName: Identifies the name of the index whose physical pages are to be re-organized

Online (inplace) table reorganization reorganizes a table and allows full access to data in the table. During an online table REORG operation, portions of data are organized sequentially within the current address space, re-establishing the clustering, reclaiming the free space, and eliminate overflow rows (if any) instead of copying it to a temporary table space (this is the behavior in an offline REORG operation).

 There are four phases in an online table reorganization, and they are as follows:

 Select a range:

The database manager selects a range of sequential pages based on the extent size for REORG operation. The minimum number of pages which it selects is 32.

Vacate a range:

 The database manger performs these steps:

The REORG process moves all of the pages of selected range to free pages in the table.

Each row that is moved leaves behind a REORG table pointer (RP) record containing the record ID (RID) of the row's new location.

The row is placed on a free page in the table as a REORG table overflow (RO) record that contains the data.

Once it finishes moving a set of rows, it waits until all applications that are accessing data in the table are finished. Any old connection using these rows uses old RIDs (old scanner) when table data is accessed; however, any new connection accessing the same data is accessed via new RIDs (new scanner) to access the data.

Once all the old scanners are complete, the REORG utility cleans up the moved rows, deleting RP records and converting RO records into regular records.

Fill the range:

Once all the rows in a specific range are vacated, the database manager will then write them back in a reorganized format; they are sorted according to any indexes that were used, and obeying any PCTFREE restrictions that were defined.

When all of the pages in the range are rewritten, the next sequential pages in the table are selected, and the process is repeated.

Truncate the table:

When all of the pages in the table are re-organized, the table is truncated to reclaim space. If the NOTRUNCATE option is specified, the re-organized table is not truncated.

If you want to perform an online reorganization on table DB2INST1.EMPLOYEE, you can execute the REORG commands shown here, based on need:

 

    REORG TABLE db2inst1.employee INPLACE;

    DB20000I  The REORG command completed successfully.

    DB21024I  This command is asynchronous and may not be effective immediately.

   

    REORG TABLE db2inst1.employee INPLACE FULL TRUNCATE TABLE START;

    DB20000I  The REORG command completed successfully.

    DB21024I  This command is asynchronous and may not be effective immediately.

   

    REORG TABLE db2inst1.employee INPLACE FULL;

    DB20000I  The REORG command completed successfully.

    DB21024I  This command is asynchronous and may not be effective immediately.

   

    REORG TABLE db2inst1.employee INPLACE CLEANUP OVERFLOWS;

    DB20000I  The REORG command completed successfully.

    DB21024I  This command is asynchronous and may not be effective immediately.

 

You can use the RECLAIM EXTENTS option within the REORG command to re-organize and reclaim extents that are not being used in column-organized BLU Acceleration tables, multidimensional cluster (MDC) tables, and insert time-clustering (ITC) tables, as shown here:

REORG TABLE demo.employee RECLAIM EXTENTS;

The following are a few REORG command tool examples:

 If you want to re-organize a table online based on an index order, you can use the following:

 REORG TABLE [SchemaName.TableName] INDEX [SchemaName.IndexName] INPLACE ALLOW WRITE ACCESS;

If you want to re-organize indexes on a table online and cleanup all pseudo deleted keys and pseudo empty pages, you can use the following:

 REORG INDEXES ALL FOR TABLE [SchemaName.TableName] ALLOW WRITE ACCESS CLEANUP;

If you want re-organize a partition within a range partition table, you can use the following:

 REORG TABLE [SchemaName.TableName] INDEX [SchemaName.IndexName] ON DATA PARTITION [PartitionName];

If you want to re-organize a table and associated indexes using a temporary table space and rebuild the compression dictionary, you can use the following:

 REORG TABLE [SchemaName.TableName] USE [TempTableSpaceName] RESETDICTIONARY;