How to code an INSERT statement with Virtual Foreign Key support.

Document ID : KB000010850
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

How do I code an INSERT statement using Virtual Foreign Key support?

Background:

Virtual Foreign Key support was added to the CA IDMS SQL/Option during the Incremental Release program. Full support was available as of Incremental Release 4, provided with APAR RO90312.

For SQL schemas defined for non-SQL database, Virtual Foreign support provides virtual rowids as extra columns which represent the actual record, and the owner for any sets in which the record participates.

For example, assume the following SQL schema has been created:

     CREATE SCHEMA EMPNETV
         FOR NONSQL SCHEMA APPLDB.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

ROWID 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 these virtual foreign keys allows you to manipulate the data, including the nature of the set connections, with native SQL commands (i.e. the SET name does not have to be used to indicate the owner-member set connection which it represents).

Environment:
CA IDMS r19.0 GA Release.
Instructions:

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 leave out ROWID:

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 functionality, you can use the following to delete the row it created:

 

delete from empnetv.expertise where expertise_year_0425=2017;

Additional Information:

For more information, see the following CA IDMS DocOps pages:

INSERT

SQL Virtual Keys