PM32517: INCORROUT (MORE ROW FOUND) ON QUERY USING FETCH FIRST N ROWS ONLY IN SQL PL

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• IncorrOut (more row found) when running a query containing FETCH
  FIRST N ROWS ONLY clause with Native Stored procedure (SQL PL).
  
  More qualified rows (than the requested FIRST N ROWS) can be
  returned in this case.
  
  Additional Keywords: SQLINCORR SQLINCORROUT SQLPL SQLNATIVESQLPL
  DB2INCORR/K
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9  for z/OS users of native SQL      *
  *                 procedures and FETCH FIRST n ROWS ONLY       *
  ****************************************************************
  * PROBLEM DESCRIPTION: Rows fetched are not limited            *
  *                       for a cursor defined with              *
  *                       FETCH FIRST n ROWS ONLY clause         *
  *                       in a native SQL procedure body, and    *
  *                       the 'n+1' FETCH does not receive       *
  *                       the expected SQLCODE100                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When a cursor is defined with FETCH FIRST n ROWS ONLY
  for a query, DB2 limits the the number of rows that can
  be FETCHed for the query to the 'n' value specified in
  the FETCH FIRST clause. If the number of FETCHes attempted
  exceeds the specified 'n' value, then DB2 issues a SQLCODE
  +100 for that 'n+1' FETCH request.
                                                              .
  However, for a FETCH FIRST n ROWS ONLY defined query in the
  body of a native SQL procedure, DB2 ignored the FETCH FIRST
  n ROWS ONLY clause and did not limit the number of rows that
  can be fetched. As a result, the cursor behaved as a normal
  cursor and the native SQL procedure was able to fetch more
  than the 'n' rows it expected. DB2 did not issue the expected
  SQLCODE100 for the 'n+1' FETCH request.
                                                              .
  If the query returns many rows and the native SQL procedure is
  designed to issue FETCH until SQLCODE +100 is received,
  this could also result in the native SQL procedure
  exceeding its expected running time or appearing to hang for
  some time.
  

Problem conclusion

• DB2 has been changed to correctly handle a FETCH FIRST n ROWS
  ONLY cursor defined in a native SQL procedure body, so that
  the number of rows that can be fetched by the native SQL
  procedure is limited to the 'n' value specified.
  The 'n+1' FETCH will receive SQLCODE +100 .
  
  Additional Keywords:
  SQLPL SQL PL SQLNATIVESQLPL INCORROUT SQLFFNR SQLINCORR
  SQLINCORROUT DB2INCORR/K
  

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:

  UK67262

Modules/Macros

• DSNXERT
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK67262

     UP11/05/17 P F105

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.