Fixes are available
DB2 Version 9.1 Fix Pack 7 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12 for Linux, UNIX and Windows
APAR status
Closed as program error.
Error description
This problem could occur when all the following conditions are met: . 1) The SQL statement has an OR predicate with subquery of the form . <col-1> IN (select <col-sq> from....) OR <other-subterm> . -OR- <col-1> NOT IN (select <col-sq> from....) OR <other-subterm> 2) The statement also has an OR predicate with subquery of the form <col-2> IN (select <col-sq> from....) OR <other-subterm> -OR- <col-2> NOT IN (select <col-sq> from....) OR <other-subterm> Note that the OR predicate in 1) and 2) could be the same OR predicate or two different OR predicates 3) The <col-sq> in 1) and 2) must be the same value of a constant, a host variable, a parameter marker, or a special register such as CURRENT DATE. In addition, <col-sq> could be a normal column in the user written SQL statement but is substituted by DB2 Query Rewrite to one of those aforementioned types, which could be observed in the Optimized Statement of an output of db2exfmt. 4) There are join predicates in other part of the same SQL statement such that DB2 Optimizer could derive the following relationship <col-1> = <col-2> = <col-3> An example of such join predicates is <col-1> = <col-3> AND <col-2> = <col-3> 5) When all the above conditions are met, DB2 incorrectly derives that <col-1> = <col-2> = <col-3> = <col-sq> In some certain choices of access plan where DB2 Optimizer chooses to apply the two join predicates <col-1> = <col-3> and <col-2> = <col-3> in the same join operator, the Optimizer may not apply one of the join predicates in 4). As a result, DB2 will produce possibly more rows in the result set.
Local fix
Problem summary
This problem could occur when all the following conditions are met: . 1) The SQL statement has an OR predicate with subquery of the form <col-1> IN (select <col-sq> from....) OR <other-subterm> . -OR- <col-1> NOT IN (select <col-sq> from....) OR <other-subterm> 2) The statement also has an OR predicate with subquery of the form <col-2> IN (select <col-sq> from....) OR <other-subterm> -OR- <col-2> NOT IN (select <col-sq> from....) OR <other-subterm> Note that the OR predicate in 1) and 2) could be the same OR predicate or two different OR predicates 3) The <col-sq> in 1) and 2) must be the same value of a constant, a host variable, a parameter marker, or a special register such as CURRENT DATE. In addition, <col-sq> could be a normal column in the user written SQL statement but is substituted by DB2 Query Rewrite to one of those aforementioned types, which could be observed in the Optimized Statement of an output of db2exfmt. 4) There are join predicates in other part of the same SQL statement such that DB2 Optimizer could derive the following relationship <col-1> = <col-2> = <col-3> An example of such join predicates is <col-1> = <col-3> AND <col-2> = <col-3> 5) When all the above conditions are met, DB2 incorrectly derives that <col-1> = <col-2> = <col-3> = <col-sq> In some certain choices of access plan where DB2 Optimizer chooses to apply the two join predicates <col-1> = <col-3> and <col-2> = <col-3> in the same join operator, the Optimizer may not apply one of the join predicates in 4). As a result, DB2 will produce possibly more rows in the result set.
Problem conclusion
apar: iz40625 module: engn_sqnr fixed >= v91 fpk8
Temporary fix
Comments
APAR Information
APAR number
IZ40625
Reported component name
DB2 EDE AIX
Reported component ID
5724N7600
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2008-12-22
Closed date
2009-12-03
Last modified date
2009-12-03
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Modules/Macros
ENG_SQNR
Fix information
Fixed component name
DB2 EDE AIX
Fixed component ID
5724N7600
Applicable component levels
R910 PSN
UP
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910"}]
Document Information
Modified date:
03 October 2021