"Order by xxx Desc" does not work with DB 8.1 FP2.

We use Type 4 Driver (DB2Java.zip), to connect our java application running on IBM JRE 1.3.1 to DB2 8.1 FP2 database.

A select query is executed repeatedly to fetch results in batches. For example, if the Select query returns 100 results, we store only the first 10 from the fetched results in a two dimensional array and then return the array. When the user clicks "next" same query is executed again and next only 10 results are returned.

This is basically done for page-wise display of results in JSP page.

1.Populated with same data in two different databases. One DB2 7.2 and another is DB 8.1
2.Below query and sample results works properly in DB 7.2 database and in case of DB 8.1 FP 2 same results are returned again and again.
3. Application and databases run on AIX version 5.2 machines.
4. Keyword DISTINCT in SQL query is not required for our requirement. We get unique results always without distinct also.
5. Problem happens obly for "Order by DESC". " Order by Asc" works fine.

IS THIS IS A BUG WITH DB2JAVA.ZIP DRIVER FILE

Table Structure
|
Table Name : | Table Name :
MESSAGE | STATUS
|
Columns: | Columns:
MessageID | StatusID
Subject | StatusName
StatusID (Req.)|

SQL Query

SELECT MessageID, Subject,
COALESCE(UPPER(case when m.StatusID < 9 then 'Active' when m.StatusID = 9 then 'Complete' else 'Unknown State' end ),' ') STATUS
FROM MESSAGE m, STATUS s
WHERE m.StatusID = s.StatusID
ORDER BY STATUS DESC


MessageID Subject STATUS
2 xxxx Complete
6 yyyy Complete
1 bbbb Complete
3 cccc Complete
5 aaaa Active
7 cdfs Active
9 rhtf Active
4 khjk Active
6 thjh Active
11 sdfg Active
<-- First round
13 ghgh Active
32 jkhj Active
34 iuyy Active
14 uiui Active
15 rfff Active
17 ffff Active
19 wewe Active
20 sdsd Active
22 shhh Active
21 llll Active
<-- Second Round ( If DB 7.2 )
13 ghgh Active
32 jkhj Active
34 iuyy Active
14 uiui Active
5 aaaa Active
<-- Wrong Results
7 cdfs Active
9 rhtf Active
4 khjk Active
6 thjh Active
11 sdfg Active
<-- Second Round ( If DB 8,1 )

I hope I do understand your problem right but I don't see a problem

You're sorting on the STATUS column so if it contains the same value for different rows (Active) the result will be unpredictable. If 7.2 returns them in a predictable way you are lucky, it's not because of the query.

If you want it to be sorted on the statusid you need to include the column in your query and sort on it.

Hope this helps.

regards, Gert

Thanks Gert,

But problem occut\rs for other SQL queries alo.

The SQL query given is a same query.

The same problem occurs for other similar queries where we "order by DESC" on column names.

The same queries work fine when they are ordered by same column name in Ascedning order.