Materializing pending definition changes

After generating pending definition changes by issuing the ALTER TABLESPACE statement, you must materialize pending definition changes at the table space level. Materialization of the pending definition changes means implementing the changes in the database system.

About this task

Start of change Pending definition changes are data definition changes that do not take effect immediately. When definition changes are pending, the affected objects are available until it is convenient to implement the changes. End of change

Most pending data definition changes are supported only for universal table spaces, with the following exceptions:

Converting single-table simple or segmented (non-universal) table spaces to partition-by-growth universal table spaces, with the MAXPARTITIONS attribute.
Converting partitioned (non-universal) table spaces to range-partitioned universal table space, with the SEGSIZE attribute.

Tip:

Try to run REORG at a time when the data is not heavily accessed. Otherwise, application outages might occur, as described in Reorganization with pending definition changes. End of change

Procedure

To materialize pending data definition changes, use the following approaches:

Run the REORG TABLESPACE utility with SHRLEVEL REFERENCE or SHRLEVEL CHANGE. Do not specify FASTSWITCH NO.
Also note the restrictions for REBALANCE in Syntax and options of the REORG TABLESPACE control statement.

Restriction: Using the REORG TABLESPACE utility with SHRLEVEL REFERENCE or SHRLEVEL CHANGE does not drop empty partitions from a partition-by-growth universal table space.
For pending definition changes for indexes, issue REORG INDEX statements. Only pending definition changes to the reorganized index are materialized. Pending definition changes to the table space or table remain pending.

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.

Begin general-use programming interface information.

Consider the following scenario:

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
;

After migrating to new-function mode in Version 10, 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.
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.
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.
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.

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;

End general-use programming interface information.

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.
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.