PH23594: INCORROUT FOR QUERY WITH INLIST (IN-LIST) ACCESS PATH (LESS ROWS THAN EXPECTED)

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• INCORROUT for Query with INLIST ( IN-List ) access path
  (less rows than expected)
  An incorrect result can occur for a Query using
   INLIST Access Path ( ACCESSTYPE = IN in Plan_Table) when
  using vargraphic values exceeding a certain length.
  In this case, a length of over 255 bytes was observed in
  internal control blocks.
  Further, internal testing did show that this issue came up while
   using at least 10 or more IN-List values.
  However, it has been observed also that a IN-LIST with e.g. 62
   or 126 values returned 62 or 126 rows respectively in its
  result set, while using 63 values returned less rows than
  expected.
  In this particular example, the incorrout condition comes up for
   a query like this:
  SELECT * FROM T1
   WHERE Colx IN
    ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
    FOR READ ONLY WITH UR ;
  Colx is defined as VARGRAPHIC(250) NOT NULL WITH DEFAULT and
   Table T1 is defined with CCSID Unicode.
  Index: CREATE UNIQUE INDEX XT1 ON T1 ( Colx ASC )
  ----
  The problem can also occur when Colx is defined as
  VARCHAR(300).
  
  ADDITIONAL SYMPTOMS:
  INCORROUT SQLINCORR SQLINCORROUT SQLIN
  

Local fix

• BYPASS/CIRCUMVENTION:
  Avoid IN-List Access Path
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users of queries that                    *
  * contain an IN LIST on a varying data column                  *
  * with a length exceeding 255 bytes and using                  *
  * IN LIST access.                                              *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * An incorrect result of 'less rows than                       *
  * expected' can be returned for a query                        *
  * containing an IN LIST on a varying                           *
  * data column with a length exceeding                          *
  * 255 bytes when using an IN LIST                              *
  * access type.                                                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  An incorrect result of 'less rows than expected' can be
  returned for a query containing an IN LIST on a VARGRAPHIC(250)
  column if the chosen access path is INLIST (ACCESS TYPE = IN
  in Plan_Table for EXPLAIN). The problem occurs because an
  incorrect data move is performed internally for the VARGRAPHIC
  column using an invalid length. The problem can also occur when
  using a VARCHAR column that can have a data length exceeding
  255 bytes, for example VARCHAR(300).
  
  The following example helps to illustrate a failing case. This
  example is chosen because it exhibits that IN LIST access is
  chosen by Db2, and that long VARGRAPHIC strings are vulnerable.
  Step 1. Create a Table T1.
     CREATE TABLE T1
     (C1 VARGRAPHIC(250) NOT NULL WITH DEFAULT ' ',
      C2 INTEGER NOT NULL WITH DEFAULT 0,
      PRIMARY KEY (C1) )
     IN DB1.TS1
     CCSID    UNICODE ;
  
  Step 2. Create an ascending index IX1 for IN LIST access.
  CREATE  UNIQUE  INDEX IX1
    ON  T1 (C1 ASC)
        CLUSTER
        NOT PADDED
        USING STOGROUP SYSDEFLT  PRIQTY 4 SECQTY -1
        ERASE NO
        FREEPAGE 0 PCTFREE 10
        GBPCACHE CHANGED
        PIECESIZE        2 G
        DEFINE  NO
        COMPRESS NO
        BUFFERPOOL BP3 CLOSE YES;
  Step 3. Insert values into Table T1. For this example we will
  insert 12 rows. The rows contain strings for C1 having lengths
  of 105, 105, 112, 112, 110, 112, 108, 109, 110, 111, 110 and
  188 characters. The values for C2 will be 100,200,...,1200.
  Note: The value of C1 for row 12 is vulnerable because it shows
  that 2 x length exceeds 255 for this row (2 x 188 > 255).
  NOTE: In this example, 12 rows are used because when using less
  rows, IN LIST access is not chosen.
  
  Step 4. Populate 12 host variables with the same C1 values from
  the insert.
  Step 5. Run the following query.
  SELECT  CAST ( C1 AS VARCHAR(250) FOR SBCS DATA ), C2
                  FROM T1
    WHERE C1 IN ( ?,?,?,?,?,?,?,?,?,?,?,? )
    WITH UR ;
  Step 6. Check the results.
  The correct (and expected results) is 12 rows returned.
  The incorrect result for this example is 11 rows returned.
  Note: The 12th row identified above will not be returned but
  should be.
  An EXPLAIN can be performed for the query to see that IN LIST
  access (ACCESSTYPE = IN in Plan Table).
  The problem occurs because an incorrect internal data move is
  used which is sensitive to a maximum length of 255. So, for
  both column data types (VARCHAR or VARGRAPHIC), if the length
  of the data exceeds 255 bytes, the internal move of the data
  will be incomplete leading to a possible incorrect query result
  of "less rows than expected" being returned.
  
  Please note: The same problem can easily be produced by using a
  VARCHAR(300) column C1 instead that includes data values with
  lengths less than 255 for the first 11 rows, and a data value
  for the last row having a length exceeding 255.
  

Problem conclusion

• The code in Db2 is modified to use an enhanced data move that
  supports larger (more than 255 bytes) varying data lengths.
  Additional Keywords: SQLINLIST INCORROUT SQLINCORROUT
  SQLVARCHAR SQLVARGRAPHIC DB2INCORR/K SQLINCORR
  

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:

  UI69974

Modules/Macros

• DSNXROJ1 DSNXRFF  DSNXRSGB DSNXRT1J DSNXREOJ DSNXRFN  DSNXRSFN
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI69974

     UP20/06/18 P F006 ¢

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.