Specifying direct row access by using row IDs

A ROWID column uniquely identifies each row in a table. With ROWID columns you can write queries that navigate directly to a row in the table because the column implicitly contains the location of the row. You can define a ROWID column as either GENERATED BY DEFAULT or GENERATED ALWAYS:

• If you define the column as GENERATED BY DEFAULT, you can insert a value. Db2 provides a default value if you do not supply one. However, to be able to insert an explicit value (by using the INSERT statement with the VALUES clause), you must create a unique index on that column.
• If you define the column as GENERATED ALWAYS (which is the default), Db2 always generates a unique value for the column. You cannot insert data into that column. In this case, Db2 does not require an index to guarantee unique values.

When you select a ROWID column, the value implicitly contains the location of the retrieved row. If you use the value from the ROWID column in the search condition of a subsequent query, Db2 can choose to navigate directly to that row.

If you define a column in a table to have the ROWID data type, Db2 provides a unique value for each row in the table only if you define the column as GENERATED ALWAYS. The purpose of the value in the ROWID column is to uniquely identify rows in the table.

You can use a ROWID column to write queries that navigate directly to a row, which can be useful in situations where high performance is a requirement. This direct navigation, without using an index or scanning the table space, is called direct row access. In addition, a ROWID column is a requirement for tables that contain LOB columns. This topic discusses the use of a ROWID column in direct row access.

For example, suppose that an EMPLOYEE table is defined in the following way:

CREATE TABLE EMPLOYEE
  (EMP_ROWID   ROWID NOT NULL GENERATED ALWAYS,
   EMPNO       SMALLINT,
   NAME        CHAR(30),
   SALARY      DECIMAL(7,2),
   WORKDEPT    SMALLINT);

EXEC SQL BEGIN DECLARE SECTION;
  SQL TYPE IS ROWID hv_emp_rowid;
  short             hv_dept, hv_empno;
  char              hv_name[30];
  decimal(7,2)      hv_salary;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL 
  SELECT EMP_ROWID INTO :hv_emp_rowid 
  FROM FINAL TABLE (INSERT INTO EMPLOYEE
                    VALUES (DEFAULT, :hv_empno, :hv_name, :hv_salary, :hv_dept));
EXEC SQL
  UPDATE EMPLOYEE
  SET SALARY = SALARY + 1200
  WHERE EMP_ROWID = :hv_emp_rowid;

EXEC SQL COMMIT;

For Db2 to be able to use direct row access for the update operation, the SELECT from INSERT statement and the UPDATE statement must execute within the same unit of work. Alternatively, you can use a SELECT from MERGE statement. The MERGE statement performs INSERT and UPDATE operations as one coordinated statement.

The value that you retrieve from a ROWID column is a varying-length character value that is not monotonically ascending or descending (the value is not always increasing or not always decreasing). Therefore, a ROWID column does not provide suitable values for many types of entity keys, such as order numbers or employee numbers.