A fix is available
APAR status
Closed as program error.
Error description
A query may be slower when it includes the OPTIMIZE FOR n ROWS clause than when it does not include that clause. This may occur even if the query also includes the FETCH FIRST n ROWS clause, with the value used for n in both clauses being the same. This may occur if index-oring is used in the access plan of the query without the OPTIMIZE FOR n ROWS clause, but index-oring is not used for the query with that clause. This may happen because index-oring is not normally used in the access plan of any query that includes the OPTIMIZE FOR n ROWS clause. You can view the access plan for a query by using a tool such as db2exfmt. If the access plan includes a node labelled RIDSCN dominating more than one branch--each of which includes a node labeled IXSCAN, this shows that the query plan includes index-oring. This APAR addresses the issue by creating a new setting for the DB2_REDUCED_OPTIMIZATION DB2 UDB registry variable, which will prevent the slow performance in this case. It does so by allowing index-oring to be used in the access plan of queries that include the OPTIMIZE FOR n ROWS clause. Instead of the DB2_REDUCED_OPTIMIZATION setting that this APAR introduced, it is recommended to use a new setting DB2_REDUCED_OPTIMIZATION=IXOROFNR that was introduced by APAR IY80102 (which is in DB2 version 8 fixpak 12 and later) to prevent the slow performance in this case. To activate that setting, run db2set DB2_REDUCED_OPTIMIZATION=IXOROFNR and then restart DB2.
Local fix
Omit the OPTIMIZE FOR n ROWS clause.
Problem summary
Users affected: Users of DB2 version 8 on all Linux, UNIX and Windows operating systems. Problem Description: A query may be slower when it includes the OPTIMIZE FOR n ROWS clause than when it does not include that clause. This may occur even if the query also includes the FETCH FIRST n ROWS clause, with the value used for n in both clauses being the same. This may occur if index-oring is used in the access plan of the query without the OPTIMIZE FOR n ROWS clause, but index-oring is not used for the query with that clause. This may happen because index-oring is not normally used in the access plan of any query that includes the OPTIMIZE FOR n ROWS clause. You can view the access plan for a query by using a tool such as db2exfmt. If the access plan includes a node labelled RIDSCN dominating more than one branch--each of which includes a node labeled IXSCAN, this shows that the query plan includes index-oring. This APAR addresses the issue by creating a new setting for the DB2_REDUCED_OPTIMIZATION DB2 UDB registry variable, which will prevent the slow performance in this case. It does so by allowing index-oring to be used in the access plan of queries that include the OPTIMIZE FOR n ROWS clause. To activate this setting, run db2set DB2_REDUCED_OPTIMIZATION=-1,999,999,0000000000101000000000 (all on the same command line) and then restart DB2.
Problem conclusion
DEFECT=366548 CSD>c050816 MODULE=engn_sqno First fixed in DB2 UDB Version 8, FixPak 11
Temporary fix
Omit the OPTIMIZE FOR n ROWS clause.
Comments
APAR Information
APAR number
IY75348
Reported component name
DB2 UDB ESE SOL
Reported component ID
5765F4102
Reported release
810
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2005-08-16
Closed date
2006-02-06
Last modified date
2009-04-03
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
R820 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":"810"}]
Document Information
Modified date:
03 October 2021