AHA request for Db2 Administration Tool

We are using another vendors DB2 database management solution and have developed a fully automated batch process which propagates DDL changes to many DB2 for z/OS DB2 systems.

Our application developers use several different Db2 environment to implement their changes. These changes may also require changes to the Db2 data model.

The application and DDL changes are propagated to various TEST, QA and PROD environments using the Serena Changeman product.

When such a change has arrived at a PROD system, it is automatically installed in all pre-production Db2 systems (other DEV, TEST or QA environments) where it is missing.

One of the most important use cases of the DDL change process is to add new columns to existing Db2 tables. New columns are always appended to the end of the table. But, as it is possible that 2 application development teams add a new column to the same table in parallel in different development environments, it is important to remember what the last column of the table was when the DDL change was developed. Otherwise, we cannot guarantee the same column sequence in all DB2 environments.

Let’s look at an example: Table TB1 contains only columns A and B.

We want to add column C to the table.

The DB2 database management solution we are currently using translates “ALTER TABLE T1 ADD COLUMN C” into an internal ICL statement “ALTER TABLE TB1 ADD COLUMN C AFTER B”. The internal ICL statement of the current solution remembers the column (B) after which the new column (C) is appended.

We tried to implement the same logic using the Change Management of the DB2 Administration Tool. When we import an “ALTER TABLE T1 ADD COLUMN C” it is internally stored as is. Change Management has no knowledge of the last column where the new column should be appended.

When we export such a change to other DB2 environments the sequence of columns in the table is determined by the execution sequence of the changes. But the sequence in which the changes are executed is controlled by Changeman and can be different in different DB2 environments.

Example:

Change CHG1: add column C to table TB1

Change CHG2: add column D to table TB1

In DB2 system DEV1 run CHG1 first, then CHG2.

In DB2 system DEV2 run CHG2 first , then CHG1.

As a result, you will get TB1 ( A,B,C,D) in DEV1 and TB1 (A,B,D,C) in DEV2.

Request for Enhancement:

We are looking for an ADMIN ALTER statement which allows to add a column after another column.

Currently, there is an ADMIN statement “ADMIN ALTER TABLE TB1 INSERT COLUMN C BEFORE B”.

In analogy to this statement, we are asking for the implementation of a “ADMIN ALTER TABLE TB1 INSERT COLUMN C AFTER B” functionality.

This functionality would allow us to control where a new column is appended.

As the ADMIN ALTER syntax is not officially documented we would prefer if this statement could be generated by change management as the result of an import of the “ALTER TABLE TB1 ADD COLUMN C” statement.

Example:

//IMPORT EXEC GOCCM

//GOCCM.PARMS DD *

ACTION_IMPORT_CHANGE=’Y’

ACTION_COMPARE=’N’

//IMCHG001 DD *

ALTER TABLE TB1 ADD C …

This import should result in an internal change statement : “ADMIN ALTER TABLE TB1 INSERT COLUMN C AFTER B ….”.

Mr Richard Schaufuss <schaufus@us.ibm.com> asked us to open the AHA and is aware of the problem .