IDMS/SQL: Using ROWID for DIRECT-DBKEY

Products

• IDMS
• IDMS - Database

Issue/Introduction

This article describes how to use the ROWID virtual foreign key column of a table to influence the underlying record's dbkey when the location mode is DIRECT.

Environment

Release : 19.0
Component : CA IDMS/SQL Option

Resolution

When using Virtual Foreign key support, every record occurrence has a virtual column called ROWID which identifies the dbkey of the record.

In an INSERT, if the location mode is CALC or VIA; the ROWID column should be set to NULL, because at the time of the INSERT, the dbkey of the record is obviously not known.

However, if the location mode is DIRECT, the ROWID column can be used to influence the page on which the record will be stored, in the same way that the DIRECT-DBKEY can be used in a network STORE.

Example:

INSERT INTO TDMSCHEM.ADDR
VALUES ( 'ADDR0101' , 'ADDR0102', X'024DDD0000000008' );
*+ Status = 0        SQLSTATE = 00000
*+ 1 row processed

SELECT * FROM TDMSCHEM.ADDR;
*+
*+ COL1_1103  COL2_1103                 ROWID
*+ ---------  ---------                 -----
*+ ADDR0101   ADDR0102    X'024DDD0C00000008'
*+
*+ 1 row processed

Note that the record was stored on page x'24DDD' (151,005).