Analysis of an RC/Migrator ICL Analysis Report results in error message DDL0500 and 'table not found on target subsystem'.

Document ID : KB000051092
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

An ICL Analysis of an 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?

Solution:

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 ===> TEST                                                             
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     ===> TEST             
 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 ===> TEST                                                           
GROUP NAME ===> *          CREATOR ===> *                                      
---------------------------------------------------------------------- USER001
                                                                               
                                               S  +---- LAST UPDATE -----+     
O GROUPNAME DESCRIPTION               CREATOR  O  USER     DATE      TIME      
c PRODCG__  Copy Group for ssid PROD_ 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   PROD 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     ===> PROD    
 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 Note:

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 TEST 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 *******************************