A fix is available
APAR status
Closed as program error.
Error description
Incorrout issues occurs with a DELETE statement when the DELETE has a NOT IN subquery and the subquery has nested NOT IN subquery too SAMPLE: DDL: CREATE TABLE REMARKS (ID INTEGER NOT NULL, REPID INTEGER NOT NULL); CREATE TABLE REPORT (ID INTEGER NOT NULL, MSGID INTEGER); CREATE TABLE MESSAGE (ID INTEGER NOT NULL); INSERT INTO REMARKS VALUES (2, 2); INSERT INTO REPORT VALUES (2, NULL); INSERT INTO MESSAGE VALUE(5); COMMIT: SQL: DELETE FROM REMARKS WHERE REPID NOT IN (SELECT ID FROM REPORT WHERE MSGID NOT IN (SELECT ID FROM MESSAGE));
Local fix
N/A
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 11 for z/OS users of sparse index * * and IN predicates * **************************************************************** * PROBLEM DESCRIPTION: DB2 may incorrectly return a row when * * comparing the null value to the result * * of an IN subquery. The problem occurs * * when a sparse index is used for the * * subquery. * **************************************************************** * RECOMMENDATION: * **************************************************************** DB2 may incorrectly return a row when comparing the null value to the result of an IN subquery. The problem occurs when a sparse index is used for the subquery. The following is an example of the incorrect output. CREATE TABLE REMARKS (ID INTEGER NOT NULL, REPID INTEGER NOT NULL); CREATE TABLE REPORT (ID INTEGER NOT NULL, MSGID INTEGER); CREATE TABLE MESSAGE (ID INTEGER NOT NULL); CREATE INDEX IX1 ON MESSAGE(ID); INSERT INTO REMARKS VALUES(2,2); INSERT INTO REPORT VALUES(2,NULL); INSERT INTO MESSAGE VALUES(5); DELETE FROM REMARKS WHERE REPID NOT IN (SELECT ID FROM REPORT WHERE MSGID NOT IN (SELECT ID FROM MESSAGE));
Problem conclusion
DB2 has been changed to correctly compare the NULL value to the sparse index. Additional keywords : SQLIN SQLNOTIN SQLSPARSEINDEX SQLINCORR INCORROUT SQLINCORROUT DB2INCORR/K
Temporary fix
********* * HIPER * *********
Comments
APAR Information
APAR number
PI24602
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2014-08-25
Closed date
2014-09-30
Last modified date
2014-11-04
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI21887
Modules/Macros
DSNXRSC2
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RB10 PSY UI21887
UP14/10/16 P F410
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