Expansion of rowid-pseudo-column

The expanded parameters of rowid-pseudo-column request the ROWID values to be determined when the SQL statement in which they appear is executed.
idmscu19
The expanded parameters of rowid-pseudo-column request the ROWID values to be determined when the SQL statement in which they appear is executed.
Syntax
Expansion of rowid-pseudo-column
►►─┬──────────────────────────────────────────┬───┬──── ROWID ────────────────►◄    ├──┬──────────────┬┬─ table-identifier. ───┤   │    │  └ schema-name. ┘└── view-identifier. ───┘   │    └──────────────── alias. ──────────────────────┘
Parameters
  • schema-name
    Specifies the schema with which the table or view identified by table-identifier or view-identifier is associated. For more information about using a schema name to qualify a table or view identifier, see Identifying Entities in Schemas.
  • table-identifier
    Identifies a base table defined in the dictionary.
  • view-identifier
    Identifies a view defined in the dictionary.
  • alias
    Specifies the alias associated with the table or view to which the ROWID pseudo-column refers. The alias must be defined in the FROM parameter of the subquery, query specification, or SELECT statement that includes the ROWID.
Usage
Because the pseudo-column ROWID obviously becomes easily ambiguous when multiple tables or views are involved in an SQL statement, qualification is required in most cases.
ROWID can generally also be used for tables associated with native VSAM files. However for KSDS native VSAM files ROWID cannot be used to directly access a KSDS record.
Examples
Using ROWID in a Simple SELECT Statement
SELECT ROWID, OFFICE_CODE_0450, OFFICE_CITY_0450   FROM EMPSCHM.OFFICE;                  ROWID  OFFICE_CODE_0450  OFFICE_CITY_0450                -----  ----------------  ----------------  X'0125970100000008'  002               BOSTON  X'0125A00100000008'  001               SPRINGFIELD  X'0125A30100000008'  005               GLASSTER  X'0125A60100000008'  012               CAMBRIDGE  X'0125A90100000008'  008               WESTON
The values of ROWID are displayed as hexadecimal values, which in this case are also the values of the DBKEY and page information (page group and radix) for the OFFICE record in the non-SQL-defined schema EMPSCHM VERSION 100 of the demo employee database.
Using ROWID in the WHERE clause of a Searched UPDATE Statement
UPDATE EMPSCHM.EMPLOYEE SET EMP_CITY = 'BRUSSELS'   WHERE ROWID = X'0124FF01';
The column EMP_CITY of the EMPLOYEE record in the non-SQL schema EMPSCHM VERSION 100 is updated for the record whose DBKEY is X'0124FF01'. In this example the schema does not include virtual keys, so the page information for the EMPLOYEE record is used.
Using ROWID with a Virtual Key Schema
CREATE SCHEMA EMPVFK FOR NONSQL SCHEMA APPLDICT.EMPSCHM V 100 DBNAME EMPDEMO WITH VIRTUAL KEYS; SELECT ROWID, DEPT_ID_0410, DEPT_NAME_0410 FROM EMPVFK.DEPARTMENT WHERE ROWID=X'0125BD0100000008';                  ROWID  DEPT_ID_0410  DEPT_NAME_0410                -----  ------------  --------------  X'0125BD0100000008'           100  EXECUTIVE ADMINISTRATION
This example returns the values for the DEPARTMENT record with DBKEY x’0125BD0100000008’. The ROWID is displayed as an 8-byte hexadecimal value.
Using ROWID in a JOIN of a Base Table and a View
Both examples use view EMPSQL.EMPLOYEEV which is defined as follows:
CREATE VIEW EMPSQL.EMPLOYEEV     AS SELECT * FROM EMPSCHM.EMPLOYEE;
In the first example, view EMPSQL.EMPOFFV is defined as follows:
CREATE VIEW EMPSQL.EMPOFFV  AS SELECT EV.*, O.*   FROM EMPSCHM.OFFICE O, EMPSQL.EMPLOYEEV EV  WHERE "OFFICE-EMPLOYEE";
The returned ROWID for the view is the ROWID of the EMPSCHM.OFFICE base table:
SELECT EOV.ROWID, D.ROWID, D.*, EMP_ID_0415, OFFICE_CODE_0450 FROM EMPSQL.EMPOFFV EOV, EMPSCHM.DEPARTMENT D  WHERE "DEPT-EMPLOYEE" AND EMP_ID_0415 < 5;                  ROWID                 ROWID  DEPT_ID_0410  DEPT_NAME_0410                -----                 -----  ------------  --------------  X'0125A00100000008'   X'0125BD0100000008'           100  EXECUTIVE ADMINISTRATION  X'0125A00100000008'   X'0125BC0100000008'          3100  INTERNAL SOFTWARE  X'0125A00100000008'   X'0125AB0100000008'          3200  COMPUTER OPERATIONS    DEPT_HEAD_ID_0410  EMP_ID_0415       OFFICE_CODE_0450  -----------------  ----------------  ----------------                 30                 1  001                  3                 3  001                  4                 4  001 
In the second example, EMPSQL.EMPOFFV is defined as follows:
CREATE VIEW EMPSQL.EMPOFFV  AS SELECT EV.*, O.*   FROM EMPSQL.EMPLOYEEV EV, EMPSCHM.OFFICE O  WHERE "OFFICE-EMPLOYEE";
The returned ROWID for the view is the ROWID of the EMPSCHM.EMPLOYEE base table, which is the first base table in the view EMPLOYEEV.
SELECT EOV.ROWID, D.ROWID, D.*, EMP_ID_0415, OFFICE_CODE_0450 FROM EMPSQL.EMPOFFV EOV, EMPSCHM.DEPARTMENT D  WHERE "DEPT-EMPLOYEE" AND EMP_ID_0415 < 5;                  ROWID                 ROWID  DEPT_ID_0410  DEPT_NAME_0410                -----                 -----  ------------  --------------  X'0125280100000008'   X'0125BD0100000008'           100  EXECUTIVE ADMINISTRATION  X'01253B0100000008'   X'0125BC0100000008'          3100  INTERNAL SOFTWARE  X'0125530100000008'   X'0125AB0100000008'          3200  COMPUTER OPERATIONS    DEPT_HEAD_ID_0410  EMP_ID_0415  OFFICE_CODE_0450  -----------------  -----------  ----------------                 30            1  001                  3            3  001                  4            4  001
These examples are valid only when using schemas without virtual keys. For tables in schemas with virtual keys, the rowid columns must be explicitly listed in the view column name list due to the inclusion of the ROWID columns when using the *. For more information, see CREATE VIEW.
Searched Update of Records Without Primary Key
This example updates all the COVERAGE records of the employee with EMP_ID=23:
UPDATE EMPSCHM.COVERAGE C   SET SELECTION_YEAR_0400 = 20  WHERE C.ROWID IN (            SELECT CI.ROWID              FROM EMPSCHM.EMPLOYEE E, EMPSCHM.COVERAGE CI             WHERE "EMP-COVERAGE"               AND EMP_ID_0415 = 23);
Searched Delete of Records Without Primary Key
This example deletes all the COVERAGE records of the employee with EMP_ID=23:
DELETE FROM EMPSCHM.COVERAGE C  WHERE C.ROWID IN (        SELECT CI.ROWID          FROM EMPSCHM.EMPLOYEE E, EMPSCHM.COVERAGE CI         WHERE "EMP-COVERAGE"           AND EMP_ID_0415 = 23);