Rename column in DB2 v8.1

The following outlines a simple example of the renaming of a DB2 table column using the SYSPROC.ALTOBJ procedure.

See the below link for a more detailed description of using ALTOBJ.

Suppose a table is created with the following sql snippet:

CREATE TABLE DB2ADMIN.TX (TXAA_ID BIGINT NOT NULL,
  COUNSELLING_REQUIRED_FLAG CHAR(1) NOT NULL WITH DEFAULT 'N',
  BILLING_QUANTITY DECIMAL(8,2) );

To rename the COUNSELLING_REQUIRED_FLAG column to COUNSELLING_REQUIRED we use the SYSPROC.ALTOBJ call.
Note that the table creation SQL is passed as a string to ALTOBJ. For this reason, the quotation marks used to encapsulate strings in the original SQL needs to be escaped out with an additional single quote.

CALL SYSPROC.ALTOBJ('APPLY_CONTINUE_ON_ERROR',
  'CREATE TABLE DB2ADMIN.TX
    (TXAA_ID BIGINT NOT NULL,
     COUNSELLING_REQUIRED CHAR(1) NOT NULL WITH DEFAULT ''N'',
     BILLING_QUANTITY DECIMAL(8,2) )',-1, ?);

Since this table is relatively simple, the above commands should execute without error. However, because we are telling DB2 to ignore errors with ‘APPLY_ON_ERROR’ it is important to compare the original table with the newly renamed one for differences.
By differences, we are referring to indexes, triggers, dependencies etc that may not have been translated properly. Comparing the DDL description generated with db2look before and after the desired change is useful for this.

db2look -d <dbname> -e -x -t <tblname> -o <filename>

e.g. db2look -d hw -e -x -t tx

As a more complicated example, when an attempt was made to rename the COMPOUND_AAC_OVERRIDDEN_FLAG column to AAC_OVERRIDDEN_FLAG in db2admin.TX, ALTOBJ generated unexpected errors. The following image is a small portion of the diff between the DDL of the TX table before and after the column rename.

Note that besides the obvious column name change, ALTOBJ had problems recreating the PK_TX index, the claim_projection VIEW, and numerous triggers.

The original desire was to create an automated procedure to rename columns in DB2. However, because errors can be unpredictable due to unknowns in how DB2 implements the actual database, the creation of an automated method may be difficult.

It's only fair to share...
Share on FacebookGoogle+Tweet about this on TwitterShare on LinkedIn

Leave a Reply