Use a Group ICL to DDL Analysis to change two target tables using RC/Migrator
Article ID: 223908
Updated On:
Products
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 a RC/Migrator for Db2 for z/OS (RCM)
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.
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
--------------------------------------------------------------------- authid1
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 ===> *
--------------------------------------------------------------------- authid1
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.
Additional Information
Feedback
