A fix is available
APAR status
Closed as program error.
Error description
This apar addresses the problem of sort avoidance being excluded from a query which has the following charcateristics: * the query uses OPTIMIZE FOR 1 ROW and the composite table is very small * the table is in a PBR universal tablespace * the table has a clustered DPSI index defined to it whose column order matches the column order of the ORDER BY statement in the query The query highlites 2 problems: * the reduction logic for OPTIMIZE FOR 1 ROW with a small composite cardinality that existed prior to V11 has been removed and must be reestablsihed * the cost calculation for the DPSI merge is overstimated due to the estimated number of qualified partitions being inaccurate
Local fix
Have customer set OPT1ROWBLOCKSORT=ENABLE in DSNZPARMs
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 11 for z/OS users whose SQL contains * * FETCH FIRST 1 ROW ONLY or OPTIMIZ FOR 1 ROW * * clause. * **************************************************************** * PROBLEM DESCRIPTION: Inefficient access path with sort was * * chosen when the query or query block * * contains FETCH FIRST 1 ROW ONLY * * OPTIMIZE FOR 1 ROW clause. * **************************************************************** * RECOMMENDATION: * **************************************************************** DB2 may choose an inefficient access path with sort for a query or query block with FETCH FIRST 1 ROW ONLY or OPTIMIZE FOR 1 ROW clause. Additional keywords: SQLPERFORMANCE SQLACCESSPATH SQLOF1R SQLFF1R
Problem conclusion
DB2 has been modified to choose an access path without sort if the query or query block contains FETCH FIRST 1 ROW ONLY or OPTIMIZE FOR 1 ROW clause.
Temporary fix
Comments
APAR Information
APAR number
PI22266
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2014-07-18
Closed date
2014-10-28
Last modified date
2014-12-01
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI22577
Modules/Macros
DSNXOGP DSNXOPNR
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RB10 PSY UI22577
UP14/11/13 P F411
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 December 2014