A fix is available
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 number
PH23594
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
C10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2020-03-24
Closed date
2020-06-10
Last modified date
2020-07-06
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
07 July 2020