Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Sam Gehouse
Ranch Hand
Posts: 281
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic