search cancel

CA RC/Migrator for DB2 for Z/OS : Propagate DDL changes to many DB2 Subsystems

book

Article ID: 54080

calendar_today

Updated On:

Products

RC Compare for DB2 for z/OS RC/Migrator for DB2 for z/OS

Issue/Introduction

Consider a table which has an instance on a large number of systems. It must regularly be updated to make the same changes to each instance as many of these are test environments. Is there a way to avoid having to manually create individual DDL's for each instance?

It is also a requirement that a good audit trail of the 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.

 

Environment

Release: R19, R20
Component: RCC

Resolution

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

For simplicity this example will be adding a new column to a table, a column called PROJ_NEWCOL. 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. The task must also concern itself with re-establishing variations in DB2 security and/or aliases or views. Therefore there might be many additional DDL statements in order to avoid any inadvertent loss of related DB2 objects. Rebinding of Plans and packages might also be required for this change if these type of DB2 objects are using the table.

Table DDL:

   CREATE TABLE AUTHID.TBPROJECT
     (PROJ_NO CHARACTER(6) FOR MIXED DATA            NOT NULL
     ,PROJ_NAME CHARACTER(20) FOR MIXED DATA         NOT NULL
     ,PROJ_DEPT CHARACTER(6) FOR MIXED DATA          NOT NULL
     ,PROJ_MGR CHARACTER(6) FOR MIXED DATA  WITH DEFAULT NULL
     ,PROJ_START DATE  WITH DEFAULT NULL
     ,PROJ_END DATE  WITH DEFAULT NULL
     ,PROJ_DESC CHARACTER(100) FOR MIXED DATA  WITH DEFAULT NULL
     ,PROJ_SUPERIOR CHARACTER(6) FOR MIXED DATA  WITH DEFAULT NULL
       ,CONSTRAINT PROJ_NO PRIMARY KEY
       (PROJ_NO
       )
       )
       IN DBCORP1.TSPROJ
  APPEND NO
  NOT VOLATILE CARDINALITY
  DATA CAPTURE NONE
  AUDIT NONE
  CCSID EBCDIC;

The Alteration Strategy object looks like this:

Table       => TBPROJECT_R         > Creator   => AUTHID    > Comm/Lab => N
Database    => DBCORP1               Editproc  =>          Data Cap => NONE
Tablespace  => TSREJ                 Validproc =>          OBID     =>
Partitioning > NO  (TS Parts:NONE)   Audit     => NONE     Volatile => N
Table Type  => REGULAR               Restrict  => N        CCSID    => EBCDIC
Row Size    => 164/-3,884            Forgn Key => N        Chk Const > N
                                                           Append   => N

CMD ### PS COLUMN NAME        COLUMN TYPE        SIZE       N D FORDAT PK UK FK
___ 1      PROJ_NO            CHAR               6          N _ MIXED  __
___ 2      PROJ_NAME          CHAR               20         N _ MIXED  __
___ 3      PROJ_DEPT          CHAR               6          N _ MIXED  __
___ 4      PROJ_MGR           CHAR               6          Y N MIXED  __
___ 5      PROJ_START         DATE               4          Y N _____  __
___ 6      PROJ_END           DATE               4          Y N _____  __
___ 7      PROJ_DESC          CHAR               100        Y N MIXED  __
___ 8      PROJ_SUPERIOR      CHAR               6          Y N MIXED  __
___ 9      PROJ_NEWCOL        CHAR               6          Y N MIXED  __
******************************* BOTTOM OF DATA ********************************

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 utilities. ICL can be thought of as pseudo code or a higher level language than DB2 SQL.

-- **************************************************************
-- *                                                            *
-- * TABLE INCREMENTAL CHANGE LANGUAGE STATEMENTS               *
-- *                                                            *
-- **************************************************************

ALTER TABLE AUTHID.TBPROJECT_R
      ADD   COLUMN     PROJ_NEWCOL
            FOLLOWS    PROJ_SUPERIOR
            CHAR       (6)
            DEFAULT  NULL
            FOR MIXED  DATA
   ;

ICL to DDL ANALYSIS with analysis line command "A".

After generating the ICL perform 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.

-- **************************************************************
-- *                                                            *
-- * TABLE CREATE AND ALTER STATEMENTS                          *
-- *                                                            *
-- **************************************************************


-- AUTHID.TBPROJECT_R WILL BE ALTERED VIA THESE NATIVE DB2 COMMANDS.

ALTER   TABLE AUTHID.TBPROJECT_R
        ADD PROJ_NEWCOL CHAR ( 6 )
                                    FOR MIXED DATA
            ;

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.

Groupname ===> CR0X             Description  ===> ONE TO MANY CREATORS
Creator   ===> AUTHID Share Option ===> U   (U,Y,N)
--------------------------------------------------------------------- AUTHID

                                         GLOBAL  GLOBAL-CHANGE-SET TRG  UTILITY
O NUM COPY-ID  DESCRIPTION               CHANGES CREATOR  NAME     SSID OPTIONS
  1 CR02     CR01 TO CR02              Y       AUTHID  CR0X2         N
  2 CR03     CR01 TO CR03              Y       AUTHID  CR0X3         N
  3 CR04     CR01 TO CR04              Y       AUTHID CR0X4         N
  4 CR05     CR01 TO CR05              Y       AUTHID CR0X5         N

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.

 

Additional 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 RC/Migrator 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.