PQ86096: ISSUANCE OF +354 SQLCODE FOR ROWSET CURSORS.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A +354 SQLCODE may be issued for rowset cursors during a
  multiple row fetch request.  If the fetch receives bind out
  errors via DSNXROHx, rows will continue to be fetched and a +354
  sqlcode will be issued to warn the user about the errors.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 R810 Users of rowset cursors.        *
  ****************************************************************
  * PROBLEM DESCRIPTION: A top level SQLCODE354 (SQLCODE +354)   *
  *                      will be issued to indicate that a       *
  *                      rowset fetch statement has returned one *
  *                      or more rows of data with one or more   *
  *                      bind out processing warning or error    *
  *                      conditions.                             *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 will continue multi-row fetch processing on errors or
  warnings and issue a top level SQLCODE of +354.
  
  This apar will also make the following DB2 documentation
  changes.
  
  
  The DB2 v8 SQL Reference will be modified to reflect the
  following changes.
  
  FETCH Statement
  
  Examples of Fetching Multiple Rows with a Single FETCH Statement
  
  Given the cursor C1 defined as:
  
  DECLARE C1 CURSOR WITH ROWSET POSITIONING FOR SELECT * FROM EMP;
  
  Consider the following examples, where we attempt to fetch 10
  rows with a single FETCH statement.
  
  Example 1: Assume that an error is detected on the 5th row.
  Also, assume that the remaining rows are fetched correctly.
  SQLERRD3 is set to 9 for the 9 returned rows, SQLSTATE is set
  to 01668, and SQLCODE is set to +354. This information is also
  available from the GET DIAGNOSTICS statement (the information
  that is returned is generated from connected server, which may
  differ across different servers). For example:
  
       GET DIAGNOSTICS :num_rows = ROW_COUNT, :num_cond = NUMBER;
       -- Results of the statement:
       -- num_rows =  9 and num_cond =  2 (2 conditions)
  
       GET DIAGNOSTICS CONDITION 1 :sqlstate = RETURNED_SQLSTATE,
       :sqlcode = DB2_RETURNED_SQLCODE, :row_num = DB2_ROW_NUMBER;
       -- Results of the statement:
       -- sqlstate = 01668, sqlcode = +354, and row_num = 0
  
       GET DIAGNOSTICS CONDITION 2 :sqlstate = RETURNED_SQLSTATE,
       :sqlcode = DB2_RETURNED_SQLCODE, :row_num = DB2_ROW_NUMBER;
       -- Results of the statement:
       -- sqlstate = 22003, sqlcode = -802, and row_num = 5
  
  Example 2: Assume that an end of data condition is detected on
  the 6th row, and that the cursor does not have immediate
  sensitivity to updates. SQLERRD3 is set to 5 for the 5 returned
  rows, SQLSTATE is set to 02000, and the SQLCODE is set to +100.
  This information is also available from the GET DIAGNOSTICS
  STATEMENT. For example
  
       GET DIAGNOSTICS :num_rows = ROW_COUNT, :num_cond = NUMBER;
       -- Results of the statement:
       -- num_rows = 5 and num_cond = 1 (1 condition)
  
       GET DIAGNOSTICS CONDITION 1 :sqlstate = RETURNED_SQLSTATE,
       :sqlcode = DB2_RETURNED_SQLCODE, :row_num = DB2_ROW_NUMBER;
       DB2_RETURNED_SQLCODE, :row_num = DB2_ROW_NUMBER;
       -- Results of the statement:
       -- sqlstate = 02000, sqlcode = 100, and row_num = 0.
  
  Example 3: Assume that indicators have been provided for values
  returned for column 1, but not for column 2. The 5th row has a
  data mapping error (+802) for column 1, and the 7th row has a
  data mapping error for column 2 (-802 is returned because an
  indicator was not provided for column 2). SQLERRD3 is set to 10
  for the 10 returned rows, SQLSTATE = 01668 and SQLCODE = +354.
  The indicator variable for the 5th row column 1 indicates that
  a data mapping error was found. This information is also
  available from the GET DIAGNOSTICS statement, for example:
  
       GET DIAGNOSTICS :num_rows = ROW_COUNT, :num_cond = NUMBER;
       -- Results of the statement:
       -- num_rows =  10 and num_cond = 3 (3 conditions)
  
       GET DIAGNOSTICS CONDITION 1 :sqlstate = RETURNED_SQLSTATE,
       :sqlcode = DB2_RETURNED_SQLCODE, :row_num = DB2_ROW_NUMBER;
       -- Results of the statement:
       -- sqlstate = 01668, sqlcode = +354, and row_num = 0
  
       GET DIAGNOSTICS CONDITION 2 :sqlstate = RETURNED_SQLSTATE,
       :sqlcode = DB2_RETURNED_SQLCODE, :row_num = DB2_ROW_NUMBER;
       -- Results of the statement:
       -- sqlstate = 22003, sqlcode = -802, and row_num = 7
  
       GET DIAGNOSTICS CONDITION 3 :sqlstate = RETURNED_SQLSTATE,
       :sqlcode = DB2_RETURNED_SQLCODE, :row_num = DB2_ROW_NUMBER;
       -- Results of the statement:
       -- sqlstate = 01519, sqlcode = +802, and row_num = 5
  
  Example 4: Assume that null indicators are provided, that rows 3
  and 5 are holes, and that data exists for the other requested
  rows. SQLERRD3 is set to 10 to reflect that 10 fetches were
  completed and that information has been returned for the 10
  requested rows. Eight rows actually contain data. For two rows,
  indicator variables are set to indicate no data was returned for
  those rows. SQLSTATE is set to 01668, SQLCODE is set to +354,
  and all null indicators for rows 3 and 5 are set to -3 to
  indicate that a hole was detected. This information is also
  available from the GET DIAGNOSTICS statement, for example:
  
       GET DIAGNOSTICS :num_rows = ROW_COUNT, :num_cond = NUMBER;
       -- Results of the statement:
       -- num_rows = 10 and num_cond = 3 (3 conditions)
  
       GET DIAGNOSTICS CONDITION 1 :sqlstate = RETURNED_SQLSTATE,
       :sqlcode = DB2_RETURNED_SQLCODE, :row_num = DB2_ROW_NUMBER;
       -- Results of the statement:
       -- sqlstate = 01668, sqlcode = +354, and row_num = 0
  
       GET DIAGNOSTICS CONDITION 2 :sqlstate = RETURNED_SQLSTATE,
       :sqlcode = DB2_RETURNED_SQLCODE, :row_num = DB2_ROW_NUMBER;
       -- Results of the statement:
       -- sqlstate = 02502, sqlcode = +222, and row_num = 5
  
       GET DIAGNOSTICS CONDITION 3 :sqlstate = RETURNED_SQLSTATE,
       :sqlcode = DB2_RETURNED_SQLCODE, :row_num = DB2_ROW_NUMBER;
       -- Results of the statement:
       -- sqlstate = 02502, sqlcode = +222, and row_num = 3
  
  Providing indicator variable for error condition:
  
  If an error occurs as the result of an arithmetic expression in
  the SELECT list of an outer SELECT statement (division by zero
  or overflow) or a numeric conversion error occurs, the result
  is the null value. As in any other case of a null value, an
  indicator variable must be provided and the main variable is
  unchanged. In this case however, the indicator variable is set
  to -2. Processing of the statement continues as if the error had
  not occurred. (However, this error causes a positive SQLCODE).
  If you do not provide an indicator variable, a negative
  SQLCODE is returned for that row. Processing of the statement
  continues when this type of error is encountered. No value is
  assigned to the host variable or to later variables for that
  row, though any values that have already been assigned to
  variables remain assigned.  A positive SQL code (SQLSTATE 01668,
  SQLCODE +354) will be returned for the statement in this case.
  
  Additionally, a -3 is returned in all indicators provided by the
  application, when a hole was detected for the row on a rowset
  positioned FETCH, and values were not returned for the row.
  Processing of the statement continues after an SQLCODE -247 and
  SQLSTATE 24519 is issued when a hole is detected and at least
  one indicator variable was not provided by the application.
  
  
  MESSAGES AND CODES
   New SQL Codes
  
  
    +354                      A ROWSET FETCH STATEMENT
                              MAY HAVE RETURNED ONE OR
                              MORE ROWS OF DATA. HOWEVER,
                              ONE OR MORE WARNING OR ERROR
                              CONDITIONS WERE ALSO ENCOUNTERED.
                              USE THE GET DIAGNOSTICS STATEMENT
                              FOR MORE INFORMATION REGARDING
                              THE ERROR AND WARNING CONDITIONS
                              THAT WERE ENCOUNTERED
  
    Explanation: A rowset FETCH statement encountered one
    or more warning or error conditions.
    Use the GET DIAGNOSTICS statement to obtain information
    about the conditions that occurred, and whether data was
    returned. In cases where the row information returned
    for the row that encountered the condition is incomplete,
    use of the data for the row that encountered the condition,
    such as displaying or printing the data, is not recommended.
  
    System Action: DB2 processed the statement successfully, but
    with a warning that some rows may have encountered warnings
    or errors.
  
    Programmer Response: Analyze the conditions to determine
    if all the requested rows were fetched, or if a partial
    rowset was returned.
  
    Destination: 01668
  
    Some other problems were also fixed in the process of testing
    this APAR. They are:
  
    1. An SQLCODE -304 will be correctly issued instead of a +304
       when indicator variables are not associated with the
       output host variable entry that encounters the error.
  
    2. The message parameters for SQLCODES -331 and +331 were
       modified to be set correctly.
  
    3. Missing information on a GET DIAGNOSTICS in a distributed
       environment was fixed.
  
    4. Missing module name for a SQLCODE +100 for both local and
       distributed environments was fixed.
  
    5. Two +100 sqlcode conditions issued incorrectly instead of
       the expected one condition on a rowset fetch.
  

Problem conclusion

• The code was modified to continue fetching when an error or
  warning on a rowset fetch is encountered and issue a top
  level sqlcode.
  
  Additional Keywords:
  SQLCODE354 SQLMULTIROW SQLFETCH SQLERROR
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UK07280

Modules/Macros

•    DSNDDDXB DSNDF029 DSNDSQLD DSNLXMRF DSNLZDTA
  DSNXBTBL DSNXCTMD DSNXEADW DSNXEBR  DSNXEFDA DSNXEMDA DSNXERD
  DSNXESQL DSNXREOJ DSNXRFF  DSNXRFN  DSNXRGD  DSNXROHB DSNXROHR
  DSNXROHS DSNXROHV DSNXROJ1 DSNXROU  DSNXROUA DSNXRRPJ DSNXRSCR
  DSNXRSFN DSNXRSGB DSNXRT   DSNXRT1J DSNXSRC
  

Fix information

• Fixed component name

  5740 IBM DATABA

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK07280

     UP05/10/01 P F509 Ž

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.