A fix is available
APAR status
Closed as program error.
Error description
When using a query with a subquery and a FETCH FIRST n ROW ONLY and incorrout will occur. Example query is as follows: SELECT * FROM DSN8810.EMP WHERE (WORKDEPT) IN ( SELECT WORKDEPT FROM DSN8810.EMP GROUP BY WORKDEPT ORDER BY WORKDEPT FETCH FIRST 2 ROWS ONLY); DB2INCORR/K
Local fix
N/A
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 9 for z/OS users of subqueries * * containing fetch-first-clause with Group By. * **************************************************************** * PROBLEM DESCRIPTION: An incorrect result set (fewer rows * * than expected) can be returned for a * * query that uses the fetch-first-clause * * in a subquery containing a GROUP BY. * **************************************************************** * RECOMMENDATION: * **************************************************************** DB2 can return an incorrect result set (fewer rows than expected) for a query that uses the fetch-first-clause and a GROUP BY clause in a subselect. In the following example, the IN subquery is expected to return the first two WORKDEPT values, i.e. 'A00' and 'B01'. However, only 'A00' gets returned as the FETCH FIRST rows get counted before the grouping operation. So, the end result is missing a group. Given the following example query. SELECT EMPNO,WORKDEPT FROM DSN8910.EMP WHERE (WORKDEPT) IN (SELECT WORKDEPT FROM DSN8910.EMP GROUP BY WORKDEPT FETCH FIRST 2 ROWS ONLY); Five rows are returned but this is incorrect. The group of values for WORKDEPT 'B01' are missing. EMPNO WORKDEPT ------ -------- 000010 A00 000110 A00 000120 A00 200010 A00 200120 A00 Instead, the following 6 rows are expected. EMPNO WORKDEPT ------ -------- 000010 A00 000020 B01 000110 A00 000120 A00 200010 A00 200120 A00 Note the presence of group 'B01' for WORKDEPT.
Problem conclusion
The code in DB2 has been modified to process the Fetch First clause after other subselect clauses such as Group By, Having, and Order By have been processed. This will allow the correct result set to be returned. Additional Keywords: SQLINSUBQ SQLSUBQUERY SQLGROUPBY FETCHFIRST
Temporary fix
Comments
APAR Information
APAR number
PK79632
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt
Submitted date
2009-01-29
Closed date
2009-03-16
Last modified date
2011-05-12
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK44930
Modules/Macros
DSNXREXI DSNXRGBJ DSNXRGRP DSNXRRPJ DSNXRSGB
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
R910 PSY UK44930
UP09/04/01 P F903
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":"9.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":"9.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
12 May 2011