Selected number of records from database in DB2.

Question

Hi All,

I have to fetch only 50 records at a time from database(DB2), for this I have been usig Row_Number but now the persons are telling that this Row_Number is not stable and has bugs in it so now I have to write a different querry for the same as I have to fetch only 50 records at a time.

so please can any body help me out for the same. Thanks in advance. The Query which I have been using is

SELECT PLC.* FROM
     ( SELECT 
      ROW_NUMBER() OVER (ORDER BY PRDLN_CTLG_OID) AS Row,
      PRDLN_CTLG_OID, 
      PRODUCT_LINE_OID      AS  PRODUCT_LINE_OID, 
      RTRIM(CATALOG_ID)             AS  CATALOG_ID,
      FROM PROD_LINE_CATALOG 
      WHERE PRODUCT_LINE_OID=:productLineOID AND ACTV_IND = 1 
      ORDER BY CATALOG_ID) PLC
      WHERE  Row >= :startIndex AND Row <= :endIndex
      ORDER BY PLC.CATALOG_ID DESC 
     WITH UR

Answer 1

use the fetch clause

FETCH FIRST 50 ROWS ONLY

edit

It looks like you use a startIndex and endIndex value. I realize you compare these to Row, but do you have another index to compare? If not, and you insist on moving from Row_Number, maybe the table needs another index.

Select PLC.* From (Select omitting row_number())
Where yournewindex >= startIndex
Fetch first 50 rows only