With IBM DB2 9.7, Change is Easy
August 12, 2010
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 shouldnt 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, Im 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 wont 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 cant use it for renaming columns in other objects like views or materialized query tables.
I think Ill try this out in my tiny sandbox database. Ill 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 Ill 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 youre 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, Ill need to re-create the view in order to make it usable again.
First, Ill 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.
Now I want to find out what happens when I change things up a bit. Ill 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 lets 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 were 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 didnt 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 didnt 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 Ive 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 didnt 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 didnt 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. Hes an all around answer person for many authors and we all appreciate his knowledge, positive attitude and willingness to help.