DB2 Version 9.7 for Linux, UNIX, and Windows

ROWNUM pseudocolumn

DB2® converts any unresolved and unqualified column reference to ROWNUM to the OLAP specification ROW_NUMBER() OVER().

ROWNUM pseudocolumn support is enabled through the setting of the DB2_COMPATIBILITY_VECTOR registry variable.

Both ROWNUM and ROW_NUMBER() OVER() are allowed in the WHERE clause of a subselect, and are useful for restricting the size of a result set.

If ROWNUM is used in the WHERE clause, and there is an ORDER BY clause in the same subselect, the ordering is applied before the ROWNUM predicate is evaluated. This is also true for a ROW_NUMBER() OVER() function in the WHERE clause.

If the OLAP specification ROW_NUMBER() OVER() is used in the WHERE clause, neither a window-order-clause nor a window-partition-clause can be specified.

Notes

Before translating an unqualified reference to 'ROWNUM' as ROW_NUMBER() OVER(), DB2 attempts to resolve the reference as one of:
- A column within the current SQL query
- A local variable
- A routine parameter
- A global variable
Avoid using 'ROWNUM' as a column name or a variable name while ROWNUM pseudocolumn support is enabled.

Example 1

Set the DB2_COMPATIBILITY_VECTOR registry variable to support an application using ROWNUM and outer join operator queries. To achieve maximum compatibility with Oracle, set the value to ORA. This is the recommended setting.

   db2set DB2_COMPATIBILITY_VECTOR=ORA
   db2stop
   db2start

The new setting does not take effect until after the instance has been stopped and then restarted.

Example 2

Assuming that ROWNUM pseudocolumn support is enabled for the connected database, retrieve the twentieth to the fortieth row of a result set that is stored in a temporary table.

   SELECT TEXT FROM SESSION.SEARCHRESULTS
     WHERE ROWNUM BETWEEN 20 AND 40
     ORDER BY ID

Note that ROWNUM is affected by the ORDER BY clause.