Row ID values

A ROWID column enables queries to be written that navigate directly to a row in the table because the column implicitly contains the location of the row. Each value in a ROWID column must be unique. Although the location of the row might change, for example across a table space reorganization, Db2 maintains the internal representation of the row ID value permanently. 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.

The internal representation of a row ID value is transparent to the user. The value is never subject to character conversion because it is considered to contain BIT data. The length of a ROWID column as described in the LENGTH column of catalog table SYSCOLUMNS is the internal length, which is 17 bytes. The length as described in the LENGTH2 column of catalog table SYSCOLUMNS is the length of a retrieved ROWID value, which is 40 bytes. The retrieved ROWID value is not permanent. If a commit operation and a REORG on the table space occur after the value is inserted, and before the value is retrieved, the physical location of the row might change.

A ROWID column can be either user-defined or implicitly generated by Db2. You can use the CREATE TABLE statement or the ALTER TABLE statement to define a ROWID column. If you define a LOB column in a table and the table does not have a ROWID column, Db2 implicitly generates a ROWID column. Db2 takes the following actions:

• Creates the column named DB2_GENERATED_ROWID_FOR_LOBSnn, where Db2 appends nn only if the column name already exists in the table, replacing nn with '00' and incrementing by 1 until the name is unique within the row.
• Defines the column as GENERATED ALWAYS and IMPLICITLY HIDDEN.
• Appends the column to the end of the row after all the other explicitly defined columns.