PI85077: DDL OR GEN DOES NOT DISPLAY SQL-ROUTINE-BODY FOR SQL EXTERNAL PROCEDURES

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A DDL or GEN command does not display the SQL-routine-body for
  SQL external procedures.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Administration for          *
  *                 z/OS who use GEN/DDL function on             *
  *                 external SQL procedures which                *
  *                 contain a SQL-routine-body element.          *
  ****************************************************************
  * PROBLEM DESCRIPTION: When processing external SQL            *
  *                      procedures which contain a              *
  *                      SQL-routine-body ( typically            *
  *                      created through a call to               *
  *                      SYSPROC.DSNTPSMP ), the                 *
  *                      GEN/DDL function displays               *
  *                      incomplete DDL and does                 *
  *                      not generate the                        *
  *                      SQL-routine-body.                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When processing external SQL procedures which contain
  a SQL-routine-body ( typically created through a call to
  SYSPROC.DSNTPSMP ), the GEN/DDL function displays incomplete
  DDL and does not generate the SQL-routine-body.
  SQL-routine-body.
  

Problem conclusion

• Problem has been resolved.
  In addition, a new batch restart program / ADBTEP2 option
  has been introduced: 'USE_DSNTPSMP'
  
  When new ADBTEP2 option 'USE_DSNTPSMP' is specified,
  ADBTEP2 will use SYSPROC.DSNTPSMP options which
  was specified as functional comments in the WSL and will
  create the external SQL procedures with the SQL-routine-body
  through a CALL to the SYSPROC.DSNTPSMP stored
  procedure.
  
  The new option can be specified by setting 'Use DSNTPSMP'
  as YES on Batch Job Utility Parameters panel, ADB2UPA, that
  can be navigated by P.BP from main menu, ADB2.
  The generated JCL will have the new option in ADBTEPIN
  DD like.
  
  //ADBTEPIN DD *
  BINDERROR='MAXE'
  ,TSACCESS='NO'
  ,USE_DSNTPSMP='YES'
  ,AC='NO'
  ,RESTENV='BOTH'
  ;
  Example of SQL - external procedures with SQL-routine-body
  generated by GEN -:
  --
  --#SET TERMINATOR `
  --#ADMIN START EXTERNAL SQL PROCEDURE
  --BUILDSPROC SYSPROC.DSNTPSMP
  --PCOPTS NEWFUN(YES)
  --CMOPTS NOTEST(BLOCK,NOLINE,NOPATH)
  --PLKDOPTS
  --LKEDOPTS
  --BINDOPTS PACKAGE(NULLID) ACTION(REP)
     ISOLATION(CS)
  --PROCNAME stpschema.stpname
  --
  -----------------------------------------------------
  -- Stored procedure=>SQLID<.>object<
  ------------------------------------------------------
  --
    SET CURRENT SQLID=>SQLID<
  --
  CREATE PROCEDURE >object<
  (IN EMPLOYEE_NUMBER CHAR(10),
  IN RATE DECIMAL(6,2))
  LANGUAGE SQL
  FENCED
  EXTERNAL NAME >object<
  MODIFIES SQL DATA
  WLM ENVIRONMENT PARTSA
  DETERMINISTIC
  RUN OPTIONS MSGFILE(OUTFILE),
  RPTSTG(ON),RPTOPTS(ON)
  COMMIT ON RETURN YES
  UPDATE EMP
  SET SALARY = SALARY * RATE
  WHERE EMPNO = EMPLOYEE_NUMBER-
  --
     COMMIT`
  --
   --#ADMIN END EXTERNAL SQL PROCEDURE
  --#SET TERMINATOR ;
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI51254 UI51255

Modules/Macros

•    ADBASW   ADBCCMM  ADBCDTS  ADBCEST  ADBCP
  ADBCRCR  ADBCSPG  ADBCVCT  ADBCVEY  ADBCVIC  ADBCVOC  ADBDAT
  ADBDK    ADBEALT  ADBHUPA  ADBLI    ADBNSPB  ADBPA    ADBPA1
  ADBTEPI  ADBTEPR  ADBTEPU  ADBTEP2  ADBVFC   ADBVFCA  ADBVFCF
  ADBV99   ADB2CDC  ADB2CID  ADB2CMF  ADB2CMP  ADB2CMR  ADB2CMS
  ADB2CMT  ADB2DEP  ADB2FMT  ADB2MSK  ADB2MSQ  ADB2OVW  ADB2PA
  ADB2PA3  ADB2PA4  ADB2PA5  ADB2PA6  ADB2PA7  ADB2RE   ADB2REE
  ADB2REG  ADB2REH  ADB2REM  ADB2REP  ADB2REU  ADB2REY  ADB2REZ
  ADB2RGC  ADB2RPT  ADB2TEP  ADB2UPA  ADB2WCL  ADB2WIN  ADB2WVL
  ADB27AC  ADB28MC
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RB20 PSY UI51254

     UP17/10/23 P F710

• RC10 PSY UI51255

     UP17/10/23 P F710

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.