A fix is available
APAR status
Closed as program error.
Error description
SQL with RANKING specification and char data type may get incorrect output
Local fix
N/A
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 10 for z/OS users of the RANK and * * DENSE_RANK window functions in the OLAP * * specification. * **************************************************************** * PROBLEM DESCRIPTION: An incorrect result can be returned for * * an SQL statement that contains the * * RANK and DENSE_RANK window functions. * **************************************************************** * RECOMMENDATION: * **************************************************************** The RANK and DENSE_RANK window functions can return an incorrect result set when the argument contains a padded string. The following simple example illustrates a failing case. Step 1. Create Table T1 and insert three rows. CREATE TABLE T1(C_CHA CHAR(2), C_VARC VARCHAR(2)); INSERT INTO T1 VALUES('A','AA'); INSERT INTO T1 VALUES('A','AA'); INSERT INTO T1 VALUES('AA','A'); Step 2. Select from Table T1 using the RANK and DENSE_RANK OLAP functions. SELECT C_CHA AS OBY, C_VARC||C_CHA AS PBY, RANK() OVER( PARTITION BY C_VARC||C_CHA ORDER BY C_CHA ) AS RANK1, DENSE_RANK() OVER( PARTITION BY C_VARC||C_CHA ORDER BY C_CHA ) AS DENSE_RANK1 FROM T1; Step 3. Check the results. The following incorrect result is returned. The 3rd row is not as expected. The RANK1 and DENSE_RANK1 values for the 3rd row should be 3 and 2. +-------------------------------------------------- | OBY | PBY | RANK1 | DENSE_RANK1 | +-------------------------------------------------- 1_| A | AAA | 1 | 1 | 2_| A | AAA | 1 | 1 | 3_| AA | AAA | 1 | 1 | +-------------------------------------------------- This problem occurs because DB2 doesn't handle the padding character properly for the input argument to the RANK and DENSE_RANK window functions.
Problem conclusion
DB2 code is modified to use correct padding character for the argument for RANK and DENSE_RANK window functions. Additional Keywords: SQLINCORR SQLINCORROUT INCORROUT DB2INCORR/K SQLRANK SQLDENSERANK
Temporary fix
********* * HIPER * *********
Comments
APAR Information
APAR number
PM88703
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-05-09
Closed date
2013-06-05
Last modified date
2013-07-17
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK94861
Modules/Macros
DSNXRWND
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RA10 PSY UK94861
UP13/06/20 P F306 ½
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:
17 July 2013