APAR status
Closed as program error.
Error description
The type of subquery that may see this problem is a select that should take the same path regardless of whether the select is run as a standalone query or a subquery. The problem is that the query takes a different, much slower path when run as a subquery. This is a regression from 9.40 . The workaround is to use an optimizer directive to force the subquery to take the same path (the same join order) as the path taken when the subquery is run as a standalone query.
Local fix
Use an optimizer directive to force the subquery to choose the higher cost path.
Problem summary
**************************************************************** * USERS AFFECTED: * * Users of 10.00.xC8 and later, 11.10.xC2 and later, 11.50.xC1 * * and later. * **************************************************************** * PROBLEM DESCRIPTION: * * A complex query with nested subquery may have sub-optimal * * query plan if the following conditions are met: * * * * Both a subquery and its nested subquery are correlated * * subqueries. * * The nested subquery correlates with the main query (its * * grand parent), not the subquery (its parent). * * * * For example, if you have a main query, subq1, subq2, where * * subq1 is a subquery in main query, and subq2 is a subquery * * inside subq1 (nested), and both subq1 and subq2 are * * correlated subqueries, and in case of both subq1 and subq2, * * the correlation is with the main query. * **************************************************************** * RECOMMENDATION: * * Upgrade to IDS 11.50.xC6W2 or above. * ****************************************************************
Problem conclusion
The problem was first fixed in IDS 11.50.xC6W2.
Temporary fix
Comments
APAR Information
APAR number
IC62006
Reported component name
IBM IDS ENTRP E
Reported component ID
5724L2304
Reported release
B15
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2009-07-13
Closed date
2009-10-09
Last modified date
2011-12-14
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
IBM IDS ENTRP E
Fixed component ID
5724L2304
Applicable component levels
RB15 PSN
UP
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"B15","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
14 December 2011