PH17684: ABEND04E RC00E70005 MAY OCCUR AT DSNXEBPG M113 WHEN AN OLD STORED PROCEDURE CALLING AN APPLICATION CONTAINING A TRIM

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABEND04E RC00E70005 INSTEAD OF AN EXPECTED SQLCODE -440 MAY
  OCCUR AT DSNXEBPG M113 WHEN AN OLD STORED PROCEDURE CALLING AN
  APPLICATION CONTAINING A TRIM FUNCTION
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users who use the                        *
  * VALIDATE(RUN) bind option or the                             *
  * SQLERROR(CONTINUE) bind option and the                       *
  * package references a built-in function.                      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Abend ABND0E4 DSNXGRDS.DSNXEBPG OFFSET                       *
  * M113 or an incorrect SQLCODE -440                            *
  * may occur if the following sequence of                       *
  * events happen:                                               *
  * 1. A package is bound with the                               *
  * VALIDATE(RUN) bind option or with the                        *
  * SQLERROR(CONTINUE) bind option. The                          *
  * package can be an application, a                             *
  * native SQL procedure, or a compiled                          *
  * SQL scalar user-defined                                      *
  * function (UDF), etc.                                         *
  *    a. If VALIDATE(RUN) is used,                              *
  *       message DSNX100I is issued to                          *
  *       indicate not all objects or                            *
  *       privileges exist at bind time                          *
  *       but the bind succeeds.                                 *
  *    b. If SQLERROR(CONTINUE) is                               *
  *       used, message DSNX200I is issued                       *
  *       to indicate the error at bind                          *
  *       time but the bind succeeds.                            *
  *    c. The package references a                               *
  *       built-in function (BIF) that is                        *
  *       supported in a higher release                          *
  *       than the release dependency                            *
  *       marking of  the package,                               *
  *       SYSPACKAGE.IBMREQD. For example,                       *
  *       the release dependency marking                         *
  *       of the package is V8,                                  *
  *       SYSPACKAGE.IBMREQD = 'L',                              *
  *       but a TRIM('A   ')  BIF is                             *
  *       referenced in the package which                        *
  *       is supported in V10 and higher.                        *
  * 2. The missing object is defined or                          *
  * the missing privilege is granted, or                         *
  * the SQL error is corrected.                                  *
  * 3. The subsequent REBIND PACKAGE                             *
  * command without changing the                                 *
  * APPLCOMPAT bind option value is issued                       *
  * to rebind the package or another BIND                        *
  * PACKAGE command is issued to bind the                        *
  * package again (in the case of SQLERROR                       *
  * (CONTINUE)), but the command receives                        *
  * the 04E abend or the incorrect SQLCODE                       *
  * -440.                                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  Abend ABND0E4 DSNXGRDS.DSNXEBPG OFFSET M113 or an incorrect
  SQLCODE -440 may occur if the following sequence of events
  happen:
  1. A package is bound with the VALIDATE(RUN) bind option or
  with the SQLERROR(CONTINUE) bind option. The package can be
  an application, a native SQL procedure, or a compiled SQL
  scalar user-defined function (UDF), etc.
    a. If VALIDATE(RUN) is used, message DSNX100I is issued to
       indicate not all objects or privileges exist at bind time
       but the bind succeeds.
    b. If SQLERROR(CONTINUE) is used, message DSNX200I is issued
       to indicate the error at bind time but the bind succeeds.
    c. The package references a built-in function (BIF) that is
       supported in a higher release than the release dependency
       marking of the package, SYSPACKAGE.IBMREQD. For example,
       the release dependency marking of the package is V8,
       SYSPACKAGE.IBMREQD = 'L', but a TRIM('A   ')  BIF is
       referenced in the package which is supported in V10 and
       higher.
  2. The missing object is defined or the missing privilege is
  granted,or the SQL error is corrected.
  3. The subsequent REBIND PACKAGE command without changing the
  APPLCOMPAT bind option value is issued to rebind the package
  or another BIND PACKAGE command is issued to bind the package
  again (in the case of SQLERROR(CONTINUE)), but the command
  receives the 04E abend or the incorrect SQLCODE -440.
  Here is an example of the sequence events that can lead to
  the 04E abend:
  1. BIND PACKAGE(PKHVAR) MEMBER(HOSTVAR) APPLCOMPAT(V12R1M500)
     VALIDATE(RUN)
  BIND a PL/1 application using VALIDATE(RUN) bind option.
  The application contains the following SQL statement and
  table T10 hasn't been created:
  EXEC SQL SELECT C1 INTO :OHV1 FROM T10
             WHERE C2 = C1 AND C3 = TRIM('3   ');
  ==> DSNX100I  -DB2A BIND SQL WARNING
             USING SYSADM AUTHORITY
             PLAN=(NOT APPLICABLE)
             DBRM=HOSTVAR
             STATEMENT=72
             SYSADM.T10 IS NOT DEFINED
  ==> BIND succeeds, the release dependency marking in
  SYSIBM.SYSPACKAGE shows SYSPACKAGE.IBMREQD = 'L' but BIF
  TRIM('3   ') is supported in release 'O'and higher only.
  2. T10 is created
  CREATE TABLE T10(C1 INT, C2 INT, C3 VARCHAR(2));
  3. REBIND PACKAGE(PKHVAR.HOSTVAR.(VERSION1)) VALIDATE(RUN)
  ==> receives ABND0E4 DSNXGRDS.DSNXEBPG OFFSET M113
  
  Db2 does not resolve BIF TRIM('3   ') properly and abends
  with the 04E abend.
  

Problem conclusion

• Db2 has been modified to ensure the above scenario does not
  cause an abend or an incorrect SQLCODE.
  Additional keywords:  SQLREBIND SQLBIND SQLVALIDATERUN
                        SQLERROR ABEND04E SQLCODE440
  

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:

  UI68165

Modules/Macros

• DSNXORFN
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI68165

     UP20/03/11 P F003

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.