Materializing pending definition changes

Examples

Example

The following example provides a scenario that shows how you can use the ALTER TABLESPACE statement to generate pending definition changes, and then use the REORG TABLESPACE utility with SHRLEVEL REFERENCE to materialize pending definition changes at the table space level.

Consider the following scenario:

1. In Version 8, you created the simple table space TS1 in database DB1, such as:

   CREATE DATABASE DB1;
   CREATE TABLESPACE TS1
   BUFFERPOOL BP0
   IN DB1;
   CREATE TABLE USER1.TB1
   (
   COL1 INTEGER,
   COL2 VARCHAR(10)
   )
   IN DB1.TS1;
   
   CREATE INDEX USER1.IX1
   ON USER1.TB1
   ( COL2 )
   BUFFERPOOL BP0
   COPY YES
   ;

2. In the current release of Db2, you issue the following ALTER TABLESPACE statement to convert the simple table space to a partition-by-growth table space, and to change the buffer pool page size. Those changes are pending definition changes. Suppose that the changes take place at time 2012-10-04-07.14.20.204010:

   ALTER TABLESPACE DB1.TS1 BUFFERPOOL BP8K0 MAXPARTITIONS 20 ;

   For each pending option in an ALTER statement, there is a corresponding entry in the SYSPENDINGDDL table. If you specify multiple pending options in one ALTER statement, each change has its own SYSPENDINGDDL entry, but the changes have the same create timestamp. In addition, the same ALTER statement text is stored repeatedly with each pending option entry that is specified with the ALTER statement. Therefore, issuing this ALTER TABLESPACE statement results in the table space being placed in AREOR state, and two pending option entries are inserted into the SYSPENDINGDDL table with OBJTYPE = 'S' for table space. This ALTER statement has not changed the current definition or data, so the buffer pool in SYSTABLESPACE still indicates BP0, and the table space is still a simple table space.

3. Later at the time of 2012-10-09-07.15.22.216020, you issue the following ALTER TABLESPACE statement that has one pending option:

   ALTER TABLESPACE DB1.TS1 SEGSIZE 64 ;

   This statement results in one entry being inserted into the SYSPENDINGDDL table with OBJTYPE = 'S', for table space. This ALTER statement has not changed the current definition or data, so the SEGSIZE in SYSTABLESPACE is still 0.
4. Next, you issue the following ALTER statement with one pending option at the time of 2012-12-14-07.20.10.405008:

   ALTER INDEX USER1.IX1 BUFFERPOOL BP16K0;

   This statement results in the index being placed in AREOR state, and an entry is inserted into the SYSPENDINGDDL table with OBJTYPE = 'I', for index. This ALTER statement has not changed the current definition or data, so the buffer pool in SYSINDEXES still indicates BP0 for the index.
5. You issue another ALTER statement that is exactly the same as the previous one, at the time of 2012-12-20-04.10.10.605058. This statement results in another entry being inserted into the SYSPENDINGDDL table with OBJTYPE = 'I', for index.
6. You run the following SELECT statement to query the SYSPENDINGDDL catalog table:

   SELECT DBNAME, TSNAME, OBJSCHEMA, OBJNAME, OBJTYPE, OPTION_SEQNO,
   OPTION_KEYWORD, OPTION_VALUE, CREATEDTS, STATEMENT_TEXT
   FROM SYSIBM.SYSPENDINGDDL
   WHERE DBNAME = 'DB1'
   AND TSNAME = 'TS1'
   ORDER BY CREATEDTS
   ;

   This query results in the following output:

   Table 1. Output from the SELECT statement for the SYSPENDINGDDL catalog

   DBNAME	TSNAME	OBJSCHEMA	OBJNAME	OBJTYPE
   DB1	TS1	DB1	TS1	S
   DB1	TS1	DB1	TS1	S
   DB1	TS1	DB1	TS1	S
   DB1	TS1	USER1	IX1	I
   DB1	TS1	USER1	IX1	I

   Table 2. Continuation of output from the SELECT statement for the SYSPENDINGDDL catalog

   OPTION_SEQNO	OPTION_KEYWORD	OPTION_VALUE	CREATEDTS
   1	BUFFERPOOL	BP8K0	2012-10-04- 07.14.20.204010
   2	MAXPARTITIONS	20	2012-10-04- 07.14.20.204010
   1	SEGSIZE	64	2012-10-09- 07.15.22.216020
   1	BUFFERPOOL	BP16K0	2012-12-14- 07.20.10.405008
   1	BUFFERPOOL	BP16K0	2012-12-20- 04.10.10.605058

   Table 3. Statement text output for the SELECT statement for the SYSPENDINGDDL catalog

   STATEMENT_TEXT
   ALTER TABLESPACE DB1.TS1 BUFFERPOOL BP8K0 MAXPARTITIONS 20;
   ALTER TABLESPACE DB1.TS1 BUFFERPOOL BP8K0 MAXPARTITIONS 20;
   ALTER TABLESPACE DB1.TS1 SEGSIZE 64;
   ALTER INDEX USER1.IX1 BUFFERPOOL BP16K0;
   ALTER INDEX USER1.IX1 BUFFERPOOL BP16K0;

   

7. Next, you run the REORG INDEX utility with SHRLEVEL CHANGE on the index. For example:

   REORG INDEX USER1.IX1 SHRLEVEL CHANGE

   However, because pending definition changes exist for the table space, the REORG utility proceeds without materializing the pending definition changes for the index, and issues warning DSNU275I with RC = 4 to indicate that no materialization has been done on the index, because there are pending definition changes for the table space. After the REORG utility runs, all the SYSPENDINGDDL entries still exist, and the AREOR state remains the same.
8. Now, you run the REORG TABLESPACE utility with SHRLEVEL REFERENCE on the entire table space. For example:

   REORG TABLESPACE DB1.TS1 SHRLEVEL REFERENCE

   The REORG utility materializes all of the pending definition changes for the table space and the associated index, applying the changes in the catalog and data. After the REORG utility runs, the AREOR state is cleared and all entries in the SYSPENDINGDDL table for the table space and the associated index are removed. The catalog and data now reflect a buffer pool of BP8K0, MAXPARTITIONS of 20, and SEGSIZE of 64.

Example

The following example provides a scenario that shows how you can use the ALTER TABLE statement to generate pending definition changes, and then use the REORG TABLESPACE utility with SHRLEVEL REFERENCE to materialize pending definition changes in the table space that contains the table.

Consider the following scenario:

1. A table, the objects that contain the table, and an index on the table were previously defined as follows:

   CREATE DATABASE DB1;
   CREATE TABLESPACE TS1 IN DB1;
   CREATE TABLE SC.TB1
    (COLUMN1 INTEGER,
     COLUMN2 CHAR(100),
     COLUMN3 VARCHAR(100))
    IN DB1.TS1;
   
   CREATE INDEX SC.IX1 ON SC.TB1(COLUMN1);

   Subsystem parameter DDL_MATERIALIZATION is set to ALWAYS_PENDING.

2. You want to alter column COLUMN1 from INTEGER to BIGINT, so you issue the following ALTER TABLE statement:

   ALTER TABLE SC.TB1 
    ALTER COLUMN COLUMN1
    SET DATA TYPE BIGINT;

   SQLCODE +610 is returned after the ALTER statement is issued, and table space TS1 is placed in advisory REORG-pending status. An entry is created in the SYSPENDINGDDL table for the pending ALTER statement.

3. You run the following SELECT statement to query the SYSPENDINGDDL catalog table:

   SELECT DBNAME, TSNAME, OBJSCHEMA, OBJNAME, OBJTYPE, OPTION_SEQNO,
    OPTION_KEYWORD, OPTION_VALUE, CREATEDTS, STATEMENT_TEXT
    FROM SYSIBM.SYSPENDINGDDL
    WHERE DBNAME = 'DB1'
    AND TSNAME = 'TS1'
    AND OBJNAME = 'TB1'
    ORDER BY CREATEDTS
   ;

   This query results in the following output:

   Table 4. Output from the SELECT statement for the SYSPENDINGDDL catalog

   DBNAME	TSNAME	OBJSCHEMA	OBJNAME	OBJTYPE
   DB1	TS1	SC	TB1	T

   Table 5. Continuation of output from the SELECT statement for the SYSPENDINGDDL catalog

   OPTION_SEQNO	OPTION_KEYWORD	OPTION_VALUE	CREATEDTS
   1	SET DATA TYPE	BIGINT	2016-10-04- 07.14.20.204010

   Table 6. Statement text output for the SELECT statement for the SYSPENDINGDDL catalog

   STATEMENT_TEXT
   ALTER TABLE SC.TB1 ALTER COLUMN COLUMN1 SET DATA TYPE BIGINT

   

4. Assume that there are no other pending definition changes on table space TS1. You run the REORG TABLESPACE utility with SHRLEVEL REFERENCE on the table space that contains table TB1. For example:

   REORG TABLESPACE DB1.TS1 SHRLEVEL REFERENCE

   The REORG utility materializes the pending definition change for the table, applying the changes in the catalog and data. After the REORG utility runs, the AREOR state is cleared, and the entry in the SYSPENDINGDDL table for table space TS1 is removed. The catalog and data now reflect a COLUMN1 data type of BIGINT.