DB2 for z/OS: Answering Some Questions About Adding and Dropping Table Columns

From the very beginning, you've been able to add a column to an existing DB2 for z/OS table by way of the SQL statement ALTER TABLE with the ADD COLUMN option. In fact, relative ease of logical database design modification had a lot to do with DB2 becoming a very widely used DBMS. Of course, there are times when what you want to do is remove a column from an existing table. That proved to be a tougher nut to crack, functionality-wise, but we finally got ALTER TABLE DROP COLUMN with DB2 11 for z/OS. I'm not going to try to tell you everything there is to know about ALTER TABLE DROP COLUMN in this blog entry (you can find a very good write-up on this new feature in section 4.7 of the "red book" titled, IBM DB2 11 for z/OS Technical Overview). Instead, what I want to do here is address a couple of questions that could come to mind as you learn more about DROP COLUMN support.

Question: ALTER TABLE DROP COLUMN is a pending change. Is ALTER TABLE ADD COLUMN also a pending change in a DB2 11 environment?

Short answer: No

Longer answer: One of the really great enhancements delivered with DB2 10 for z/OS is something called pending DDL. Again, I won't try in this space to provide you with a pile of relevant information (check out section 4.1.3 of the IBM redbook titled, DB2 10 for z/OS Technical Overview), but here are two of the key aspects of pending DDL:

• It enabled non-disruptive implementation of a number of DB2 database design changes. Like what? Like changing a table space's page size, or DSSIZE, or SEGSIZE, or type (e.g., traditional segmented to universal partition-by-growth, or "classic" table-controlled partitioned to universal partition-by-range); or, with DB2 11, dropping a column from a table. How is "non-disruptive" pulled off? Simple: After the ALTER is executed, the target table space is placed in a new (with DB2 10) and non-restrictive advisory REORG-pending state labeled AREOR. A subsequent online REORG (SHRLEVEL REFERENCE or SHRLEVEL CHANGE) of the entire table space (not a partition-level REORG) materializes the outstanding pending changes associated with the table space.
• It enabled a "never mind" action with respect to the aforementioned database design changes. Suppose you act to change a table space's SEGSIZE via ALTER TABLESPACE, and then decide that you'd rather stick with the current SEGSIZE. Is that a problem? Not at all, especially if the online REORG that would materialize the pending change in segment size for the table space has not yet been executed. In that case, you'd just issue an ALTER TABLESPACE statement for the object in question, with the new (starting with DB2 10) DROP PENDING CHANGES option. Do that, and the pending changes -- which had been recorded by DB2 in the new (with DB2 10) SYSPENDINGDDL catalog table -- are deleted, the table space's AREOR status is removed, and you're back where you started.

[Note that with the exception of a change that results in the conversion of a non-universal table space to a universal table space (such as specifying MAXPARTITIONS in an ALTER TABLESPACE statement to convert a simple or traditional segmented table space to a universal partition-by-growth table space), pending changes can be made only to universal table spaces.]

So, in reading about ALTER TABLE DROP COLUMN for DB2 11, you could find yourself thinking, "Ooh -- if ALTER TABLE DROP COLUMN is a pending change, was ALTER TABLE ADD COLUMN also made a pending change in a DB2 11 environment?" As I indicated above, the answer to that question is, "No." ALTER TABLE ADD COLUMN remains in a DB2 11 system what it's always been: an immediate change. That doesn't mean that the column in question is immediately added to the table in a physical sense (that's accomplished by way of a subsequent REORG). It means that the table's definition in the DB2 catalog is immediately changed to reflect the addition of the new column.

This answer to the first question leads to the second question.

Question: OK, so ALTER TABLE ADD COLUMN is not a pending change. Even so, could the new ALTER TABLE DROP COLUMN functionality provided by DB2 11 be used to "undo" an ALTER TABLE ADD COLUMN action, before the column has been physically added to the table?

Short answer: Yes, but...

Longer answer: Yes, but you'll still need to execute an online REORG of the table space. You might think, "Why is that? I haven't yet executed a REORG to physically add the column named in the ALTER TABLE ADD COLUMN statement; therefore, the column is only logically there. Can't it just be logically removed?" The online REORG of the table space is required because ALTER TABLE DROP COLUMN is a pending change, and pending changes don't get acted upon until an online REORG is subsequently executed for the associated table space. This online REORG to put the ALTER TABLE DROP COLUMN into effect won't change the table in a physical sense -- it will just ensure that the logically added column (reflecting the ALTER TABLE ADD COLUMN action that you now want to un-do) is not physically added to the table.

Here's a sweetener for you: if you run an online REORG just to put pending changes into effect for a table space, and you don't want to incur the cost of re-establishing clustering sequence for table rows, DB2 11 provides a CPU-saving feature. Specify, in the utility control statement, SORTDATA NO (previously not permitted for a SHRLEVEL CHANGE online REORG) together with the new RECLUSTER NO option, and data WILL NOT be unloaded by way of the table's clustering index and WILL NOT be sorted in clustering sequence.

There is a lot of great stuff in DB2 11. This blog entry has highlighted a bit of that great stuff (and some of the great stuff introduced with DB2 10). More to come, so come back by.