A fix is available
APAR status
Closed as program error.
Error description
Nonmatching index scan instead of the matching primary index may be chosen when they having very similar IMFFADJ, CLUSTERRATIO and COMPCOST. And the matching column(s) in the matching index is the supper set of the nonmatching index. Sample query and index: SELECT TEST.COL1 , TEST.COL2 , TEST.COL3 , TEST.COL4 , TEST.COL5 , TEST.COL6 , TEST.COL7 , TEST.COL8 , TEST.COL9 , TEST.COL10 , TEST.COL11 , TEST.COL12 , TEST.COL13 FROM TABLE1 TEST WHERE TEST.COL1 = :WORKTABLE1.COL1 FOR FETCH ONLY WITH UR Matching index: CREATE UNIQUE INDEX "I0"."INDEX0" ON "I0"."TABLE1" ("COL1" ASC <--Matching ,"COL2" ASC) ... Nonmatching index: CREATE INDEX "I0"."INDEX3" ON "I0"."TABLE1" ("COL13" ASC ,"COL12" DESC ,"COL1" ASC <--Screening ,"COL5" ASC)) ...
Local fix
N/A
Problem summary
**************************************************************** * USERS AFFECTED: * * All users of Db2 11 and Db2 12 for z/OS who * * have queries which contain one or more * * predicates. * **************************************************************** * PROBLEM DESCRIPTION: * * DB2 may choose an inefficient index * * access path when the query contains * * one or more predicates. * **************************************************************** * RECOMMENDATION: * * Apply corrective PTF when available * **************************************************************** When a query contains one or more predicates, Db2 may select an inefficient non-matching index when another index can match the predicates. Example: SELECT C1 FROM T WHERE C1=?; Say there are two indexes on T, IX1(C1,C3), IX2(C2,C1). For IX1, the predicate C1=? can be matching. For IX2, the predicate is screening. If both indexes get small cost estimates and the cost of IX2 is just slightly lower than IX1, DB2 may select index access using IX2, even though IX1 provides matching index access.
Problem conclusion
Code was updated to select an efficient index in the situation described above.
Temporary fix
Comments
APAR Information
APAR number
PI92078
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
2018-01-04
Closed date
2018-04-10
Last modified date
2018-05-01
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI55127 UI55128
Modules/Macros
DSNXOCSC
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"},"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:
01 May 2018