DB2 rownum equivalent

Question

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.

Solution 1

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().

Solution 2

try it:

SELECT * 
FROM  (
       SELECT f1.*, rownumber() over(ORDER BY f1.randm ) as rownum
       from ( 
            select f0.*, rand() as randm FROM db2admin.QUESTION_BANK f0
             WHERE f0.type='PROCESS'
            ) f1
      ) f2 
WHERE f2.rownum <= ?

Solution 3

You can use this:

SELECT * 
FROM db2admin.QUESTION_BANK 
WHERE type = 'PROCESS' 
ORDER BY RAND() 
fetch first ? rows only;

Comments

[Sourav Mehra]

"DB2_COMPATIBILITY_VECTOR" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.18.60

[a_horse_with_no_name]

rand is a function, not a column

[Sourav Mehra]

Yes this works perfectly. But one question here wont this only pick first say 20 questions everytime and randomize them?

[Sourav Mehra]

Perfect. This exactly does the task needed.

[a_horse_with_no_name]

@SouravMehra: no, the order by happens before the fetch first is applied

[Eugenio F. Martinez Pacheco]

RAND() is not necessary if you wanna see how a row looks like. Use it only if you are extracting a data sample.

[Eugenio F. Martinez Pacheco]

easier using fetch as commented before, no need to use analytical functions over null partitioning clause