DB2 FOR LUW / DB2FORLUW / 950 - DB2

When using the DESC or ASC clause within an SQL statement 
containing an OLAP function, the order may not be enforced 
giving unordered results 
 
e.g. 
db2 "select row_number() over() as num, tabname from 
syscat.tables order by num desc" 
gives an unordered result although DESC is specified.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* see Error Description.                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 v9.5 fp9 or later.                            * 
****************************************************************

DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
 

First fixed in DB2 v9.5 fp9.

There are 2 possible workarounds: 
 
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"

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

27.09.2011
13.03.2012
13.03.2012

9.5.FP9