PI26285: INCORROUT WHEN USING PREPARED STATEMENT WITH IN-LIST WITH 19 OR MORE PARAMETER MARKERS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrout when using prepared statement with in-list with 19 or
  more parameter markers
  The problem will occur when in list is treated as a join in the
  access path and statement is executed one time with all
  parameter markers and subsequent time with at least 18 nulls and
  1 value
  

Local fix

• Force access path not to be join for in list, or reduce the
  parameter markers in the in list
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS user's of     *
  *                 queries that include a where predicate       *
  *                 with an indexable IN-List (containing        *
  *                 many NULL entries/elements).                 *
  ****************************************************************
  * PROBLEM DESCRIPTION: An incorrect result of more rows than   *
  *                      expected can be returned from a query   *
  *                      that includes a where predicate with an *
  *                      indexable IN-List (containing many null *
  *                      entries/elements) if it is transformed  *
  *                      to use IN-List table access.            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An incorrect result (more rows than expected) can be returned
  from a query that includes a where predicate with an indexable
  IN-List (containing many NULL elements) if it is transformed to
  an "IN-List table".  The PLAN_TABLE shows ACCESSTYPE = 'IN'.
  
  PLEASE NOTE:  The TOP_ID column used to reference the IN-List in
  the following example is not NULL and is necessary for this
  problem.
  
  The following dynamic SQL, fashioned from the reported case,
  presents such a failing case.
  
  
  Given the following TABLE and chosen INDEX definition.
  
  STEP 1. Create a table T1 and index IDX1.
  
    CREATE TABLE ABC.T1
     (PEZ_ID            TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL,
      PFD_ZP            TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL
        WITH DEFAULT '9999-12-31-00.00.00.000000',
      BEG_DAT           DATE NOT NULL,
      END_DAT           DATE NOT NULL WITH DEFAULT '9999-12-31',
      ERS_ZP            TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL
        WITH DEFAULT,
      DAT_ID            CHAR(1) FOR SBCS DATA NOT NULL
        WITH DEFAULT 'J',
      PRODUCT_ID        TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL,
      TOP_ID            CHAR(10) NOT NULL,
      CONSTRAINT PEZ_ID
      PRIMARY KEY (PEZ_ID,
                   PFD_ZP,
                   BEG_DAT))
  ....
  
    CREATE INDEX IDX1
      ON ABC.T1
       (TOP_ID           ASC,
        PFD_ZP           DESC,
        BEG_DAT          DESC)
  ....
  
  
  STEP 2. Insert a row of data into the table.
  
    INSERT INTO ABC.T1   VALUES
    ('1999-10-31-00.00.00.000000','1991-10-31-00.00.00.000000',
    '2014-10-01','2014-10-31','2014-12-31-00.00.00.000000','J',
    '2000-12-31-00.00.00.000000',x'00000000000000000000');
  
  
  STEP 3. Execute the following dynamic SQL.
  
  Execute the following dynamic SQL with 24 parameter markers,
  4 which are outside the IN-List, and 20 inside the IN-List.
  
  Assign and set values for the parameter markers, i.e. host
  variable and indicator variable assignments.
  
    HV1: '1990-10-31-00.00.00.000000', I1=0
    HV2: '2014-12-31-00.00.00.000000', I2=0
    HV3: '2014-12-31', I3=0
    HV4: '2012-12-31', I4=0
  
  IN-List HV's.
  
    HV5: '', I5=-1  ...  HV24: '', I24=-1
  
  
     SELECT DISTINCT T1.PEZ_ID,
                    HEX(T1.TOP_ID),
                    T1.BEG_DAT, T1.END_DAT,
                    T1.ERS_ZP, T1.PFD_ZP,
                    T1.DAT_ID, T1.PRODUCT_ID
     FROM  ABC.T1 AS T1
     WHERE ((T1.PFD_ZP > ?
         AND T1.ERS_ZP <= ? )
         AND (T1.BEG_DAT <= ?
         AND T1.END_DAT >= ? )
         AND T1.DAT_ID = 'J')
         AND T1.TOP_ID IN (?,?,?,?,?,?,?,?,?,?,
                           ?,?,?,?,?,?,?,?,?,?) ORDER BY 1;
  
  STEP 4. Check the results.
  
  A single row is returned but no rows are expected to be
  returned. So, "more rows than expected" are returned.
  
  
  Please Note:
  In some applications, if the query/cursor is executed multiple
  times but with different host variable assignments, the first
  iteration can accidentally work whereas the second iteration may
  produce an unexpected result as in the reported case.  So, the
  problem is dependent on the access path chosen and also could
  depend on residual values in memory from the first execution.
  

Problem conclusion

• The code in DB2 (bindtime) has been modified to correct the
  logic for processing parameter markers with negative indicators
  for IN-List table access to prevent incorrect results from being
  returned during execution.
  
  Additional Keywords: SQLINCORROUT INCORROUT SQLINCORR
                       DB2INCORR/K SQLINLIST
  

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:

  UI22950 UI22951

Modules/Macros

•    DSNXGRM1
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI22950

     UP14/11/27 P F411 Ž

• RB10 PSY UI22951

     UP14/11/27 P F411 Ž

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.