Bookmark and Share
RSS

Recent Posts

DB2 12 Pending Column Alterations

March 28, 2017

Prior to DB2 12 function level V12R1M500, altering column attributes such as data type, precision, scale or length would occur instantly. This poses a few problems:
  • Dependent packages are invalidated.
  • Indexes are placed in a restrictive status; placing a unique index in restrictive status will cause a table outage.
  • The catalog definition is changed, but the table data isn't. This will cause a performance impact as  the data transforms from the old to the new format, until table reorganization.
To remedy these issues, DB2 12 includes an option to make listed column attribute changes pending, which will require a table space REORG to materialize the changes. To use pending column alterations, the table must be in a universal table space (UTS). The pending column alteration is only for data type, precision, scale or length. The table space will be placed in advisory REORG-pending (AREOR) status. All pending alterations can be grouped together to be materialized in a single REORG of the table space.

If you're only running an online REORG to materialize the changes, ensure you use the REORG option SORTDATA(NO) RECLUSTER(NO) to avoid overhead related to sorting and reorganizing the data.  

Pending column ALTER does have some restrictions that could impact certain environments. For instance, it requires a REORG on the table space rather than at the partition level. This could cause issues with very large table spaces.  

A new ZPARM, DDL_MATERIALIZATION, is used to direct DB2 to use pending alterations rather than immediate alteration. This parameter only applies when the table is defined with a UTS, has page sets already created (DEFINE YES) and is an ALTER TABLE…ALTER COLUMN…SET DATA TYPE alteration. The options are ALWAYS_IMMEDIATE (which precedes DB2 12) or ALWAYS_PENDING, which requires either a REORG SHARE LEVEL CHANGE or REFERENCE to materialize the change.  

The IBM Knowledge Center has a list of all the ALTER TABLE…ALTER COLUMN…DATA SET TYPE clause restrictions.

These statements cannot be executed when a table space has a pending change:
  • ALTER TABLE with immediate options.
  • CREATE INDEX on table.
  • ALTER INDEX ADD COLUMN or ADD INCLUDE COLUMN of any index defined on the table.
  • ALTER INDEX REGENERATE of any index defined on the table.

These statements cannot be executed if the table has pending definition changes:
  • CREATE TRIGGER on the table.
  • CREATE TRIGGER of an INSTEAD OF trigger on a view that is dependent the table.
  • CREATE PERMISSION on the table or that references the table.
  • CREATE MASK on the table or that references the table.
  • CREATE FUNCTION of an inline SQL table function that references the table.
  • CREATE TABLE or ALTER TABLE that defines a materialized query table that references the table.
These statements cannot be executed if the table contains any columns with pending definition changes:
  • CREATE VIEW that references a column with pending definition changes.
  • ALTER TABLE with the ADD VERSIONING clause that references a history table that contains columns with pending definition changes.
  • ALTER TABLE with the ENABLE ARCHIVE clause that references an archive table that contains columns with pending definition changes.
  • ALTER INDEX with the NOT PADDED clause where the index references a column with pending definition changes.
  • CREATE TABLE or ALTER TABLE that specifies a FOREIGN KEY referencing a parent column with pending definition changes.
When a statement cannot be executed, DB2 will return with SQLCODE -20385 which is:

THE STATEMENT CANNOT BE PROCESSED BECAUSE THERE ARE PENDING DEFINITION CHANGES FOR OBJECT object-name OF TYPE object- type (REASON reason-code).

The Knowledge Center also has a complete list of exceptions and reason codes.

Posted March 28, 2017 | Permalink

comments powered by Disqus