DB2 rownum equivalent

sql db2 db2-luw

Sourav Mehra · Mar 30, 2017 · Viewed 12k times · Source

I have the below query in Oracle which I want to replicate in DB2 which uses the random function and the rownum.

Oracle Query :

SELECT * 
FROM  (
   SELECT * 
   FROM db2admin.QUESTION_BANK 
   WHERE type='PROCESS' 
   ORDER BY dbms_random.value
) WHERE rownum <=?

I got alternate for random function but nothing for rownum. Below is query in DB2,

SELECT * 
FROM  (
  SELECT * 
  FROM db2admin.QUESTION_BANK 
  WHERE type='PROCESS' ORDER BY RAND
) WHERE rownum <= ?

The value for rownum is passed via PreparedStatement.

Answer

DB2 has ROWNUM when you work in the Oracle compatibility mode. To enable just this feature, use this:

db2set DB2_COMPATIBILITY_VECTOR=01
db2stop
db2start

To get all the Oracle features, enable it like this:

db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start

The doc on the DB2_COMPATIBILITY_VECTOR has details on alternatives like ROW_NUMBER() OVER().