Using ROWNUM and DUAL in DB2 9.7

The ROWNUM and DUAL supports are enabled by setting the DB2_COMPATIBILITY_VECTOR registry variable to ORA.

Oracle programmers use ROWNUM quite often to retrieve a controlled number of rows from a SQL statement. The same can be applied in DB2 9.7.

Getting ready

Enable the compatibility feature by setting the DB2_COMPATIBILITY_VECTOR registry to ORA.

How to do it...

In earlier versions of DB2, when we wanted to return only a specific number of rows of an SQL statement, we used the FETCH FIRST clause. Now that we have the Oracle compatibility feature enabled in DB2 9.7, we can use ROWNUM as in Oracle. In DB2, ROWNUM supports<, >, >=, <=, =, and BETWEEN operators.

We can combine ROWID and ROWNUM together to display the physical address of the row in the database. This value is a unique identifier of the row, and does not change over until a REORG occurs on the table.

Let's start using the ROWNUM on the existing sample database tables. With ROWNUM...