How to code an INSERT statement with Virtual Foreign Key support.
Article ID: 10850
Updated On:
Products
Issue/Introduction
This document describes how to code an INSERT statement using Virtual Foreign Key support.
Virtual Foreign Key support was added to the CA IDMS SQL/Option during the Incremental Release program of r19.0.
Full support was available as of Incremental Release 4, provided with APAR RO90312.
For an SQL schema defined for a non-SQL database, Virtual Foreign Key support provides virtual ROWIDs as extra columns on the table.
The first of these ROWIDs represents the record itself.
The remaining ROWIDs represent the owners of the sets in which the record participates.
For example, assume the following SQL schema has been created:
CREATE SCHEMA EMPNETV
FOR NONSQL SCHEMA APPLDICT.EMPSCHM VERSION 100
DBNAME EMPDEMO
WITH VIRTUAL KEYS
;
You can issue the following SELECT against the EXPERTISE record:-
SELECT * FROM EMPNETV.EXPERTISE WHERE SKILL_LEVEL_0425='01';
*+
*+ SKILL_LEVEL_0425 EXPERTISE_YEAR_0425 EXPERTISE_MONTH_0425
*+ ---------------- ------------------- --------------------
*+ 01 1976 6
*+
*+ EXPERTISE_DAY_0425 ROWID FKEY_EMP_EXPERTISE
*+ ------------------ ----- ------------------
*+ 15 X'0125050300000008' X'0125050100000008'
*+
*+ FKEY_SKILL_EXPERTISE
*+ --------------------
*+ X'0125A60300000008'
*+
*+ 1 row processed
The ROWID column is the rowid of the selected EXPERTISE record.
FKEY_EMP_EXPERTISE is the rowid of the EMPLOYEE record which owns it within the EMP-EXPERTISE set.
FKEY_SKILL_EXPERTISE is the rowid of the SKILL record which owns it within the SKILL-EXPERTISE set.
The presence of the virtual foreign key columns allows the user to manipulate the data, including the nature of the set connections, with native SQL commands.
The SET name does not have to be used to indicate the owner-member set connection which it represents.
Environment
Resolution
Using sub-selects within the VALUE clause, an INSERT statement can be constructed with no need to directly reference the hexadecimal value of the virtual foreign key rowid type columns.
For example:
INSERT INTO EMPNETV.EXPERTISE
VALUES ( '01', 2017, 11, 1,
NULL,
SELECT ROWID FROM EMPNETV.EMPLOYEE WHERE EMP_ID_0415=23,
SELECT ROWID FROM EMPNETV.SKILL WHERE SKILL_ID_0455=3650);
The above statement will store a new EXPERTISE record, connect it to the EMP-EXPERTISE set owned by the EMPLOYEE record with EMP-ID-0415=23, and connect it to the SKILL-EXPERTISE set owned by the SKILL record with SKILL-ID-0455=3650.
Note that at the time of the INSERT, the rowid of the EXPERTISE record is not known so it is set to NULL in the INSERT statement.
The need to specify NULL as the rowid of the new record can be avoided if you use the SQL syntax which identifies the columns and omit the ROWID column:
INSERT INTO EMPNETV.EXPERTISE
(SKILL_LEVEL_0425, EXPERTISE_YEAR_0425, EXPERTISE_MONTH_0425,
EXPERTISE_DAY_0425, FKEY_EMP_EXPERTISE, FKEY_SKILL_EXPERTISE)
VALUES ( '01', 2017, 11, 1,
SELECT ROWID FROM EMPNETV.EMPLOYEE WHERE EMP_ID_0415=23,
SELECT ROWID FROM EMPNETV.SKILL WHERE SKILL_ID_0455=3650);
If you run the above statements to test the information provided in this Knowledge Document, you can use the following statement to delete the row it created:
DELETE FROM EMPNETV.EXPERTISE WHERE EXPERTISE_YEAR_0425=2017;
Feedback
