ROWID columns

Informix®

When creating a table, Informix automatically adds a ROWID integer column (applies to non-fragmented tables only).

The ROWID column is auto-filled with a unique number and can be used like a primary key to access a given row.

Note: Informix ROWID usage was a common practice in the early days of Informix 4GL programming. Today it is recommended to define all your database tables with a

PRIMARY
                        KEY

to uniquely identify rows.

With Informix, the SQLCA.SQLERRD[6] register contains the ROWID of the last modified row.

IBM® DB2®

IBM DB2 ROWID columns were introduced in version 9.7. Unlike Informix integer row ids, DB2 row ids are based on VARCHAR(16) FOR BIT DATA (128 bit integer) that are usually represented as a 32 char hexadecimal representation of the value. The IBM DB2 ROWID is actually an alternative syntax for RID_BIT(), and a qualified reference to ROWID like tablename.ROWID is equivalent to RID_BIT(tablename).

For example:

x'070000000000000000000065CE770000'

In DB2 SQL, to find a row with a rowid, you must specify the rowid value as an hexadecimal value:

SELECT * FROM customer WHERE ROWID = x'070000000000000000000065CE770000'

or convert the ROWID to an hexadecimal representation and then you can compare to a simple string:

SELECT * FROM customer WHERE HEX(ROWID) ='070000000000000000000065CE770000'

Solution

If the BDL application uses ROWIDs, it is recommended that the program logic is reviewed in order to use the real primary keys instead (usually serials, which can be supported). All references to SQLCA.SQLERRD[6] must be removed because this variable will not hold the ROWID of the last modified row.

The DB2 database driver will convert the ROWID keyword to HEX(ROWID), so it can be used as a VARCHAR(32) with the hexadecimal representation of the BIT DATA. You need however to replace all INTEGER variable definitions by VARCHAR(32) or CHAR(32).

To emulate Informix integer ROWIDs, you can also use the DB2 GENERATE_UNIQUE built-in function, or the IDENTITY attribute of the INTEGER or BIGINT data types.

The ROWID keyword translation can be controlled with the following FGLPROFILE entry:

dbi.database.dsname.ifxemul.rowid = { true | false }

For more details see IBM Informix emulation parameters in FGLPROFILE.