Propagate DDL changes to many Db2 Subsystems using RC/Migrator
Article ID: 54080
Updated On:
Products
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.
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 of 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.
Feedback
