Propagate DDL changes to many DB2 Subsystems

Document ID : KB000054080
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

I have a table which has an instance on a large number of my systems. I regularly need to make the same changes to each instance as many of these are test environments. Is there a way that I can do this that will allow me to avoid having to manually create individual DDL's for each instance?

It is also a requirement that a good audit trail of these changes are maintained to satisfy the internal audit department.

Many customer sites maintain the same set of tables related to a given application system on many different subsystems. These instances would include Production, System Test, Development, User Acceptance Test, Integration Test, User Training and Demonstration. There may be multiple occurrences of the same set of tables within these also. For example a Test environment may have multiple sets of tables being worked by different teams. The same could be true for other environments also. The same table may exist in a large number of places. All of these might need to be kept updated when the master table is updated particularly when a change is signed off at the development level and then must move through the quality assurance stages to production. In order to maintain consistency the process of propagating changes must be accurate and efficient in order to avoid delays due to failed program compilation jobs.

Solution:

RC/Migrator (RCM) has a facility called Incremental Change Language(ICL) which has been designed for just this purpose. ICL is able to be used for ALTERATION work but also changes that are picked up when two environments are COMPARED. In RCM these are called ALTERATION and COMPARE strategies. ICL can be generated from both.

EXAMPLE

Lets say for simplicity that we are adding a new column to a table. Every instance of that table would need a DB2 ALTER TABLE statement to be generated and executed. The DDL for each would have to be stored somewhere for later review. Additional complications could include multiple instances of the same table on the same subsystem with different CREATORS.

The ALTER statement for each would therefore have to be different according to the CREATOR. We might have to worry about re-establishing variations in DB2 security and/or aliases or views. Therefore there might be many additional DDL statements so that we avoid any inadvertent loss of related DB2 objects. Rebinding of Plans and packages might also be required for this change.

ICL ANALYSIS

When an ALTERATION or COMPARE strategy has been created the ICL is generated from it with an "I" line command. The ICL code that is generated is NOT DB2 code and can't be run by DB2. ICL can be thought of as pseudo code. ICL to DDL ANALYSIS with analysis line command "A"

After generating the ICL do an ICL to DDL Analysis. The analysis is used to tell RCM where the change is to be applied and also what type of Utilities, if any, are required to support that change. Any related objects are also re-established if needed. The ICL can be analyzed as many times as you want, each time specifying a different SSID if required.

ICL to DDL GROUP ANALYSIS with analysis line command "G"

When there are multiple tables of the same name with different creators on the same subsystem that are to be changed, this is done with an ICL to DDL GROUP Analysis. The COPY GROUP contains a list of elements each with its own GLOBAL CHANGE pointing to an individual CREATOR on the same subsystem. One DDL file is generated for each member of the COPY GROUP. Each one is generated to take into account any associated objects for that particular CREATOR.TABLE. Each element of the COPY GROUP also allows the user to specify different analysis utility and control options.

EXECUTION

The resulting DDL is stored as normal batch processor code. The generated ICL and the analysis produced from it can be stored on PDS libraries or on managed output.

AUDIT TRAIL

These ICL and ANALYSIS Outputs are all displayed on the RCM Strategy Services screen and can be protected from view/update if required. When each analysis DDL is executed, it stores an execution audit record on the product database which can be viewed to discover when it was executed and whether it was completed or not. This gives full visibility to any Audit function.

Warning

It is not recommended to manually edit ICL. It should always be generated from a strategy.

Other Relevant Information

Refer to the RC/Migrator User Guide regarding the use of ICL. The generated ICL can be imported to another subsystem for processing. Using the RCM ICL Import function the ICL can be imported onto a subsystem which does not share DASD with the originating subsystem. This creates a strategy which can then be analyzed.