With IBM DB2 9.7, Change is Easy


IBM DB2 database administrators can now rename a column without the need to unload, drop, rebuild, reload and re-grant on the table. Rebecca Bond introduces AUTO_REVAL, a recent addition to IBM DB2 configuration options.

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

Rebecca Bond
Rebecca Bond
Rebecca Bond, an IBM Information Champion, industry recognized independent consultant and author of the only published book specific to DB2 LUW security, "Understanding DB2 9 Security", enjoys sharing technical lessons learned from her experiences in government, healthcare and financial consulting roles. Rebecca holds numerous advanced IBM certifications covering all aspects of DB2 and is an expert at balancing the twin needs of robust security and accelerated performance. Her unique background provides a wealth of pertinent database and security puzzlers, which she delights in helping us understand and solve via articles, blog posts and presentations.

Latest Articles