PM57851: INCORROUT IVP, DSNTEJ67, JOB STEP JOB STEP PH067S04, RETURN CODE 8 FOR SQLCODE -104 OR SQLCODE -20071

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• INCORROUT IVP, DSNTEJ67, job step job step PH067S04, return code
  8 for either of the following SQLCODEs:
  
  1.) SQLCODE -104 when DECIMIAL=COMMA in DSNHDECP
  
  2.) SQLCODE -20071 when DSN6SYSP.WLMENV=blank and native SQL
      routine debugging is activated
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DB2 sample job DSNTEJ67 in DB2  *
  *                 9.1 for z/OS and DB2 10 for z/OS are         *
  *                 affected by this change.                     *
  ****************************************************************
  * PROBLEM DESCRIPTION: Job DSNTEJ67 job step PH067S04 ends     *
  *                      with return code 8 and these messages:  *
  *                                                              *
  *                        Setting up the native SQL PL helper   *
  *                          routine...                          *
  *                        *CRSQLPL* Deploying SP:               *
  *                          "DSN8"."DSN8EN1"                    *
  *                        *CRSQLPL* Error                       *
  *                          SQLCODE =<s>                        *
  *                          . . .                               *
  *                        DSNTEJ67 cannot deploy the native SP  *
  *                          used for migration.                 *
  *                                                              *
  *                      where <s> is -104 or -20071.            *
  *                                                              *
  *                      In V10 only, these SQLCODEs may also    *
  *                      be received when processing native SQL  *
  *                      function DSN8.DSN8EF1.                  *
  ****************************************************************
  * RECOMMENDATION: Apply the fixing PTF for this APAR.          *
  ****************************************************************
  DB2 job DSNTEJ67 demonstrates a way to convert an external SQL
  procedure to a native SQL procedure.  Currently, depending on
  local DB2 configuration, DSNTEJ67 can terminate in job step
  PH067S04 with return code 8 and these messages:
  
    Setting up the native SQL PL helper routine...
    *CRSQLPL* Deploying SP: "DSN8"."DSN8EN1"
    *CRSQLPL* Error
      SQLCODE =<error SQL code>
      . . .
    DSNTEJ67 cannot deploy the native SP used for migration.
  
  due to one of the following errors:
  
  (1) SQLCODE -104 when DB2 uses DSNHDECP.DECIMAL=COMMA. This
      error can occur because some coding in sample native SQL
      procedure DSN8.DSN8EN1 (which is prepared by job DSNTEJ67)
      contains tokens separated by commas that are not immediately
      followed by a blank.  Such coding, when processed by a DB2
      that uses the comma as the decimal point indicator (in other
      words uses DSNHDECP.DECIMAL=COMMA), is interpreted as an
      invalid numeric value and rejected with SQLCODE -104.  In
      V10 only, this error can also be encountered when processing
      sample native SQL function DSN8.DSN8EF1.
  
  (2) SQLCODE -20071 when DB2 uses DSN6SYSP.WLMENV set to blank
      and native SQL debugging is not DISABLED.  Currently, the
      CREATE PROCEDURE statement for DSN8.DSN8EN1 does not specify
      the DEBUG MODE clause, thus the DEBUG MODE default is
      determined from the value of the CURRENT DEBUG MODE special
      register.  The CREATE PROCEDURE statement for DSN8.DSN8EN1
      also does not specify the WLM ENVIRONMENT FOR DEBUG MODE
      clause, thus the default is determined using the value of
      the DSN6SYSP.WLMENV subsystem parameter.  If the value of
      DSN6SYSP.WLMENV is blank when debugging is activated,
      processing of DSN8.DSN8EN1 fails with SQLCODE -20071 because
      no default WLM environment is defined. In V10 only, this
      error can also be encountered when processing sample native
      SQL function DSN8.DSN8EF1.
  
  Additional keywords: SQLPL SQLNATIVESQLPL SQLPROC SQLSCALARUDF
                       SQLCODE104 SQLCODE20071
  

Problem conclusion

• In response, this APAR makes the following changes to DSN8EN1,
  the source code for DSN8.DSN8EN1 and (in V10 only) to DSN8EF1,
  the source code for the sample native SQL function,
  DSN8.DSN8EF1:
  (1) To resolve the SQLCODE -104 error, all SQL statements that
      are not compatible with use of the DSNHDECP.DECIMAL=COMMA
      option are corrected.
  (2) To resolve the SQLCODE -20071 error, the DISABLE DEBUG MODE
      clause is added to the CREATE statements to ensure that a
      default WLM environment name is never required.
  

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:

  UK78343 UK78347

Modules/Macros

• DSN8EF1  DSN8EN1
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK78343

     UP12/05/15 P F205

• R910 PSY UK78347

     UP12/05/15 P F205

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.