Blog
DB2utor

Categories

April 29, 2008

Implementing an Online COLUMN Rename in DB2 V9

DB2 V9 includes the capability to rename a COLUMN on a table without having to drop the table and recreate it. Given how time-consuming it is to unload all the table data, drop the table, recreate it and then reload the data, it's easy to see how this single enhancement can make a DBA's life easier.

Besides saving DBAs time, the simple ALTER TABLE RENAME COLUMN is also intended to save companies money. Unloading and reloading tables and data can obviously lead to significant downtime. This alternative helps production systems stay running.

So while changing column names has become easier, the process still requires planning, because the ALTER TABLE RENAME COLUMN has some restrictions, namely:

* The source column name must be a column on the table
* The target column name cannot be a name already on the table
* Both source and target column names must not be qualified

In addition, you cannot rename a column if any of these conditions apply:

* The column is referenced in a view
* The column has an index on an expression defined
* The column has a check constraint defined
* The column has a field procedure defined
* The table has a trigger
* The table is a materialized query table or is referenced by a materialized query table
* The table has a valid procedure or edit procedure defined
* The table is a DB2 catalog table

The first step in planning a column change it to find all the programs and other DB2 objects that have a dependency on it. For the programs, you have developers changing the code and recompiling and binding the packages. It's up to the DBA to find all the dependent DB2 objects and ensure everything is ready for the online column rename.

Here's an example: Rename column EMPNO to Employee_Number and FIRSTNME to First_Name on table CSBP.EMP.

To find dependencies on the column being renamed, start with some simple SQL queries: What programs are using the table that this column is on?

SELECT SUBSTR(P.COLLID,1,18) AS COLLID
, SUBSTR(P.NAME,1,18) AS NAME
, P.VALID
, P.OPERATIVE
FROM SYSIBM.SYSPACKAGE P
WHERE EXISTS (SELECT '1'
FROM SYSIBM.SYSPACKDEP PD
WHERE PD.DLOCATION = P.LOCATION
AND PD.DCOLLID = P.COLLID
AND PD.DNAME = P.NAME
AND PD.BQUALIFIER = 'CSBP'
AND PD.BNAME = 'EMP'
)
WITH UR;

The output from this query gives you your list of programs. In my example, only one program, DBINSRT1, is using the EMP table.

COLLID      NAME           VALID     OPERATIVE
----------- -------------- --------- -----------------
CSBP_C      DBINSRT1       Y         Y

Now let's revisit the list of restrictions listed above and find out if using the ALTER table RENAME COLUMN statement will be a problem.

1) Is the column referenced in a view?
In this example I know I want to rename the EMPNO column in the EMP table. So I'm looking for any views using the EMP table and containing EMPNO in the view text. (You'll want to change the creator and bname to your table creator and table name.)

SELECT DISTINCT SUBSTR(V.CREATOR,1,8) AS CREATOR
, SUBSTR(V.NAME,1,18) AS NAME
FROM SYSIBM.SYSVIEWS V
WHERE EXISTS ( SELECT 1
FROM SYSIBM.SYSVIEWDEP VD
WHERE VD.BCREATOR = 'CSBP'
AND VD.BNAME = 'EMP'
AND VD.DCREATOR = V.CREATOR
AND VD.DNAME = V.NAME
)
AND V.TEXT LIKE '%EMPNO%'
WITH UR ;

2) Is the column referenced in an index expression?
The following query will find all indexes on table CSBP.EMP that contain an expression using column FIRSTNME. If one is found, I can’t use the online rename to rename this column.

SELECT SUBSTR(KT.IXSCHEMA,1,8) AS IXSCHEMA
, SUBSTR(KT.IXNAME,1,18) AS IXNAME
, I.IX_EXTENSION_TYPE
, SUBSTR(KT.DERIVED_FROM,1,100) AS DERIVED_FROM
, SUBSTR(I.TBCREATOR,1,8) AS TBCREATOR
, SUBSTR(I.TBNAME,1,18) AS TBNAME
FROM SYSIBM.SYSINDEXES I
, SYSIBM.SYSKEYTARGETS KT
, SYSIBM.SYSTABLES T
WHERE T.CREATOR = 'CSBP'
AND T.NAME = 'EMP'
AND T.CREATOR = I.TBCREATOR
AND T.NAME = I.TBNAME
AND I.CREATOR = KT.IXSCHEMA
AND I.NAME = KT.IXNAME
AND KT.KEYSEQ = 1
AND KT.DERIVED_FROM LIKE '%FIRSTNME%'
ORDER BY KT.IXSCHEMA
, KT.IXNAME
, KT.KEYSEQ
WITH UR ;

3) Is the column referenced in a check constraint?
If the following query returns a row for FIRSTNME, then I cannot use the online rename.

SELECT SUBSTR(CK.TBOWNER,1,8) AS TBOWNER
, SUBSTR(CK.TBNAME,1,18) AS TBNAME
, SUBSTR(CK.CHECKNAME,1,18) AS CHECK_NAME
, SUBSTR(CK.COLNAME,1,18) AS COLNAME
FROM SYSIBM.SYSCHECKDEP CK
WHERE CK.TBOWNER = 'CSBP'
AND CK.TBNAME = 'EMP'
AND CK.COLNAME = 'FIRSTNME'
WITH UR ;

4) Is the column referenced in a field procedure?
The following query will tell me if the column has a field proc. If so, I cannot use online rename.

SELECT SUBSTR(C.NAME,1,18)
CONCAT ': CONTAINS A FIELD PROC'
FROM SYSIBM.SYSCOLUMNS C
, SYSIBM.SYSTABLES T
WHERE T.CREATOR = C.TBCREATOR
AND T.NAME = C.TBNAME
AND T.TYPE = 'T'
AND C.FLDPROC = 'Y'
AND T.CREATOR = 'CSBP'
AND T.NAME = 'EMP'
AND C.NAME = 'FIRSTNME'
WITH UR ; 

5) Does the table have a trigger?
The following query will tell me if the table with the column that needs to be renamed contains a trigger. If it does, I cannot rename the column with the alter table statement.

SELECT SUBSTR(TR.NAME,1,18) AS NAME
, SUBSTR(TR.SCHEMA,1,8) AS SCHEMA
FROM SYSIBM.SYSTRIGGERS TR
WHERE TR.TBOWNER = 'CSBP'
AND TR.TBNAME = 'EMP'
AND TR.SEQNO = 1
WITH UR ;

6) Is the column on a materialized query table, or is it referenced by a materialized query table?
If this query returns data, I cannot rename the column using the alter table statement.

SELECT SUBSTR(T.CREATOR,1,8) AS CREATOR
, SUBSTR(T.NAME,1,18) AS NAME
, T.TYPE
, T.NUM_DEP_MQTS
FROM SYSIBM.SYSTABLES T
WHERE (T.TYPE = 'M'
OR
T.NUM_DEP_MQTS > 0 )
AND T.CREATOR = 'CSBP'
AND T.NAME = 'EMP'
ORDER BY T.CREATOR, T.NAME
WITH UR ;

7) Does the table contain a valid procedure or edit procedure?
The following query will return data when the table contains a validation or edit procedure.

SELECT SUBSTR(T.CREATOR,1,8) AS CREATOR
, SUBSTR(T.NAME,1,18) AS NAME
, T.EDPROC
, T.VALPROC
FROM SYSIBM.SYSTABLES T
WHERE T.TYPE = 'T'
AND T.CREATOR = 'CSBP'
AND T.NAME = 'EMP'
AND
( NOT EDPROC = ' '
OR
NOT VALPROC = ' '
)
WITH UR ;

These queries are designed to give you information so you know whether you should try the rename. The worst case is trying the rename and getting an error. Here's an example of trying to use the rename and learning you can’t due to one of the restrictions. In this case, the problem is that EMPNO is a column being used in a view on this table:

ALTER TABLE CSBP.EMP
RENAME COLUMN EMPNO TO EMPLOYEE_NUMBER
DSNT408I SQLCODE = -750, ERROR: THE SOURCE TABLE CSBP.EMP CANNOT BE RENAMED BECAUSE IT IS REFERENCED IN EXISTING VIEW,
MATERIALIZED QUERY TABLE, OR TRIGGER DEFINITIONS, IS A CLONE TABLE, OR HAS A CLONE TABLE DEFINED FOR IT

So my option is to drop the views, do the rename and then rebuild the views. However, I must go through and find packages that may be using these views I need to drop. Then I can go back and rebind the packages once I've rebuilt the views I dropped.

I hope you understand that, even though the rename column greatly improves availability, you still need to do your homework. If all goes well, your table will have no restrictions. But if there's a restriction, you must remove it before you can use the online rename; otherwise, you're stuck unloading, dropping, recreating and reloading data, along with making sure all the programs that used this column are changed and rebound once the changes are implemented.