A fix is available
APAR status
Closed as program error.
Error description
An SQL might produce incorrect results if all the following conditions are met: (a) The statement leverages a hash join (HSJOIN). (b) The plan has 2 nested loop joins (NLJOIN); one on the inner of another, and the other on the probe side of the HSJOIN. (c) An internal optimization on the probe side of the HSJN called a "push down" is implemented. The DB2 trace example below shows sqlrihsjnProbePD, which identifies this optimization taking place. We can use the EXPLAIN statement to see if the given SQL satisfies the conditions (a) and (b). The plan would have a graph like as below: HSJOIN / \ NLJOIN (x) / \ NLJOIN (y) Please note that this issue is likely to occur if NLJN(x) outer is not a real table, but a TABFNC: SYSIBM.GENROW Also, we can take db2trc to see if the HSJOIN is running into the condition (c), like as below: 1. Turn on the trace. db2trc on -f trc.dmp 2. Run the query. 3. Turn off the trace, then format it. db2trc off db2trc flw trc.dmp trc.flw 4. Search one sqlrihsjnProbePD that immediately returns with rc=1 inside sqlriFetch. grep -e sqlrihsjnProbePD -e sqlriFetch trc.flw 59255 | | | sqlriFetch entry 59417 | | | | | | | | | | | sqlrihsjnProbePD entry 59420 | | | | | | | | | | | sqlrihsjnProbePD exit [rc = 1] 50526 | | | sqlriFetch exit Note that the result might vary depending on the order and volume of the data.
Local fix
Users can disable all the push down by using the following setting. db2set DB2_TCG_DEFAULT_OPTIONS="set disable_pushdown on" Note that disabling push down can impact on query performance.
Problem summary
**************************************************************** * USERS AFFECTED: * * All users * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 version 10.1.0.5. * ****************************************************************
Problem conclusion
The problem is first fixed in DB2 version 10.1.0.5.
Temporary fix
Comments
APAR Information
APAR number
IT04520
Reported component name
DB2 FOR LUW
Reported component ID
DB2FORLUW
Reported release
A10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt
Submitted date
2014-09-21
Closed date
2015-07-10
Last modified date
2015-07-10
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
DB2 FOR LUW
Fixed component ID
DB2FORLUW
Applicable component levels
RA10 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":"10.1"}]
Document Information
Modified date:
25 September 2021