CA RC/Migrator for DB2 for Z/OS : Use a Group ICL to DDL Analysis to change two target tables

book

Article ID: 223908

calendar_today

Updated On:

Products

CA RC/Migrator for DB2 for z/OS

Issue/Introduction

When there are multiple objects 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 DB2 object 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 DB2 object. Each element of the COPY GROUP also allows the user to specify different analysis utility and control options.

Environment

DB2 for Z/OS

Resolution

Propagate a new column on table AUTHID1.TBROLE to two other tables on the same subsystem: AUTHID1.TBROLE4   and   AUTHID2.TBROLE4. All three start with the same column structure.

Consider this table as an example:

TABLE NAME         CREATOR  COLUMN NAME  COLTYPE   LENGTH
TBROLE             AUTHID1
                            ROLE_ID      CHAR           6
                            ROLE_DESC    CHAR          50
                            ROLE_SALARY  DECIMAL       16
                            ROLE_SALARY> CHAR           1

(1) Create an ALTERATION STRATEGY for this table in order to add a new column called ROLE_NEWCOL as below.

CMD ### PS COLUMN NAME        COLUMN TYPE        SIZE       N D FORDAT PK UK FK
___ 1      ROLE_ID            CHAR               6          N _ SBCS   1
___ 2      ROLE_DESC          CHAR               50         N _ SBCS   __
___ 3      ROLE_SALARY        DECIMAL            16,0       N Y _____  __
___ 4      ROLE_SALARY_CODE   CHAR               1          N C SBCS   __
___ 5      ROLE_NEWCOL        CHAR               1          N C SBCS   __

Save the strategy and then using the "I" line command generate the ICL.

The ICL can be browsed with the "B" line command and the ICL for this alteration would appear like this:

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

ALTER TABLE AUTHID1.TBROLE
      ADD   COLUMN     ROLE_NEWCOL
            FOLLOWS    ROLE_SALARY_CODE
            CHAR       (1)
            NOT NULL
            DEFAULT  'A'
            FOR SBCS   DATA
   ;

The ICL code above that is generated is not DB2 SQL code and can't be run by DB2 utilities. ICL can be thought of as kind pseudo code or a higher level language of DB2 SQL.
It is not recommended to manually edit ICL. It should always be generated from a strategy.

The two target tables on the same SSID have a slightly different name, one has the same creator as the source and the other has a different creator.

TABLE NAME         CREATOR  COLUMN NAME  COLTYPE   LENGTH N   COLNO
TBROLE4            authid1
                            ROLE_ID      CHAR           6 N       1
                            ROLE_DESC    CHAR          50 N       2
                            ROLE_SALARY  DECIMAL       16 N       3
                            ROLE_SALARY> CHAR           1 N       4
TBROLE4            authid2
                            ROLE_ID      CHAR           6 N       1
                            ROLE_DESC    CHAR          50 N       2
                            ROLE_SALARY  DECIMAL       16 N       3
                            ROLE_SALARY> CHAR           1 N       4

(2) Initiate a "Group analysis" by placing a "G" next to the ICL line under the Strategy that was created after the ICL was created.

STRATEGY DESCRIPTION
________ _________________________
TBROLEIC
G ICL  * MANAGED OUTPUT *

On the RC/M ICL to DDL Group Analysis screen enter an "S" in the Copy Group Specifications field. This will go to the RC/M Copy Group Services where a new COPY GROUP can be created.

COPY GROUP SPECIFICATIONS
 COPY GROUP     ===> S
 GROUP NAME     ===>
 GROUP CREATOR  ===>

RMCG1 20.0   ------------- RC/M Copy Group Services ------------ yy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

GROUP SSID ===> ssid
GROUP NAME ===> *          CREATOR ===> authid1
--------------------------------------------------------------------- BASLU02

                                               S  +---- LAST UPDATE -----+
O GROUPNAME DESCRIPTION               CREATOR  O  USER     DATE      TIME
C TBROLE4_  copy group for TBROLE____ authid1  N  <== GROUP SET CREATION

When the RC/M Copy Group Update is displayed fill in the details of the copy group. Place an "S" in the GLOBAL CHANGES column in order to create two new GLOBAL CHANGE

RMCG3 20.0   -------------- RC/M Copy Group Update ------------- yy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

Groupname ===> TBROLE4          Description  ===> COPY GROUP FOR TBROLE
Creator   ===> authid1          Share Option ===> N (U,Y,N)
--------------------------------------------------------------------- authid1

                                         GLOBAL  GLOBAL-CHANGE-SET TRG  UTILITY
O NUM COPY-ID  DESCRIPTION               CHANGES CREATOR  NAME     SSID OPTIONS
_   1 one_     CHANGE authid1.TBROLE4___ s       ________ ________ ____ N
_   2 two_     CHANGE authid2.TBROLE4___ s       ________ ________ ____ N
Create the first Global Change......
RMR1 20.0   ------------ RC/M Global Change Services ----------- yy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

SET SSID ===> ssid
SET NAME ===> *          CREATOR ===> authid1
--------------------------------------------------------------------- authid1

                                              S  +---- LAST UPDATE -----+
O SETNAME  DESCRIPTION               CREATOR  O  USER     DATE      TIME
c TBROLE1_ Glb Chg:authid1.TBROLE4   authid1  U  <== GLOBAL SET CREATION

For this first one this is the Global Change:
TB            TABLE
TBNM          _ NAME                 TBROLE____________     TBROLE4___________
TBCB          _ CREATED BY           __________________     __________________
TBCR          _ CREATOR              authid1___________     authid1___________

PF3 and then "S" select this new global change....

Then create a second Global change when the first is saved.

RMR1 20.0   ------------ RC/M Global Change Services ----------- yy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR
RM285 RM285I: Select the global change set for COPY-ID TWO
SET SSID ===> ssid
SET NAME ===> *          CREATOR ===> *
--------------------------------------------------------------------- BASLU02

                                              S  +---- LAST UPDATE -----+
O SETNAME  DESCRIPTION               CREATOR  O  USER     DATE      TIME
c TBROLE2_ GLB CHG:authid2.TBROLE4__ authid1  U  <== GLOBAL SET CREATION

For this first one this is the Global Change:
TB            TABLE
TBNM          _ NAME                 TBROLE____________     TBROLE4___________
TBCB          _ CREATED BY           __________________     __________________
TBCR          _ CREATOR              authid1___________     authid2___________

PF3 and then "S" select this new global change....

Then this is the Copy Group screen that is the result:

RMCG3 20.0   -------------- RC/M Copy Group Update ------------- yy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

Groupname ===> TBROLE4          Description  ===> COPY GROUP FOR TBROLE
Creator   ===> authid1          Share Option ===> N (U,Y,N)
--------------------------------------------------------------------- authid1

                                         GLOBAL  GLOBAL-CHANGE-SET TRG  UTILITY
O NUM COPY-ID  DESCRIPTION               CHANGES CREATOR  NAME     SSID OPTIONS
_   1 ONE      CHANGE authid1.TBROLE4    Y       authid1  TBROLE1  ____ N
_   2 TWO      CHANGE authid2.TBROLE4    Y       authid1  TBROLE2  ____ N

PF3 to save the Copy group and select the new group with an "S" line command which returns to the ICL to DDL Group Analysis screen.

The Analysis can now be submitted either ONLINE or BATCH.

This will result in two individual Analysis outputs.

RMS1 20.0   -------------- RC/M Strategy Services -------------- yy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

DB2 SSID ===> ssid
STRATEGY ===> TBROLEIC CREATOR ===> authid1  TYPE ===> *    SRC SSID ===> *
--------------------------------------------------------------------- authid1

                                              T S SRC  +---- LAST UPDATE ----+
O STRATEGY DESCRIPTION               CREATOR  P O SSID   USER     DATE   TIME
_ ________ _________________________ authid1  _ N ____ <== STRATEGY CREATION
_ TBROLEIC                           authid1  A U ssid authid1 yy/mm/dd hh:mm
_ ICL  * MANAGED OUTPUT *                            authid1 yy/mm/dd hh:mm
_ ssid * MANAGED OUTPUT * (TBROONE)                  authid1 yy/mm/dd hh:mm
_ ssid * MANAGED OUTPUT * (TBROTWO)                  authid1 yy/mm/dd hh:mm

Browsing the first one the DDL generated from the ICL looks like this:

ALTER   TABLE authid1.TBROLE4
        ADD ROLE_NEWCOL CHAR ( 1 )
                                    NOT NULL WITH DEFAULT
        'A'
                                    FOR SBCS DATA
            ;

 

Browsing the second one the DDL generated from the ICL looks like this:

ALTER   TABLE authid2.TBROLE4
        ADD ROLE_NEWCOL CHAR ( 1 )
                                    NOT NULL WITH DEFAULT
        'A'
                                    FOR SBCS DATA
            ;

 

If the utilities are requested in the analysis options the ALTER statements could be followed by a REORG and or a RUNSTATS utility.