RID and RID_BIT scalar functions

The RID and RID_BIT functions are used to uniquely identify a row. Each returns the row identifier (RID) of a row. The result of the RID_BIT function, unlike the result of the RID function, contains table information to help you avoid inadvertently using it with the wrong table. Both functions are non-deterministic.

The schema is SYSIBM. The function name cannot be specified as a qualified name.

RID: Indicates that the RID function is to be run. The RID function cannot be used in a partitioned database environment.
RID_BIT: Indicates that the RID_BIT function is to be run.
table-designator: Uniquely identifies a base table, view, or nested table expression (SQLSTATE 42703). If the table designator specifies a view or nested table expression, the RID_BIT and RID functions return the RID of the base table of the view or nested table expression. The specified view or nested table expression must contain only one base table in its outer subselect (SQLSTATE 42703). The table designator must be deletable (SQLSTATE 42703). For information about deletable views, see the Notes section of CREATE VIEW.
If a table designator is not specified, the FROM clause must contain only one element which can be derived to be the table designator (SQL STATE 42703).

Result

The data type of the result is either BIGINT (for RID) or VARCHAR (16) FOR BIT DATA (for RID_BIT). The result can be null.

The RID or RID_BIT function might return different values when invoked several times for a single row. For example, if RID or RID_BIT is run both before and after the reorg utility is run against the specified table, the function might return a different value each time.

Notes

To implement optimistic locking in an application, use the values returned by the ROW CHANGE TOKEN expression as arguments to the RID_BIT scalar function.
The RID scalar function cannot be used with column-organized tables.
Syntax alternatives: The following alternatives are non-standard. They are supported for compatibility with earlier product versions or with other database products.
- The pseudocolumn ROWID can be used to refer to the RID. An unqualified ROWID reference is equivalent to RID_BIT() and a qualified ROWID such as EMPLOYEE.ROWID is equivalent to RID_BIT(EMPLOYEE).

Examples

Example 1: Return the RID and last name of employees in department 20 from the EMPLOYEE table.

   SELECT RID_BIT (EMPLOYEE), ROW CHANGE TOKEN FOR EMPLOYEE, LASTNAME
     FROM EMPLOYEE
     WHERE DEPTNO = '20'

Example 2: Given table EMP1, which is defined as follows:

   CREATE TABLE EMP1 (
     EMPNO   CHAR(6),
     NAME    CHAR(30),
     SALARY  DECIMAL(9,2),
     PICTURE BLOB(250K),
     RESUME  CLOB(32K)
   )

Set host variable HV_EMP_RID to the value of the RID_BIT built-in scalar function, and HV_EMP_RCT to the value of the ROW CHANGE TOKEN expression for the row corresponding to employee number 3500.

   SELECT RID_BIT(EMP1), ROW CHANGE TOKEN FOR EMP1
     INTO :HV_EMP_RID, :HV_EMP_RCT FROM EMP1
     WHERE EMPNO = '3500'

Using that RID value to identify the employee, and user-defined function UPDATE_RESUME, increase the employee's salary by $1000 and update the employee's resume.

   UPDATE EMP1 SET
     SALARY = SALARY + 1000,
     RESUME = UPDATE_RESUME(:HV_RESUME)
   WHERE RID_BIT(EMP1) = :HV_EMP_RID
   AND ROW CHANGE TOKEN FOR EMP1 = :HV_EMP_RCT