A fix is available
APAR status
Closed as program error.
Error description
fix for ambigous cursor which defined as "WITH UR" . The fix also closes a loop hole for a sensitive cursor defined WITH UR , thus an SQLCODE243 will now be issued correctly during bind package .
Local fix
add 'FOR FETCH ONLY' clause to SQL
Problem summary
**************************************************************** * USERS AFFECTED: DB2 for z/OS users who define a READ ONLY * * cursor using 'WITH UR', and the cursor * * utilizes a non-correlated subquery. * **************************************************************** * PROBLEM DESCRIPTION: When a cursor is defined using 'WITH * * UR' but without a 'FOR FETCH ONLY' * * clause, and the cursor utilizes a * * non-correlated subquery, DB2 may drive * * a subquery-to-join transformation if * * there is an unique index for the * * subquery. * * However, DB2 considers this cursor * * as an ambiguous cursor when selecting * * an access path, and pins the parent * * table as the outer table. This may * * result in an inefficient access path. * * For example, * * * * DECLARE CUR1 CURSOR FOR * * SELECT T1.C1 * * FROM T1 * * WHERE T1.C2 IN * * (SELECT T2.C1 * * FROM T2 * * WHERE T2.C2 = 1) * * WITH UR; * * * * With the above cursor, if there is an * * unique index on T2, it drives the * * non-correlated subquery-to-join * * transformation. However, when DB2 * * selects access path, the cursor CUR1 * * is considered as an ambiguous cursor * * due to the lack of a 'FOR FETCH ONLY' * * clause, and pins T1 as the outer table * * of the join. This may result in a * * sub-optimal access path because T2 as * * the outer table may provide a better * * access path. * **************************************************************** * RECOMMENDATION: * **************************************************************** When a cursor is defined using 'WITH UR' and utilizes a non-correlated subquery, if there is an unique index on the non-correlated subquery, DB2 drives subquery-to-join transformation. However, due to lack of a 'FOR FETCH ONLY' clause, the cursor is considered as an ambiguous cursor during access path selection, and it pins the parent table as the outer table. This may result in a sub-optimal access path because the subquery of the join transformation as outer may provide a better access path. Additional Keywords: SQLSUBQUERY SQLNONCORRSUBQ SQLACCESSPATH SQLPERFORMANCE
Problem conclusion
Code is changed so that the above situation can consider the parent table in other join sequences as well, so that a better access path may be chosen.
Temporary fix
Comments
APAR Information
APAR number
PK64719
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
810
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2008-04-18
Closed date
2008-05-23
Last modified date
2009-08-04
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK36742
Modules/Macros
DSNXOGP
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
R810 PSY UK36742
UP08/06/12 P F806
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":"8.1","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":"8.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
04 August 2009