Fixes are available
APAR status
Closed as program error.
Error description
Optimizer may choose a non-optimal plan for queries using FETCH FIRST ROWS ONLY (FFNR) caluse, which can lead to performance degradation. This plan causes a large stream of data to be broadcasted across partitions. Users will find this evident in the query access plan, which will show the BTQ operation broadcasting large stream of data as following: /-------- 1.07181e+07 BTQ ( 13) 125531 31015.2 | 2.67952e+06 IXSCAN ( 14) 123504 31015.2 | 2.67952e+06 INDEX: <name of the index> In this example, aprox 1.07x10^7 rows are being broadcasted across partitions. This leads to poor performance.
Local fix
Problem summary
USERS AFFECTED: =============== Customer using version 9.5 and above on all platforms. PROBLEM DESCRIPTION: ==================== Queries using FIRST FIRST ROWS ONLY (FFNR) clause may experience performance problem after migrating from version 8 to version 9. The APAR can be confirmed by checking the query access graph. If a BTQ operation is seen broadcasting large stream of data, it may point to this APAR. RECOMMENDATION: =============== There is currently no workaround other than not using the FFNR clause.
Problem conclusion
Problem is first fixed in version 9.5 Fixpack 8.
Temporary fix
There is currently no workaround other than not using the FFNR clause.
Comments
APAR Information
APAR number
IZ85082
Reported component name
DB2 UDB ESE SOL
Reported component ID
5765F4102
Reported release
950
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2010-09-16
Closed date
2011-07-21
Last modified date
2011-07-21
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 UDB ESE SOL
Fixed component ID
5765F4102
Applicable component levels
R950 PSY
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":"950"}]
Document Information
Modified date:
07 October 2021