February 2010 Archives

About IBM DB2 z/OS V.8 is not support row_number(). From DB2PORTAL Blog just use count() and temporary table to solve this problem. Use this sql statement to limit result range, like pre/next page effect.

SELECT DEPTNO, ROW#
FROM DEDBADM.DEPT TB1,
TABLE (SELECT COUNT(*) + 1 AS ROW#
FROM DEDBADM.DEPT TB2
WHERE TB2.DEPTNO < TB1.DEPTNO) AS TDEPT_TAB
WHERE ROW# BETWEEN 50 AND 60
ORDER BY DEPTNO
FETCH FIRST 10 ROWS ONLY
OPTIMIZE FOR 10 ROWS
WITH UR;