Friday, January 17, 2014

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.

16 comments:

  1. Thanks! good information..
    Adding to that, whenever any pending changes are materialized, dependant packages are invalidated..

    Rajesh

    ReplyDelete
    Replies
    1. True, Rajesh -- thanks for pointing that out.

      Robert

      Delete
  2. Once you drop tablespace, do we have to drop sequence object that was created to populate a column of the dropped table

    ReplyDelete
    Replies
    1. There should be no need to drop a sequence object just because you have dropped a table that held values generated via the sequence object. That's one of the nice things about a sequence object versus an identity column of a table: the sequence object does not have a dependency on a table (so, one sequence object could be used for several tables).

      Robert

      Delete
  3. 6 1/2 years later, this post is still very helpful. Thank you!

    ReplyDelete
    Replies
    1. You're welcome, Dave. Good to know that the entry has been useful for you.

      Robert

      Delete
  4. Hi Rob.With db2v12,can we change not null column to null column & null column to not null column.If so which requires drop & recreate & which requires just an alter.

    ReplyDelete
    Replies
    1. Per my understanding, it is not possible to change a column's null-able/not null-able characteristic via ALTER TABLE with ALTER COLUMN. That said, it seems to me that one could accomplish the objective without having to drop and re-create the table. If there is an existing column COL_A that is null-able and you want to change it to NOT NULL, you could add a column COL_B to the table with the same data type and with a NOT NULL attribute; then, with UPDATE statements you could place in COL_B all of the values currently in COL_A (though you would change any null values in COL_A to a non-null default in COLL_B. Then, ALTER the table to drop column COL_A, and rename COL_B to COL_A.

      Robert

      Delete
  5. Thanks Rob! That helps

    ReplyDelete
  6. Hi Robert,does check pending applied only on child tables? will parent tables subject to check pending.if so ,under which situations,we can see that?

    ReplyDelete
    Replies
    1. In a referential integrity context, my understanding is that check-pending status only applies to a child table - the status in that case indicates that the child table needs to be examined to see if it contains any "orphan" rows (i.e., rows with a foreign key value for which there is not a matching value in the referenced column of the parent table).

      A table that happens to be a parent table could be in check-pending status, if the table has a check constraint and the table needs to be examined to verify that all values subject to the check constraint adhere to the constraint.

      Robert

      Delete
  7. What will happen if we issue a SELECT statement immediately after DROPING that particular column

    ReplyDelete
    Replies
    1. Sorry about the delay in responding.

      ALTER TABLE with DROP COLUMN is a pending change; therefore, if you issue an ALTER TABLE T1 DROP COLUMN C7, and someone issues SELECT C7 FROM T1 before T1's table space has been online REORG-ed to materialize the pending drop of C7, the SELECT will successfully retrieve C7 values (assuming that the privilege set of the process that issued the SELECT is sufficient for the SELECT to be successful). If the SELECT C7 FROM T1 is issued after the completion of the online REORG that materializes the pending drop of C7, that SELECT will fail with an error because C7 is no longer a column of T1.

      I hope that this answers your question.

      Robert

      Delete
    2. Thank you so much

      Delete