View result set returned by DSNTPSMP when migration of SQL Stored Procedures encounters SQL error +466?

Products

• RC Compare for DB2 for z/OS

Issue/Introduction

Description:

SQL Error +466 is returned when migrating SQL stored procedures (SP) using RC/Migrator's RMRSSP stored procedure. The error result set created by IBM's DSNTPSMP SP can not be found in the RC/Migrator job output, or in the SYSTSPRT output for the WLM address space that executes RMRSSP.
Changes can be made to the REXX code of member RMRSSP in the CLIST product dataset, as well as to the RMRSSP parameter in the MIGRATOR member of the PARMLIB product dataset.
These changes will cause the error result set produced by DSNTPSMP for the +466 SQL Error to be displayed in SYSTSPRT of the WLM address space that executes the RMRSSP. Details are in the Implementation Guide, Chapter 6, as well as comments in the RMRSSP member of the CLIST dataset.

Solution:

The situation is you are using RC/Migrator's (RCM) Stored Procedure (SP) process to migrate SQL Stored Procedures. This involves RCM's RMRSSP SP, which invokes IBM's DSNTPSMP SP to build the SQl stored procedure being migrated.
These are both REXX based stored procedures and as a result, each will execute under their own WLM environment.
When there is a problem with the syntax in the SQL stored procedure being migrated, an SQL Error +466 is issued, and DSNTPSMP will provide an error result set if one codes a routine to retrieve and display it.

In order for this error result set to be displayed by RCM's RMRSSP, the following changes need to be done:

1. Modify the REXX code in member RMRSSP from the CLIST product dataset.
   
   Look for the following block of code:

   ...                                                                  /*  if (wordpos('VD', SspRmrssp) <> 0) then do     */                /*  currently, DSNREXX V8 has a problem with LOCATOR's etc. */          if ((wordpos('VD', SspRmrssp) <> 0) ,                                 & (SspTargetDB2Version < 'V8R1M0')) then do                      ...

   It should be around line 553.
   
   Modify the code to uncomment one line and comment out two others.
   It should look like this:

                                            ...                                                                      if (wordpos('VD', SspRmrssp) <> 0) then do                       /*  currently, DSNREXX V8 has a problem with LOCATOR's etc. */       /* if ((wordpos('VD', SspRmrssp) <> 0) ,  */                         /*  & (SspTargetDB2Version < 'V8R1M0')) then do */                   ...

2. Update the RMRSSP parameter in the MIGRATOR PARMLIB member.
   It is specific to a DB2 subsystem, so you would need to do this for each DB2 ssid where you plan to migrate SQL stored procedures.
   
   The parameter should look like this:

                                             ...                                                                           RMRSSP     (VD)                    /* SUBSYSTEM TAG IDENTIFIER      */        ...

   This will cause the analysis of the strategy that is migrating SQL stored procedures
   to include this parameter tag or string:

                                         ...                                                                           <SspRmrssp> VD </SspRmrssp>                                                   ...

   The modified REXX code in RMRSSP will check for this parameter and, when present, retrieve the result set from DSNTPSMP and output it to SYSTSPRT for the WLM address space that executes RMRSSP.
   
   The output might look something like this:

                                        ...                                                                            DSNTPSMP|CONSOLE|1|*** DB2 V8R1 SQL Procedure Processor DSNTPSMP 1.21 (PK5901 2008-04-04)                                                                   DSNTPSMP|CONSOLE|2|** ERROR ** The procedure name in the source does not match the routine name in parameter 2.                                              DSNTPSMP|CONSOLE|3|**  procedure name: USERID1.AKSP02                          DSNTPSMP|CONSOLE|4|**     parameter 2: USERID2.AKSP02                          DSNTPSMP|CONSOLE|5|Function "REBUILD" cannot continue.                         DSNTPSMP|Summary|1|Request: REBUILD routine USERID2.AKSP02 did not complete.   ...

This process is documented in the Implementation Guide, Chapter 6, section on RC/Migrator DB2 customization. Additional information is also found in the comments in the RMRSSP REXX code.

Note: If you have a DB2 V7 subsystem, apply IBM PTF UQ87310 for APAR PQ84855. If you have a DB2 V8 subsystem, apply IBM PTF UQ87582 for APAR PQ86105.

Environment

Release:
Component: RCM