sp_iqrelocate procedure

Relocates specified tables and indexes on main dbspaces with relocate mode to main dbspaces with readwrite mode.

sp_iqrelocate ‘target [ maxsize nMB ] [ resources resource-percent ]’

target: { database | { table table-name | index index-name } […] }

DBA authority required.

This procedure relocates specified tables and indexes on main dbspaces with relocate mode to main dbspaces with readwrite mode. If the database keyword is specified, then all objects found in relocate dbspaces are relocated. If one or more tables or indexes are specified, only the specified tables and indexes are relocated. Data that belongs to the specified tables or indexes that does not reside on relocate dbspaces is not relocated.

sp_iqrelocate can also be used to relocate tables and indexes on local dbspaces.

An object is relocated by creating a new version, the same as for DML operations. This new version must be committed before old versions are relocated and the dbspace is dropped. sp_iqrelocate does not automatically commit. You must commit the changes before they are persistent.

You can use the optional maxsize keyword to limit the amount of data relocated by the sp_iqrelocate procedure.

sp_iqrelocate returns a result set that indicates the numbers of blocks that were relocated for each object specified. The status column for each object is as follows:

The following command relocates the index t1c1hg on table t1 and relocates the entire table t2:

sp_iqrelocate 'index t1c1hg table t2';

All data on dbspaces with the readwrite mode of relocate can be relocated using a single sp_iqrelocate command. The following command relocates all data on relocate dbspaces in the database:

sp_iqrelocate 'database';

Note that the four objects with relocation status of no relocs were relocated by the previous sp_iqrelocate command.