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.

22 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
  8. Hello Rob,
    Im trying to alter a table to add a column and I get 911
    DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK
    DUE TO DEADLOCK OR TIMEOUT. REASON 00C9008E, TYPE OF RESOURCE
    00000100, AND RESOURCE NAME ADAEMPSG
    when i checked log to know what was in conflict with query.I notcied an select query on different set of tables in same database(but those tables does not include the table I was trying to alter)
    but still even these select query showed locks was held on database.
    Why would an alter table/select on tables hold database lock/lock the database?

    ReplyDelete
    Replies
    1. Refer to Table 1 on this page of the Db2 for z/OS online documentation: https://www.ibm.com/docs/en/db2-for-zos/12?topic=locks-objects-that-are-subject. You will see that there can be DBD (database descriptor) lock contention between DDL statements such as ALTER and DML statements such as SELECT.

      Robert

      Delete
    2. Good to know.thanks for sharing

      Delete
  9. Couldnt find the intended blog for this question though.I see the row id from an error message in an check utility,so how do I know which row in the table is having error.so I want to know the exact row of the row id(supplying the row is).Any query/utlity/jcl to find that?

    ReplyDelete
  10. Hello,I executed almost 30 plus drop column statement.After the every drop statement -warning message issued
    DSNT404I SQLCODE = 610, WARNING: A CREATE/ALTER ON OBJECT HAS PLACED OBJECT IN ADVISORY REORG PENDING
    After this,the other subsequent drop statment all executed fine and with all this warning.Once object is placed in AREOR(after first drop) how come next drop statment was allowed.

    ReplyDelete
  11. ALTER TABLE with DROP COLUMN is a pending DDL change. As such, it places the affected table space in AREOR status (a type of advisory REORG-pending status). AREOR status is reset when the table space is online REORG-ed to materialize the pending DDL change. Regarding AREOR, refer to this page in the online Db2 for z/OS documentation: https://www.ibm.com/docs/en/db2-for-zos/13?topic=states-reorg-pending-status. On that page, in the description of AREOR status, you will see this sentence: "The affected objects are not restricted and can be accessed by both readers and writers."

    Robert

    ReplyDelete