A fix is available
APAR status
Closed as program error.
Error description
Less than optimal performance is the result when the optimizer makes a inefficient index selection for the inner table of a nested loop join or hybrid join. This occurs when the inner table index is in the same key order as the outer composite table index. Additional Keywords: SQLPERFORMANCE SQLACCESSPATH SQLNLJ NLJ SQLHYBRID SQLHYBRIDJOIN HYBRIDJOIN SQLHBJ HBJ
Local fix
Optimization Hint
Problem summary
**************************************************************** * USERS AFFECTED: DB2 11 for z/OS users of queries with 2 * * competing indexes for the inner tables. * **************************************************************** * PROBLEM DESCRIPTION: A less efficient index may be selected * * while another index may have a better * * performance for the inner table of a * * join. * **************************************************************** * RECOMMENDATION: * **************************************************************** A less efficient index may be selected while another index may have a better performance for the inner table of a join. This problem could occur when both indexes are very selective and the more efficient index has matching predicates and less screening predicates. For example, SELECT * FROM T1, T2 WHERE T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND T1.C3 = T2.C3 AND T1.C4 = T2.C4; There are 2 indexes defined on T2: IX1 on (C1,C2,C3), IX2 on (C1,C4,C5,C2,C3). If T1 is selected as the leading table and joins to T2 with Nested Loop Join, IX2 may be selected because it has more screening predicates. Additional Keywords: SQLPERFORMANCE SQLACCESSPATH SQLNLJ
Problem conclusion
DB2 has been changed to get a more efficient access path when the query may use Nested Loop Join.
Temporary fix
Comments
APAR Information
APAR number
PI59348
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2016-03-18
Closed date
2016-06-30
Last modified date
2016-08-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI39082
Modules/Macros
DSNXOCSC
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RB10 PSY UI39082
UP16/07/15 P F607
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"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
02 August 2016