Shadow data sets for REORG INDEX

When you execute the REORG INDEX utility with SHRLEVEL REFERENCE or SHRLEVEL CHANGE, the utility uses shadow data sets.

For user-managed data sets, you must preallocate the shadow data sets before you execute REORG INDEX with SHRLEVEL REFERENCE or SHRLEVEL CHANGE. If an index or partitioned index resides in Db2-managed data sets and shadow data sets do not already exist when you execute REORG INDEX, Db2 creates the shadow data sets. At the end of REORG processing, the Db2-managed shadow data sets are deleted. You can create the shadows ahead of time for Db2-managed data sets.

Shadow data set names

Each shadow data set must have the following name:

catname.DSNDBx.dbname.psname.y000z.Lnnn

In the preceding name, the variables have the following meanings:

variable

meaning

catname

The VSAM catalog name or alias

x

C or D

dbname

Database name

psname

Table space name or index name

y

I or J

z

1 or 2

Lnnn

Partition identifier. Use one of the following values:

A001 through A999 for partitions 1 through 999
B000 through B999 for partitions 1000 through 1999
C000 through C999 for partitions 2000 through 2999
D000 through D999 for partitions 3000 through 3999
E000 through E996 for partitions 4000 through 4096

Begin general-use programming interface information.

To determine the names of existing data sets, execute one of the following queries against the SYSTABLEPART or SYSINDEXPART catalog tables:

SELECT DBNAME, TSNAME, IPREFIX
   FROM SYSIBM.SYSTABLEPART
   WHERE DBNAME = 'dbname'
    AND TSNAME = 'psname';

SELECT DBNAME, IXNAME, IPREFIX
   FROM SYSIBM.SYSINDEXES X, SYSIBM.SYSINDEXPART Y
   WHERE X.NAME = Y.IXNAME
    AND X.CREATOR = Y.IXCREATOR
    AND X.DBNAME = 'dbname'
    AND X.INDEXSPACE = 'psname';

End general-use programming interface information.

Defining shadow data sets

Consider the following actions when you preallocate the data sets:

Allocate the shadow data sets according to the rules for user-managed data sets.
Define the shadow data sets as LINEAR.
Use SHAREOPTIONS(3,3).
Define the shadow data sets as EA-enabled if the original table space or index space is EA-enabled.
Allocate the shadow data sets on the volumes that are defined in the storage group for the original table space or index space.

If you specify a secondary space quantity, Db2 does not use it. Instead, Db2 uses the SECQTY value for the table space or index space.

Recommendation: Use the MODEL option, which causes the new shadow data set to be created like the original data set. This method is shown in the following example:

DEFINE CLUSTER +
   (NAME('catname.DSNDBC.dbname.psname.x0001.L001') +
   MODEL('catname.DSNDBC.dbname.psname.y0001.L001')) +
   DATA                                               +
   (NAME('catname.DSNDBD.dbname.psname.x0001.L001') +
   MODEL('catname.DSNDBD.dbname.psname.y0001.L001') )

Creating shadow data sets for indexes

Db2 treats preallocated shadow data sets as Db2-managed data sets.

When you preallocate shadow data sets for indexes, create the data sets as follows:

Create shadow data sets for the partition of the table space and the corresponding partition in each partitioning index and data-partitioned secondary index.
Create a shadow data set for logical partitions of nonpartitioned secondary indexes.

Use the same naming scheme for these index data sets as you use for other data sets that are associated with the base index, except use J0001 instead of I0001.

Estimating the size of shadow data sets

If you do not change the value of FREEPAGE or PCTFREE, the amount of space that is required for a shadow data set is approximately comparable to the amount of space that is required for the original data set.