With IBM DB2 9.7, Change is Easy

August 12, 2010

I spend a lot of time in New Orleans, the home of the Big Easy, so why shouldn’t my IBM DB2 tasks be easy too?

Before DB2 9.7, I considered using a novel approach to make a point whenever someone asked me to “just change a column name” on a table. I had grown weary of repeatedly explaining that I would need to unload, drop, rebuild, reload and re-grant on the table just to accomplish that supposedly easy task.

My plan was simple. The next time the subject of a column name change came up, I would simply hold up my black velvet painting of a sad, frowning clown and hope I could avoid the detailed explanations. Now, I’m glad that I no longer need sad clowns to help me make a point. DB2 9.7 is fitting in nicely with the culture of the Big Easy.

As much as I like the idea of new functionality, I also have to face the reality that making changes in any database environment can cause problems, so a cautious approach is always a good idea. For example, what if you change a column name in a production environment and all of a sudden, the web application no longer functions because it was expecting the original column name? So, as always, I will proceed with caution, test out the functionality, start in a safe development environment, verify the outcome and then (maybe) I will eventually make the ‘easy’ change in production.

According to the documentation, some good news is that renaming a column (as long as that is all that is being changed) will not cause data loss and won’t affect any granted privileges or LBAC policies associated with the table that is being altered. Renaming a column is only supported for base tables, so you can’t use it for renaming columns in other objects like views or materialized query tables.

First Experiment:

I think I’ll try this out in my tiny sandbox database. I’ll build a PRODUCT table and a simple view.

CREATE TABLE "LOCKSMITH"."PRODUCT"  (
                  "PID" VARCHAR(10) NOT NULL ,
                  "NAME" VARCHAR(128) ,
                  "PRICE" DECIMAL(30,2) ,
                  "PROMOPRICE" DECIMAL(30,2) ,
                  "PROMOSTART" DATE ,
                  "PROMOEND" DATE )
                 IN "USERSPACE1" ;
 
CREATE VIEW VPROD (PID,NAME,PRICE) AS SELECT PID,NAME,PRICE from PRODUCT;

Now I’ll rename a column.

 $>db2 "ALTER TABLE LOCKSMITH.PRODUCT RENAME COLUMN PRICE to COST"
 
 $>db2 "describe table locksmith.product"
 
                  Data type                Column
 Column name      schema    Data type name Length     Scale Nulls
 ---------------- --------- -------------- ---------- ----- ------
 PID              SYSIBM    VARCHAR                10     0 No
 NAME             SYSIBM    VARCHAR               128     0 Yes
 COST             SYSIBM    DECIMAL                30     2 Yes
 PROMOPRICE       SYSIBM    DECIMAL                30     2 Yes
 PROMOSTART       SYSIBM    DATE                    4     0 Yes
 PROMOEND         SYSIBM    DATE                    4     0 Yes
 DESCRIPTION      SYSIBM    XML                     0     0 Yes

I can see the column change was successful. The Price column has been renamed to Cost.

If you’re like me, you are wondering about dependent objects when a column is renamed. Fortunately with DB2 9.7, checking for invalid objects is also easy.

db2 "select char(objectschema,12) objectschema, char(objectname,15) objectname, objecttype from SYSCAT.INVALIDOBJECTS"
 
OBJECTSCHEMA OBJECTNAME      OBJECTTYPE
------------ --------------- ----------
LOCKSMITH    VPROD           V

There is only one view on this table, but as we suspected, it was invalidated by this change. With DB2 9.7, revalidating an object can be accomplished automatically in many cases. However, this is not one of those cases. Because the view explicitly references the PRICE column, I’ll need to re-create the view in order to make it usable again.

First, I’ll modify the view SQL to match our new column names. To rebuild the new view, I can make use of the some easy new functionality to CREATE or REPLACE the view.

$> db2 "CREATE or REPLACE VIEW VPROD (PID,NAME,COST) AS SELECT PID,NAME,COST from PRODUCT"

Yielding to my normal DBA paranoia, I re-checked SYSCAT.INVALIDOBJECTS just to be sure and have confirmed that the view is no longer listed as invalid.

Second Experiment

Now I want to find out what happens when I change things up a bit. I’ll start from the beginning and drop the Product table and the VProd view and re-create them from scratch with one minor adjustment. The Product table will look just like it did before, but the view will change.

$> db2 "create view vprod as (select * from locksmith.product where pid <> 'MAINTENANCE')"   

Notice that for this experiment the view definition does not explicitly name the Price column.

Now let’s go back to the rename step to see what happens with the view this time.

$> db2 "alter table locksmith.product rename column price to cost"

$>db2 "select char(objectschema,12) objectschema, char(objectname,15) objectname, objecttype from SYSCAT.INVALIDOBJECTS"

OBJECTSCHEMA OBJECTNAME      OBJECTTYPE
------------ --------------- ----------
LOCKSMITH    VPROD           V

As expected, the VProd view is now invalid. Up until this point the outcome of Experiment 1 and Experiment 2 are the same.

But, since we’re looking for things that make our jobs easier, what happens if I try a SELECT on the view in its invalid state?

$>db2 "select * from locksmith.vprod"

PID     NAME   COST   PROMOPRICE  PROMOSTART PROMOEND

  0 record(s) selected.

(There are no records that match the criteria, but there was no error.)

I just successfully performed a SELECT on the view. That should mean that the view is valid. Does the view still appear as invalid to DB2?

$>db2 "select char(objectschema,12) objectschema, char(objectname,15) objectname, objecttype from SYSCAT.INVALIDOBJECTS"

OJECTSCHEMA OBJECTNAME      OBJECTTYPE
------------ --------------- ----------
  0 record(s) selected.

This is different. Now there are no invalid objects, but I didn’t have to re-create the view this time. How did it get revalidated?

Show Me the Big Easy

DBAs are so predictable. You are wondering why I didn’t have to re-create the view this time. Am I right?

Let me introduce you to AUTO_REVAL, a recent addition to our easy DATABASE CONFIGURATION options. AUTO_REVAL can be set to one of IMMEDIATE, DISABLED, DEFERRED or DEFERRED_FORCE. The default, which is what I am using, is DEFERRED. The DEFERRED parameter means that DB2 attempts to re-validate an invalidated dependent object the next time the object is accessed.

$>db2 "get db cfg" | grep REVAL

 Auto-Revalidation                          (AUTO_REVAL) = DEFERRED

Keeping things equal when testing is important so I’ve kept AUTO_REVAL set to DEFERRED for both experiments.

The documentation on AUTO_REVAL is less than crystal clear regarding what happens when a renamed column in a base table is explicitly cited by the dependent object. What I didn’t tell you at the time was that in Experiment 1, a SELECT on the VProd view returned this error:

SQL0727N  An error occurred during implicit system action type "3".
Information returned for the error includes SQLCODE "-206", SQLSTATE "42703" and message tokens "PRICE".  SQLSTATE=56098  

That error verified what I suspected. Because the view explicitly referenced a column that was no longer in the base table, I would have to rebuild the view.

In our second experiment though, the view did not explicitly reference the renamed column. With AUTO_REVAL set to DEFERRED, revalidation was performed at the first attempt to access the dependent object and we didn’t have to re-create the VProd view. All we had to do was issue a SELECT to the VProd view and the revalidation was easy.

What Happens to the Sad Clown Painting Now?

The Big Easy winner for DB2 DBAs is that we can now RENAME a column. Now when developers ask, I just nod my head and agree that renaming a column IS easy. My Black Velvet Sad Clown painting can remain safely undisturbed in its rightful place on my cubicle wall.

To find out more about AUTO_REVAL see the IBM DB2 Information Center.

Big Thanks to Paul Bird, Senior Technical Staff Member at IBM Toronto Lab for his help researching the mysteries of AUTO_REVAL. Paul has been a technical reviewer for me since I wrote the first outline for a book proposal on DB2 Security. He’s an all around “answer” person for many authors and we all appreciate his knowledge, positive attitude and willingness to help.

Additional Resources

Free DB2/Info Management Downloads, Tutorials and Articles on the DB2/Info ManagementShowcase

» See All Articles by Columnist Rebecca Bond