Analysis of an RC/Migrator ICL Analysis Report results in error message DDL0500 and table not found on target subsystem
Article ID: 51092
Updated On:
Products
Issue/Introduction
An ICL Analysis of an RC/Migrator for Db2 for z/OS (RCM) Alter strategy is performed and generates the correct ICL.
The Alter is adding a new column to the EMP table.
The ICL statement is generated as follows:
ALTER TABLE DSN8810.EMP
ADD COLUMN COLX
FOLLOWS COMM
CHAR (1)
DEFAULT NULL
;
An (A) Analysis of this RC/Migrator ICL Analysis Report is performed to produce DDL. The generated Analysis Report results in the following error message:
--THE FOLLOWING ICL STATEMENTS WERE IN ERROR: -- -- ALTER TABLE DSN8810.EMP ADD COLUMN COLX FOLLOWS COMM CHAR ( 1 ) DEFAULT -- NULL -- -- ************************************************************************ -- DDL00500: TABLE DSN8810.EMP -- was not found on the target subsystem. -- ************************************************************************
The analysis was performed with Global Changes but these were ignored:
--Global Changes: -- -- SET NAME ===> EMPCHG -- SET CREATOR ===> TEST -- -- CODE DESCRIPTION -- TB TABLE -- TBCR CREATOR -- FROM DSN8810 TO DSN8910 --
On the target subsystem the table creator is DSN8910. The error messages refer to the source table creator DSN8810.
Why is RC/Migrator ignoring the Global Changes?
Resolution
DDL Global Changes are only applied after all ICL operations have been determined prior to final DDL output generation.
When the ICL has a name and creator that are not identical to the target name and creator, then in this situation a G (Group Analysis) should be performed rather than an A (Analysis).
ICL Global Changes are applied to the objects in the input ICL data set and are performed prior to ICL operations. Only Name and Creator specifications are applicable.
So in this case, the source (TEST) and target (PROD) creators are different. With a creator of DSN8810 for source, and DSN8910 for target, we must use Group Analysis, so that the Global Changes are applied to the input ICL data set. This will ensure global changes are applied to the NAME and CREATOR attributes to match the target environment.
To do this we must use Copy Group Services as part of Group Analysis to perform the Global Changes.
This can be done as follows:
On the Strategy Services panel use the G (Group Analysis) option against the ICL Analysis entry:
RMS1 R12 -------------- RC/M Strategy Services -------------- 10/07/15 11:10
COMMAND ===> SCROLL ===> CSR
DB2 SSID ===> ssid
STRATEGY ===> NEWCOL CREATOR ===> * TYPE ===> * SRC SSID ===> *
---------------------------------------------------------------------- USER001
T S SRC +---- LAST UPDATE ----+
O STRATEGY DESCRIPTION CREATOR P O SSID USER DATE TIME
_ NEWCOL ADD NEW COL TO EMP TABLE USER001 A U TEST USER001 10/07/15 10:10
G ICL * MANAGED OUTPUT * USER001 10/07/15 10:11 <- ICL Analysis
_ PROD * MANAGED OUTPUT * USER001 10/07/15 10:13 <- Failed (A) Analysis of ICL Analysis
******************************** BOTTOM OF DATA *******************************
The ICL to DDL Group Analysis panel is displayed. From here you first need to create a Copy Group.
To do this enter S in the COPY GROUP field:
RMA5IG R12 --------- RC/M ICL to DDL Group Analysis --------- 10/07/15 11:39
COMMAND ===>
EXECUTION SPECIFICATIONS ACCESS SPECIFICATIONS
EXECUTION MODE ===> ( O -Online, B -Batch) ACM ANALYSIS ===> N
OVERRIDE ===> N PDS ANALYSIS ===> N
RECOVERY ===> N
TARGET SPECIFICATIONS
OPTION SPECIFICATIONS SSID ===> ssid
UPDATE OPTIONS ===> N LOCATION ===> LOCAL
GLOBAL CHANGES ===> N
SET NAME ===> COPY GROUP SPECIFICATIONS
SET CREATOR ===> COPY GROUP ===> S
GROUP NAME ===>
ICL INPUT SPECIFICATIONS GROUP CREATOR ===>
EDIT INPUT ===> N ANALYSIS USER ===> USER001
STRATEGY ===> NEWCOL ANALYSIS DATE ===> 20100715
CREATOR ===> USER001 ANALYSIS TIME ===> 10115453
DDL OUTPUT ANALYSIS SPECIFICATIONS
EDIT OUTPUT ===> N ANALYSIS USER ===> USER001
STRATEGY ===> NEWCOL ANALYSIS DATE ===> N/A
CREATOR ===> USER001 ANALYSIS TIME ===> N/A
COMMENT ===>
The Copy Group Services panel is displayed. Note this panel can also be accessed via the RC/M Expert Profile menu. That is option 0 - Profile from the RC/Migrator Main Menu,
followed by option 7 - Copy Group Services from the RC/M Expert Profile panel.
To create a new Copy Group enter the details on the first line:
RMCG1 R12 ------------- RC/M Copy Group Services ------------ 10/07/15 08:05
COMMAND ===> SCROLL ===> CSR
RM288I: Please select a Copy Group for this analysis.
GROUP SSID ===> ssid
GROUP NAME ===> * CREATOR ===> *
---------------------------------------------------------------------- USER001
S +---- LAST UPDATE -----+
O GROUPNAME DESCRIPTION CREATOR O USER DATE TIME
c PRODCG__ Copy Group for ssid xxxx_ USER001 Y <== GROUP SET CREATION
_
_
Hit ENTER and the Copy Group Update panel will be displayed.
The Global Change set used in the initial Analysis can now be added to this Copy Group.
RMCG3 R12 -------------- RC/M Copy Group Update ------------- 10/07/15 11:52
COMMAND ===> SCROLL ===> CSR
Groupname ===> PRODCG Description ===> COPY GROUP FOR PROD
Creator ===> USER001 Share Option ===> Y (U,Y,N)
---------------------------------------------------------------------- USER001
GLOBAL GLOBAL-CHANGE-SET TRG UTILITY
O NUM COPY-ID DESCRIPTION CHANGES CREATOR NAME SSID OPTIONS
_ 1 EMPC DSN8810 TO DSN8910 Y USER001 EMPCHG ssid N
******************************* BOTTOM OF DATA ********************************
PF3 and this Copy Group can now be selected for this Analysis using the Copy Group Services panel.
Select the newly created Copy Group and hit ENTER. You will be returned to the 'ICL to DDL Group Analysis' panel.
Specify the target subsystem id in the TARGET SPECIFICATIONS fields.
TARGET SPECIFICATIONS SSID ===> ssid LOCATION ===> LOCAL
The Group Analysis is now ready to be performed, either in online or batch mode.
The generated ICL Analysis Report now contains the correct table creator, DSN8910. DDL statements are generated for target table TABLE
DSN8910.EMP. DDL, to drop and then create the target table, is generated with the new column added to the table DDL.
CREATE TABLE DSN8910.EMP <- target table
( EMPNO CHAR ( 6 ) NOT NULL
FOR MIXED DATA
, FIRSTNME VARCHAR ( 12 ) NOT NULL
FOR MIXED DATA
, MIDINIT CHAR ( 1 ) NOT NULL
FOR MIXED DATA
, LASTNAME VARCHAR ( 15 ) NOT NULL
FOR MIXED DATA
, WORKDEPT CHAR ( 3 )
FOR MIXED DATA
, PHONENO CHAR ( 4 )
FOR MIXED DATA
, HIREDATE DATE
, JOB CHAR ( 8 )
FOR MIXED DATA
, EDLEVEL SMALLINT
, SEX CHAR ( 1 )
FOR MIXED DATA
, BIRTHDATE DATE
, SALARY DECIMAL ( 9 , 2 )
, BONUS DECIMAL ( 9 , 2 )
, COMM DECIMAL ( 9 , 2 )
, COLX CHAR ( 1 ) <- new column
,PRIMARY KEY
...
..
.
Additional Information
In this example we have used Managed Output for the Analysis Reports rather than a dataset. For Managed Output, RC/MIGRATOR generates the group migration
analysis managed output member names automatically. This member name is based on the first four characters of the strategy name and then the copy-ID name.
In this case the member name is generated as NEWCEMPC (strategy NEWCOL and COPY-ID name EMPC).
RMS1 R12 -------------- RC/M Strategy Services -------------- 10/07/15 12:24
COMMAND ===> SCROLL ===> CSR
DB2 SSID ===> TEST
STRATEGY ===> NEWCOL CREATOR ===> * TYPE ===> * SRC SSID ===> *
---------------------------------------------------------------------- USER001
T S SRC +---- LAST UPDATE ----+
O STRATEGY DESCRIPTION CREATOR P O SSID USER DATE TIME
_ ________ _________________________ USER001 _ N ____ <== STRATEGY CREATION
_ NEWCOL ADD NEW COL TO EMP TABLE USER001 A U ssid USER001 10/07/15 10:10
_ ICL * MANAGED OUTPUT * USER001 10/07/15 10:11 <- ICL Analysis
_ PROD * MANAGED OUTPUT * USER001 10/07/15 10:13 <- Failed (A) Analysis of ICL Analysis
_ PROD * MANAGED OUTPUT * (NEWCEMPC) USER001 10/07/15 12:19 <- Group (G) Analysis of ICL Analysis
******************************** BOTTOM OF DATA *******************************
Feedback
