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

Products

• RC/Migrator for DB2 for z/OS
• RC Compare for DB2 for z/OS

Issue/Introduction

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

- The Analysis of a RC/Migrator for Db2 for z/OS (RCM) migration strategy, to migrate a native Stored Procedure, is not generating any DDL at execution.
- 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.

Resolution

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 statement	Meaning	Yes	No
1	.IF  (PROCEDURE "USER01"."UPDATE_PROCEDURE")	Does USER01.UPDATE_PROCEDURE already exist	Go to 2	CREATE PROCEDURE USER01.UPDATE_PROCEDURE
2	.IF  (PROCEDURE "USER01"."UPDATE_PROCEDURE"."V1")	Is it version 1?	Go to 3	ALTER 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