Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC78930 Status: Closed

UNABLE TO SORT WHEN USING OLAP FUNCTION AND "ORDER BY"

product:
DB2 FOR LUW / DB2FORLUW / 910 - DB2
Problem description:
When using the "ORDER BY DESC" or "ORDER BY ASC" clause within 
an SQL statement 
containing an OLAP function, the order might not be enforced 
giving unordered results 
 
Per example: 
db2 "select row_number() over() as num, tabname from 
syscat.tables order by num desc" 
gives an unordered result although DESC is specified.
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* Users of OLAP functions in SQL statements                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* When using the "ORDER BY DESC" or "ORDER BY ASC" clause      * 
* within                                                       * 
* an SQL statement                                             * 
* containing an OLAP function, the order might not be enforced * 
* giving unordered results                                     * 
*                                                              * 
* For example:                                                 * 
* db2 "select row_number() over() as num, tabname from         * 
* syscat.tables order by num desc"                             * 
* gives an unordered result although DESC is specified.        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please upgrade to DB2 LUW V9.1 FP11                          * 
****************************************************************
Local Fix:
available fix packs:
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

Solution
Problem was fixed in DB2 LUW V9.1 FP11
Workaround
1)"order by 1" 
 
db2 "select row_number() over() as num, tabname from 
syscat.tables 
order by 1 desc" 
 
. 
 
2)the OLAP query can be moved into a nested subquery 
 
db2 "select num, tabname from ( select row_number() over() as 
num, 
tabname from syscat.tables ) T order by num desc"
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC78931 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
27.09.2011
23.12.2011
23.12.2011
Problem solved at the following versions (IBM BugInfos)
9.1.FP11
Problem solved according to the fixlist(s) of the following version(s)
9.1.0.11 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.