Is there any column in DB2 like "ROWNUM" in oracle? 1

[Guest_imported]

Hi,

I am fetching the details based on ROWNUM in oracle. When I am porting the same query in DB2, the ROWNUM column is not available. Is there any alternative to identify as ROWNUM. Thanks in advance.

bye...
Lakshmi Narayana

 

[legacy370]

What you might be looking for is ROWID. Take a look at the following, from the IBM DB2 manual:

A row ID is a value that uniquely identifies a row in a table. A column or a host variable can have a row ID data type. A ROWID column enables queries to be written that navigate directly to a row in the table. Each value in a ROWID column must be unique, and DB2 maintains the values
permanently, even across table space reorganizations. When a row is inserted into the table, DB2 generates a value for the ROWID column unless one is supplied. If a value is supplied, it must be a valid row ID value that was previously generated by DB2 and the column must be defined as GENERATED BY DEFAULT. Users cannot update the value of a ROWID column.

 

[Guest_imported]

DB2 supports two ways of generating unique ID's for rows : identity columns and sequences. You can of course roll your own but that will always be slower than using one of these two.

Generally speaking, I recommend using sequences because they do not force you to insert a row to retrieve a unique ID. As we all know, DB2 (unlike Oracle) behaves poorly when you insert a row and don't commit pretty quickly.

Sequences are new as of 7.1.

-- Max