RID scalar function

The RID function returns the record ID (RID) of a row. The RID is used to uniquely identify a row.

Read syntax diagramSkip visual syntax diagramRID( table-designator)

The schema is SYSIBM.

The function might return a different value when it is invoked multiple times for a row. For example, after the REORG utility is run, the RID function might return a different value for a row than would have been returned prior to the REORG utility being run. The RID function is not deterministic.

table-designator
table-designator must be an exposed name that uniquely identifies a base table, a view, or a nested table expression of a subselect in which the function is referenced.

If table-designator specifies a view or a nested table expression, the RID function returns 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.

table-designator must not identify:

  • A table function
  • A collection-derived table
  • An alias, a synonym, or a materialized view
  • A nested table expression that is materialized
  • A system-period temporal table, if the system time sensitive bind option is YES
  • An archive-enabled table, if one of the following conditions is true:
    • For a static statement, the archive sensitive option in effect is YES.
    • For a dynamic statement, the archive sensitive option in effect is YES, and the GET_ARCHIVE built-in global variable is set to 'Y'.

The result of the function is BIGINT. The result can be null.

Notes

Considerations for RID values:
Db2 might reuse RID numbers when a REORG operation is performed. If the RID function is used to obtain a value for a row and an application depends on that value remaining the same as long as the row exists, consider the following alternatives:
  • Add a ROWID column to the table to provide a value that can be associated with each row, rather than invoking the RID function to generate a value for a row.
  • Define a primary key for the table, using the columns of the primary key to ensure uniqueness, rather than invoking the RID function to generate a value for a row.

Examples

Example 1:
Return the RID and last name of employees who are in department '20': 
SELECT RID(EMP), LASTNAME
 FROM EMP
 WHERE DEPTNO = '20';
Example 2:
Return the RID and last name of employees who are in department '20', in a query that specifies a correlation name of A for table EMP. A is the exposed name for table EMP, so it is used as the argument to the RID function.
SELECT RID(A), LASTNAME
 FROM EMP AS A
 WHERE DEPTNO = '20';
If EMP is specified as the argument to the RID function instead of the exposed name, A, an error is returned.
Example 3:
Set the host variable HV_EMP_RID as the value of the RID for the employee with the employee number of '3500': 
SELECT RID(EMP) INTO :HV_EMP_RID
 FROM EMP
 WHERE EMPNO = '3500';