RID
The RID function returns the record ID (RID) of a row. The RID is used to uniquely identify a row.
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 uniquely identify 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 specify a table function, a materialized view, a nested table expression that is materialized, an alias, or a synonym.
The result of the function is BIGINT. The result can be null.
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.
SELECT RID(EMP), LASTNAME
FROM EMP
WHERE DEPTNO = '20';
SELECT RID(EMP) INTO :HV_EMP_RID
FROM EMP
WHERE EMPNO = '3500';