DDLIN data sets in REORG PLUS

REORG PLUS does not explicitly execute the SQL statements in the DDLIN data set. Instead, it extracts the partition number and the associated limit key values from the statements. REORG PLUS reorganizes the data in the partitions based on the new key values and updates the limit keys after reloading the object. Using the DDLIN data set, you can rebalance up to 255 rebalance groups in a single execution of REORG PLUS.

The data set’s attributes must be specified as fixed length (RECFM is F, FB, or FBS), and the record length must be 80 columns (LRECL=80). REORG PLUS uses only columns 1 through 72.

For additional considerations, see Rebalancing-partitions.

Guidelines for using the DDLIN data set

REORG PLUS processes the DDLIN data set according to the following rules:

• REORG PLUS does not validate LIMITKEYS. LIMITKEYS supplied out of sequence might result in data integrity issues. 
• REORG PLUS ignores the DB2 subsystem parameter PREVENT_ALTERTB_LIMITKEY. Therefore, this parameter has no effect on the processing of the statements in your DDLIN data set.
• REORG PLUS ignores any ALTER INDEX or ALTER TABLE statements that refer to an index that is not part of the reorganization.
• REORG PLUS ignores any ALTER TABLE statements for an index-controlled table space.
• If you specify the same partition number on more than one ALTER INDEX or ALTER TABLE statement, REORG PLUS uses the limit key value from the last statement that it found.
• Character, hexadecimal, and graphic string constants are limited to a length of 256 bytes.
• REORG PLUS does not support columns that use a FIELDPROC in SQL ALTER INDEX or ALTER TABLE statements in the DDLIN data set.

Restrictions

REORG PLUS ignores the rebalance request when reorganizing partition-by-growth table spaces.

REORG PLUS terminates when any of the following conditions exist:

• You specify any of the following items in the DDLIN data set:

  • The same partition number more than once on the same ALTER INDEX or ALTER TABLE statement, as shown in the following example:

    ALTER INDEX USER1.TBL1INX
                  PART  1 VALUES (X'11'),
                  PART  2 VALUES (X'22'),
                  PART  2 VALUES (X'11'),
                  PART  3 VALUES (X'33'),
                  PART  4 VALUES (X'44')
                 ;
  • Any of the following constants as a limit key value:
    • Floating-point
    • Decimal floating-point
    • XML
    • LOB
    • Row ID
    • Graphic types
    • Binary strings
  • A limit key constant that spans a line
  • An invalid limit key value
  • A limit key value in VARGRAPHIC format ('G' or 'N' in front of a double-byte string)
  • A limit key value that is a timestamp with a precision greater than the precision defined on the column
• You are using the DDLIN data set to rebalance one of the following types of table spaces:
  • XML table spaces
  • LOB table spaces
  • Table spaces that contain a LOB column
  • Table spaces for clone objects or base objects that participate (or have participated) in a clone relationship
  • Table spaces that contain archive-enabled tables
• The table space that you are rebalancing contains an XML column, and an ALTER statement in the DDLIN data set would alter the last partition of one of the following types of table spaces:
  • A table space that uses table-controlled partitioning
  • A table space that is defined with the LARGE or DSSIZE attribute
• The database containing the object to be reorganized is not in read-write (RW) status.
• The DDLIN data set contains any of the following items:
  • Any SQL statement other than an ALTER INDEX or ALTER TABLE statement
  • Hexadecimal string constants UX' xxxx' and GX' xxxx'
  • An SQL syntax error for any object, regardless of whether that object is part of the reorganization
• You are running a DSNUTILB reorganization.

Sample ALTER INDEX statements and resulting messagesIn the following example, the DDLIN data set contains three ALTER INDEX statements. The first and third statements alter nine partitions of index USER1.TBL1INX, which is an index on table 1. The second statement alters three partitions of index USER1.TBL2INX, which is an index on table 2. The user specified to use the data set on the reorganization of the table space that contains table 1.

The statements in the DDLIN data set are as follows:

REORG PLUS processed the ALTER statements successfully and sent the following messages to SYSPRINT:

Sample ALTER TABLE statements and resulting messagesIn the following example, the DDLIN data set contains 10 ALTER TABLE statements. The user specified to use the data set on the reorganization of the table space that contains table F509085D.LART003.

The statements in the DDLIN data set are as follows:

REORG PLUS processed the ALTER statements successfully and sent the following messages to SYSPRINT: