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
