Migration of a Native Stored Procedure generates code with different DDL statements

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

Multiple versions can exist for the same native SQL stored procedure.  If multiple versions exist, only one procedure can be active.

 

Question:

- The Analysis of a migration strategy, to migrate a native Stored Procedure, is not generating any DDL at execution.

- I used analyze option  'DROP OBJECTS  ==> Y' but no 'DROP PROCEDURE ..' is generated.

- Why does RC/Migrator generate .IF statements, and different DDL for the selected NSP, in the SCRIPT.

Answer:

During migration, the procedure is created or added in the target environment, depending on whether the procedure is the first version or an additional version. 

This is done by checking the status of the NSP on the target system during execution of the script. The .IF statements are interpreted by the Batch Processor and necessary DDL executed.

Here is an example of the code generated:

.IF  (PROCEDURE "USER01"."UPDATE_PROCEDURE")
  .IF  (PROCEDURE "USER01"."UPDATE_PROCEDURE"."V1")
    .IF  (ACTIVE <> 'Y' )     
      ALTER PROCEDURE "USER01"."UPDATE_PROCEDURE”
            REPLACE VERSION "V1"
    .ELSE
      .RESUME
    .ENDIF
  .ELSE
    ALTER PROCEDURE "USER01"."UPDATE_PROCEDURE"
          ADD VERSION "V1"
  .ENDIF
      (
       IN IN_COL3 CHAR(3) CCSID EBCDIC FOR SBCS DATA
      ,
       OUT OUT_COL1 CHAR(1) CCSID EBCDIC FOR SBCS DATA
      (
      LANGUAGE SQL
      DETERMINISTIC
      ----etc--
      ----etc-
.ELSE
    CREATE PROCEDURE USER01.UPDATE_PROCEDURE
      (
       IN IN_COL3 CHAR(3) CCSID EBCDIC FOR SBCS DATA
      ,
       OUT OUT_COL1 CHAR(1) CCSID EBCDIC FOR SBCS DATA
      (
      LANGUAGE SQL
      DETERMINISTIC
      ----etc--
      ----etc-
.ENDIF
 
.SYNC 5         'DDL FOR PROCEDURE USER01.UPDATE_PROCEDURE'

 

Here is an explanation of the process: 
 

#IF statementMeaningYesNo
1.IF  (PROCEDURE "USER01"."UPDATE_PROCEDURE")Does USER01.UPDATE_PROCEDURE
already exist
Go to 2CREATE PROCEDURE USER01.UPDATE_PROCEDURE                       
2.IF  (PROCEDURE "USER01"."UPDATE_PROCEDURE"."V1")Is it version 1?Go to 3ALTER PROCEDURE USER01.UPDATE_PROCEDURE
ADD VERSION "V1"
 
3.IF  (ACTIVE <> 'Y' )Is it NOT active?ALTER PROCEDURE USER01.UPDATE_PROCEDURE
REPLACE VERSION "V1"
 
Skip migration of this NSP
 

In this example version 1 (V1) of the NSP was selected for migration. However, any version could be selected and V1 would change accordingly (e.g. V3). 

If you want to migrate an NSP and unconditionally DROP the existing procedure, you can generate an SQL ONLY analyze with DROP OBJECTS = Y 

BND/DAT/STA/SQL/GRNT/RI ==> S

DROP OBJECTS      ==> Y  

 Note:

- All versions of the native SQL procedure are dropped with the DROP PROCEDURE statement. The new procedure becomes the initial version (V1).

 - With SQL ONLY no .SYNC statements are generated and restart to a specific sync point will not be possible

 

Additional Information:

CA Database Management Solutions for DB2 for z/OS documentation / Use the Batch Processor. 

R19 Define and Execute Refined Batch Processor Scripts

R20 Define and Execute Refined Batch Processor Scripts

 

CA RC/Migrator™ for DB2 for z/OS and CA RC/Compare documentation

R19 Migrating Native SQL Stored Procedures

R20 Migrating Native SQL Stored Procedures