In the early days of DB2 for z/OS, pretty much every change to a table would result in an unload-drop-recreate-load of the table. This article will talk about how the ALTER table in DB2 12 for z/OS has evolved from DB2 11 for z/OS and what you need to know from a practical point of view.
In DB2 11 for z/OS many features could be altered, provided that your table resided in a Universal table space. For the rest of this article we will assume that the table resides in a Universal table space.
We have immediate table changes and deferred table changes in DB2 11 for z/OS.
Immediate alters/changes
Most of the immediate alters have been around longer. The most well-known is the data type change.
ALTER TABLE tb1 COLUMN col2 SET DATA TYPE VARCHAR(40) ;
There are some limitations and consequences. The limitations are that you can only alter to a compatible data type and the new data type has to be an extension of the original data type (or at least the same space requirements as the original data type).
There are some consequences to these ALTER statements :
- Performance impact: since these changes are purely in the catalog, the data does not get materialized immediately. This means a translation has to be done every time the data is brought outside of DB2. This causes a fairly significant performance overhead and this overhead accumulates quickly if multiple versions are active. That is why DB2 will let you know that a materialization is recommended by setting an advisory state AREO* (advisory reorg pending).
- Possible outage: despite the name “online change”, a data type change can cause an outage to your application. If there is an index defined on the altered column those indexes can become RBDP (Rebuild pending). This can have two serious consequences:
- The index is a unique index: index validation is no longer possible and there is a full application outage.
- The index will be avoided: An index that is rebuild pending is not available as an access path.
- That means that dynamic statements won’t choose this index, which may cause a significant performance impact.
- In case of a static program it all depends on your AUTOBIND zparm.
- If DB2 allows automatic binds, than similar to dynamic statements, you might experience a performance degradation.
- However if DB2 doesn’t allow automatic binds, than the package execution will fail causing an outage.
Deferred Alters/Changes
In DB2 11 for z/OS certain changes are deferred, that means that they will only materialize and occur after an online reorg. At a table level, the best known example would be:
ALTER TABLE tb1 DROP COLUMN col2 RESTRICT ;
Issuing this statement will only mark the column to be dropped, the data remains available until the next online reorg. Provided the conditions are met. In order to drop a column:
- No extended indexes on that column may exist
- No row permissions used
- No column masks on that column
- The column is not used in "Inline SQL table functions"
There are some consequences to these ALTER statements :
- AREOR status : The table space becomes in an AREOR state. Something that you may not know, a REORG SHRLEVEL NONE will not reset the AREOR advisory state, only an online REORG (SHRLEVEL REFERENCE/CHANGE) will materialize the change and remove the exception status.
- AREOR status (2): If a table space is in AREOR status, DB2 will not allow you to perform an immediate alter (e.g. SET DATATYPE). That immediate alter will fail with SQLCODE = -20385, indication another pending alter prevents it.
- NO PIT-recovery: Once the change is materialized and the column has been dropped, there is no way to recover to a point in time before the online reorg.
- Deferred alters can be undone: As long as a deferred alter has not been materialized, it can be undone. However it’s important to understand that this UNDO happens at a table space level and will undo ALL pending DDL changes, if multiple changes are waiting to be materialized. You can use :
ALTER TABLESPACE DB1.TS1 DROP PENDING CHANGES
What has changed in DB2 12 for z/OS ?
DB2 12 for z/OS gives you the choice, and therefore more control, on how and when you would want your alters to materialize. This way you can prevent the potentials problems we had in DB2 11 for z/OS (performance & outage, see above).
The magic to this improvement DSNZPARM : DDL_MATERIALIZATION
- ALWAYS_IMMEDIATE (default)
- ALWAYS_PENDING
You should be aware that this setting will be system wide, and not just for your change.
There are some prerequisites:
- The APPLCOMPAT bind option (for static ALTER) or the CURRENT APPLICATION COMPATIBILITY special register (for dynamic ALTER) is set to V12R1M500 or higher.
- The table must reside in a Universal Table Space.
- Not eligible for pending alters: ALTER COLUMN SET DATA TYPE
- Altering the inline length of a LOB column
- Altering the subtype of a column
- Altering the XML type modifier of an XML column
Obviously this has consequences
Source IBM DB2 12 for z/OS Technical Overview (SG24-8383-00)
An example where you can clearly see the difference in behaviour between the values ALWAYS_IMMEDIATE and ALWAYS_PENDING.
Let’s assume col1 is currently a DEC(15,0) and is part of a unique key.
When you issue :
ALTER TABLE tbl1 ALTER COLUMN col1 SET DATATYPE DEC(24,2)
Then DDL_MATERIALIZATION=ALWAYS_IMMEDIATE will cause a similar behaviour as in DB2 11 for z/OS. Meaning the table space will be in AREO* and the index will be RBDP, meaning no inserts are possible any more.
However DDL_MATERIALIZATION=ALWAYS_PENDING nothing happens at the moment of the alter at the table level but the table space will be in AREOR and a record will be added in SYSPENDINGDDL the materialization will happen at the next online reorg.
I’m convinced that the new ZPARM DDL_MATERIALIZATION should be set to ALWAYS_PENDING for most DB2 shops. As if brings more flexibility, less chances of an outage and better control for the DBAs. It truly unleashes: The awesome power of Alter table in DB2 for z/OS.
Kurt Struyf
Content committee chair
IBM GOLD consultant