File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes ROW_NUMBER() OVER in DB2 8.x in Z/OS invoked from Java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ROW_NUMBER() OVER in DB2 8.x in Z/OS invoked from Java" Watch "ROW_NUMBER() OVER in DB2 8.x in Z/OS invoked from Java" New topic
Author

ROW_NUMBER() OVER in DB2 8.x in Z/OS invoked from Java

Sam Gehouse
Ranch Hand

Joined: Jul 21, 2003
Posts: 281
I am trying to accomplish pagination in Java class (DAO) by writing SQL invoked on DB2 8.x running on Z/OS (mainframe).

I would like to pass parameter to SQL to retrieve a chunk of records e.g. One SQL will retrieve rows 1 through 250 (incluside)
Next SQL will retrieve rows 251 through 500 and so on.

I would like to write SQL as preparedstatement as:

SELECT *
FROM (SELECT
ROW_NUMBER() OVER (ORDER BY empno ASC) AS rownumber,empno
FROM edwarde.employee
) AS foo
WHERE rownumber between ? and ?;

? are start and end row numbers which will be set programmatically in Java in Preparedstatement. Obviously, multiple calls will be made to database; each time with differetnt parameters.

My questions are:
1) Has anybody had success in using syntax above to get chunks of records in DB2 in Z/OS?
2) If I do not need ordering, can I remove ORDER by cluase? If I remove ORDER BY clause, probably there will be improvements in performance.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ROW_NUMBER() OVER in DB2 8.x in Z/OS invoked from Java