A fix is available
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 number
PI26285
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
A10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2014-09-24
Closed date
2014-11-11
Last modified date
2014-12-01
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
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"}],"Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
06 May 2020