PL48514: BIND WITH EMBEDDED GROUP BY DOES NOT RETURN SQLCODE815

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A BIND WITH THE FOLLOWING STATEMENT:
  BIND ACTION(REPLACE) RETAIN VALIDATE(BIND) ISOLATION(RR)
       FLAG(I) ACQUIRE(USE) RELEASE(COMMIT) EXPLAIN(YES)
  ON A PROGRAM WITH AN EMBEDDED SELECT WITH ONE TABLE, ONE
  WHERE PREDICATE AND A GROUP BY STATEMENT RETURNS MSGDSNT200I
  INSTEAD OF AN SQLCODE815 OR OTHER ERROR MESSAGE.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: ALL                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION: MSGDSNT200I - BIND FOR PLAN SUCCESSFUL  *
  *                      WHEN AN EMBEDDED SELECT STATEMENT       *
  *                      CONTAINS A GROUP BY CLAUSE. SQLCODE815  *
  *                      -815 SHOULD BE ISSUED BECAUSE A GROUP   *
  *                      BY CLAUSE IS NOT PERMITTED IN AN        *
  *                      EMBEDDED SELECT.                        *
  *                      NRF SQLCODE100 DSNXRSGB RETURNED IF SQL *
  *                      IS EXECUTED (INCORROUT)                 *
  ****************************************************************
  * RECOMMENDATION: APPLY PTF WHEN AVAILABLE.                    *
  ****************************************************************
  THE CUSTOMER HAD THE FOLLOWING SELECT EMBEDDED IN A COBOL
  APPLICATION:
     SELECT C1 INTO :V1 FROM T1 GROUP BY C1
  THIS IS AN INVALID SELECT INTO STATEMENT. PAGE 214 OF THE R210
  DB2 SQL REFERENCE ( SC264380-0 ) SHOWS THE CORRECT SYNTAX OF THE
  SELECT INTO STATEMENT. THE GROUP BY CLAUSE IS NOT ALLOWED.
  R302 CUSTOMERS SHOULD REFERENCE PAGE 203 OF THE DB2 SQL
  REFERENCE ( SC264346-0 ) UNDER THE HEADING "EMBEDDED SELECT".
  THE CUSTOMER ISSUED THE FOLLOWING BIND COMMAND FOR THE PROGRAM
  THAT CONTAINS THE EMBEDDED SELECT STATEMENT SHOWN ABOVE:
     BIND PLAN(XXXX) ACTION (REPLACE) ...... EXPLAIN (YES)
  THE BIND COMMAND RECEIVED MSGDSNT200I - BIND SUCCESSFUL.
  SQLCODE -815 SHOULD HAVE BEEN ISSUED IN THIS CASE SINCE THE
  GROUP BY CLAUSE IS NOT ALLOWED IN THE EMBEDDED SELECT.
  CODE IN DSNXOGB WAS CHANGED BY APAR PP56194 TO NOT ISSUE
  SQLCODE -815 WHEN BINDING A DYNAMIC SQL EXPLAIN STATEMENT.
  HOWEVER, SQLCODE -815 SHOULD BE ISSUED FOR A STATIC BIND WITH
  THE EXPLAIN(YES) OPTION IN EFFECT.
    ADDITIONAL SEARCH ARGUMENT: SQLGROUPBY
  

Problem conclusion

• CODE HAS BEEN ADDED TO DSNXOGB TO DISTINGUISH BETWEEN STATIC
  BINDING WITH THE EXPLAIN (YES) OPTION IN EFFECT AND BINDING
  A DYNAMIC SQL EXPLAIN STATEMENT.
  SQLCODE -815 WILL BE ISSUED WHEN AN EMBEDDED SELECT STATEMENT
  CONTAINS A GROUP BY CLAUSE AND A STATIC BIND WITH THE EXPLAIN
  (YES) OPTION IS BEING PERFORMED.
  THIS IS A FORWARD FIT OF APAR PL46447 ( PTM KZF4041 ).
  

Temporary fix

Comments

APAR Information

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

  PL46447

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

  UL58409

Modules/Macros

•    DSNXOGB
  

Fix information

• Fixed component name

  5740 IBM DATABA

• Fixed component ID

  5740XYR00

Applicable component levels

• R220 PSY UL58409

     UP89/09/12 P F907

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.