Shadow data sets for REORG TABLESPACE

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

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';

For a partitioned table space, Db2 returns rows from which you select the row for the partitions that you want to reorganize.

For example, assume that you have a ten-partition table space and you want to determine a naming convention for the data set in order to successfully execute the REORG utility with the SHRLEVEL CHANGE PART 2:6 options. The following queries of the Db2 catalog tables SYSTABLEPART and SYSINDEXPART provide the required information:

SELECT DBNAME, TSNAME, PARTITION, IPREFIX FROM SYSIBM.SYSTABLEPART
  WHERE DBNAME = 'DBDV0701' AND TSNAME = 'TPDV0701'
  ORDER BY PARTITION;
SELECT IXNAME, PARTITION, IPREFIX FROM SYSIBM.SYSINDEXPART
  WHERE IXNAME = 'IXDV0701
  ORDER BY PARTITION;

The preceding queries produce the information that is shown in the following table.

The following table shows the results from the first query.

The following table shows the results from the second query.

To execute REORG SHRLEVEL CHANGE PART 2:6, you need to preallocate the following shadow objects. The naming convention for these objects use information from the query results that are shown in the previous tables.

vcatnam.DSNDBC.DBDV0701.TPDV0701.J0001.A002
vcatnam.DSNDBC.DBDV0701.TPDV0701.I0001.A003
vcatnam.DSNDBC.DBDV0701.TPDV0701.J0001.A004
vcatnam.DSNDBC.DBDV0701.TPDV0701.I0001.A005
vcatnam.DSNDBC.DBDV0701.TPDV0701.I0001.A006
vcatnam.DSNDBC.DBDV0701.IXDV0701.J0001.A002
vcatnam.DSNDBC.DBDV0701.IXDV0701.I0001.A003
vcatnam.DSNDBC.DBDV0701.IXDV0701.J0001.A004
vcatnam.DSNDBC.DBDV0701.IXDV0701.I0001.A005
vcatnam.DSNDBC.DBDV0701.IXDV0701.I0001.A006

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.

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:

When you preallocate data sets for indexes, create the shadow 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 each nonpartitioned secondary index.

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. For more information about this naming scheme, see the information about the shadow data set naming convention at the beginning of this topic.

Estimating the size of shadow data sets

If you have not changed the value of FREEPAGE or PCTFREE, the amount of required space for a shadow data set is comparable to the amount of required space for the original data set.

Preallocating shadow data sets for REORG PART

By creating the shadow data sets before executing REORG PART, even for Db2-managed data sets, you prevent possible over-allocation of the disk space during REORG processing. When reorganizing a partition, you must create the shadow data sets for the partition of the table space and for the partition of the partitioning index. In addition, before executing REORG PART with SHRLEVEL REFERENCE or SHRLEVEL CHANGE on partition mmm of a partitioned table space, you must create a shadow data set for each nonpartitioning index that resides in user-defined data sets. Each shadow data set is to be used for a copy of the index and must be as large as the entire original nonpartitioned index. The name for this shadow data set has the form catname.DSNDBx.dbname.psname.y0mmm.Annn.

Shadow data sets when materializing a MOVE TABLE operation

FL 508 If REORG is materializing a pending MOVE TABLE operation (ALTER TABLESPACE with the MOVE TABLE clause), the utility allocates shadow data sets for all of the following objects:

• The source nonpartitioned table space
• The target partition-by-growth table space
• Indexes on all tables in the reorganized table spaces, regardless of whether the table is being moved

Shadow data sets are not allocated for any LOB or XML table spaces.