PM70173: SQLCODE = 0 VS. SQLCODE = -904 ON 3RD OPEN CURSOR FOR SELECT UNION ALL

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Application resource unavailable retry routine receives
  sqlcode = 0 with no data returned on third OPEN CURSOR.
  for UNION ALL select statement.
  SQLCODE904 is expected (-904 received for first 2 OPENs)
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 users of queries that contain the    *
  *                 UNION or UNION ALL operator with dynamic     *
  *                 statement cache is enabled.                  *
  ****************************************************************
  * PROBLEM DESCRIPTION: When a cursor, containing a query with  *
  *                      a UNION or UNION ALL operator, is       *
  *                      opened for the first and second time,   *
  *                      DB2 returns an SQLCODE -904             *
  *                      (SQLCODE904) as expected for error path *
  *                      testing.  However, when the same cursor *
  *                      is opened a third time, DB2 does not    *
  *                      return an error.  An SQLCODE -904 is    *
  *                      expected. Dynamic statement caching is  *
  *                      enabled.                                *
  *                                                              *
  *                      This could also happen for other SET    *
  *                      operations.                             *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When a cursor, containing a query with a UNION or UNION ALL
  operator, is opened for the first and second time, DB2 returns
  an SQLCODE -904 (SQLCODE904) as expected for error path testing.
  However, when the same cursor is opened a third time, DB2 does
  not return an error SQLCODE -904 to the application as expected
  when dynamic statement caching is enabled.
  
  The following scenario summarizes the error situation.
  
  1, A cursor C1 is declared with a UNION or UNION ALL operator.
  2, Stop the tablespace for the table referenced in C1.
  3, OPEN cursor C1 the first time, DB2 will issue a -904.
  4, OPEN cursor C1 the second time, DB2 will issue a -904.
  5, OPEN cursor C1 a third time, DB2 will return with no SQLCODE.
  
  DB2 does not handle the error path properly for a UNION or UNION
  ALL operator when dynamic statement caching is enabled and the
  same statement is invoked multiple times.
  

Problem conclusion

• The code in DB2 has been modified to process the error path
  properly for a UNION or UNION ALL operator when dynamic
  statement caching is enabled.  This includes issuing an SQLCODE
  as needed.
  
  Additional keywords: SQLUNION SQLUNIONALL SQLDYNSTMTCACHE
  

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:

  UK82006 UK82007

Modules/Macros

• DSNXGOSQ DSNXGRSR DSNXGSOR DSNXGTRE
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK82006

     UP12/10/09 P F210

• R910 PSY UK82007

     UP12/10/09 P F210

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.