This Db2 for z/OS News from the Lab blog entry was originally published on 2019-06-20.

This post is part of the Db2 12 GA feature series, which highlights new features and capabilities introduced by the Db2 12 for z/OS, at general availability (GA) in October 2016.

By Regina Liu and Paul McWilliams.

Db2 12 for z/OS introduced a new capability to alter certain attributes of table columns as pending data definition changes. With pending data definition changes, you can reduce data unavailability and better manage the scheduling of alterations and REORG utility operations.

In previous releases, and by default in Db2 12, all column attribute alterations take immediate effect, resulting in immediate changes to the column definition in the Db2 catalog and the OBD, but not in the data. The result is a “mismatch” between the schema definition indicated in the catalog and OBD and the schema format of the data as it is actually stored. To handle the mismatch, Db2 must use one of the following mechanisms:

• Schema Versioning – Db2 generates a new schema version for each different schema definition alteration to a table. Db2 stores the generated schema version in the catalog and OBD, and tags each row of data with the schema version number to indicate the format of the stored data. Any time that Db2 retrieves the data, it must convert the data to the current schema format. In this way, the ALTER statement can execute without causing data unavailability. However, you incur a performance cost for the conversion, until the REORG utility is run to convert data to the current schema format.
• Restrictive Status – Certain types of alterations cannot be handled by schema versioning, namely alterations that result in different ordering of data in an index or across partition boundaries. Db2 handles such alterations by placing the table or index in a restrictive REORG or REBUILD pending state. The result is that the data becomes unavailable after the ALTER statement is executed until you run the REORG or REBUILD utility.

Db2 10 introduced the capability to defer certain ALTER operations (DROP COLUMN and partition boundary changes) as pending data definition changes. With pending data definition changes, Db2 completes the authorization and semantic validation for the ALTER statement at execution time, but Db2 does not alter the the schema definition in the catalog, OBD, or data at that time. Instead, the ALTER statement is saved in the catalog, pending a subsequent REORG that materializes the schema definition changes in all places at the same time—the catalog, OBD, and data. In this way, the impact to data availability is limited to the REORG SWITCH phase.

Now in Db2 12, you can specify that Db2 uses the same approach to defer the alterations for ALTER TABLE statements that alter the data type, precision, scale, or length attribute of a column in a universal table space.

To activate this capability, you set the DDL_MATERIALIZATION subsystem parameter to ALWAYS_PENDING. Pending column alterations can be materialized by online REORG of the table space.

An immediate column alteration can be materialized by a part-level REORG. A pending column alteration, however, requires a REORG of the entire table space to materialize. If you are able to REORG the entire table space, you might want to consider executing a column alteration as a pending definition change for the following reasons:

• To reduce the unavailability of tables or indexes. Indexes are often defined on the columns to be altered, and such indexes might be placed in restrictive pending states for alterations executed as immediate changes. The indexes, or the table in the case of a unique index, become unavailable for both insert and update until you rebuild the indexes. By executing the column alterations as pending changes instead, you can reduce the unavailability to a very short window during the SWITCH phase of REORG.
• To reduce the number of REORG operations required to materialize multiple changes. Other pending alterations might be needed for the containing table space or objects in the table space. If other pending alterations for a table space are awaiting materialization, executing a column alteration as an immediate change is restricted. You can either alter the column before executing any pending alterations, or first materialize all pending alterations before executing an immediate column alteration, which would then require a second REORG to convert the data to the new format. By executing the column alterations as a pending change, you can group the column alterations with other pending alterations and materialize all of them in the same REORG.
• To avoid a performance impact. Because the schema change for an immediate change takes place immediately in the catalog and OBD but not in the data, any subsequent data access must convert the data from the old schema format to the current schema format, until REORG is run to convert all the data. By executing the column alterations as a pending change, the schema alteration takes place in the catalog, OBD, and data at the same REORG time, eliminating the performance impact.