A fix is available
APAR status
Closed as program error.
Error description
The output records may not be in the right Order By sequence on query with Order-by clause. In such cases, it's Hybrid join plan with SORTN_JOIN=N being chosen. Additional Keywords: SQLINCORR SQLINCORROUT DB2INCORR/K SQLHYBRID SQLHYBRIDJOIN HYBRIDJOIN SQLORDERBY
Local fix
Use HINT or add OPTIMIZE FOR 1 ROW to force an access plan other than Hybrid join.
Problem summary
**************************************************************** * USERS AFFECTED: All users of DB2 10 for z/OS who have * * queries containing ORDER BY, GROUP BY, or * * set function DISTINCT with an index matching * * the ORDER BY, GROUP BY, or set function * * DISTINCT columns. * **************************************************************** * PROBLEM DESCRIPTION: Possible incorrect output for a * * query containing ORDER BY, GROUP BY, * * or set function DISTINCT when hybrid * * join is chosen with SORTN_JOIN=N, an * * index matches the ORDER BY, GROUP BY, * * or set function DISTINCT columns, and * * no extra sort is added to handle this * * sorting. * **************************************************************** * RECOMMENDATION: * **************************************************************** Incorrect output may occur for a query containing ORDER BY, GROUP BY, or set function DISTINCT when hybrid join is chosen with SORTN_JOIN=N, an index matches the ORDER BY, GROUP BY, or set function DISTINCT columns, and no extra sort is added to handle this sorting. The chosen access path included a RID list scan which did not preserve the order provided by the index. Additional Keywords: INCORROUT SQLINCORROUT SQLINCORR DB2INCORR/K SQLORDERBY SQLGROUPBY SQLDISTINCT SQLHYBRID SQLHYBRIDJOIN HYBRIDJOIN
Problem conclusion
Code is added to include the extra sort in the situation described above. If the estimated cost of the new access path with the extra sort is higher than a different access path, an access path without hybrid join may be chosen. An access path with an extra sort may be chosen if it is determined that the sort is needed to prevent incorrect output.
Temporary fix
********* * HIPER * *********
Comments
APAR Information
APAR number
PM81848
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
A10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt
Submitted date
2013-01-31
Closed date
2013-04-14
Last modified date
2013-05-06
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK93437 UK93438
Modules/Macros
DSNXOTS1
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
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":"10.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":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
06 May 2013