This document describes how member records can be DISCONNECTed and reCONNECTed to other set occurrences using SQL.
Release : 19.0
Component : CA IDMS/SQL Option
To be able to DISCONNECT/CONNECT records using SQL, Virtual Foreign Key support must be used.
This was provided with RO90312 and is documented at SQL Virtual Keys.
The following SQL commands describe how to update the Virtual Foreign key columns in order to DISCONNECT and CONNECT member records.
The SQL schema must be defined as such:
CREATE SCHEMA EMPNETV
FOR NONSQL SCHEMA APPLDICT.EMPSCHM VERSION 100
DBNAME EMPDEMO
WITH VIRTUAL KEYS
;
Then use the following commands:
SET SESSION CURRENT SCHEMA EMPNETV;
*+ Status = 0 SQLSTATE = 00000
-- LIST ALL THE EMPLOYEES IN DEPARTMENTS 100 AND 5300
SELECT D.ROWID AS DROWID, D.DEPT_ID_0410,
E.EMP_ID_0415, E.FKEY_DEPT_EMPLOYEE
FROM DEPARTMENT D INNER JOIN EMPLOYEE E ON "DEPT-EMPLOYEE"
WHERE D.DEPT_ID_0410 IN (100,5300);
*+
*+ DROWID DEPT_ID_0410 EMP_ID_0415 FKEY_DEPT_EMPLOYEE
*+ ------ ------------ ----------- ------------------
*+ X'0125940100000008' 5300 371 X'0125940100000008'
*+ X'0125940100000008' 5300 366 X'0125940100000008'
*+ X'0125940100000008' 5300 321 X'0125940100000008'
*+ X'0125A40100000008' 100 30 X'0125A40100000008'
*+ X'0125A40100000008' 100 471 X'0125A40100000008'
*+ X'0125A40100000008' 100 1 X'0125A40100000008'
*+ X'0125A40100000008' 100 472 X'0125A40100000008'
*+
*+ 7 rows processed
-- DISCONNECT EMPLOYEE 472 FROM ITS DEPARTMENT
UPDATE EMPLOYEE SET FKEY_DEPT_EMPLOYEE=NULL WHERE EMP_ID_0415=472;
*+ Status = 0 SQLSTATE = 00000
*+ 1 row processed
-- CONFIRM THAT EMPLOYEE 472'S DEPARTMENT IS NOW NULL
SELECT E.EMP_ID_0415, E.FKEY_DEPT_EMPLOYEE
FROM EMPLOYEE E WHERE E.EMP_ID_0415=472;
*+
*+ EMP_ID_0415 FKEY_DEPT_EMPLOYEE
*+ ----------- ------------------
*+ 472 <null>
*+
*+ 1 row processed
-- CONNECT EMPLOYEE 472 TO DEPARTMENT 5300
UPDATE EMPLOYEE SET FKEY_DEPT_EMPLOYEE=
(SELECT ROWID FROM DEPARTMENT WHERE DEPT_ID_0410=5300)
WHERE EMP_ID_0415=472;
*+ Status = 0 SQLSTATE = 00000
*+ 1 row processed
-- LIST ALL THE EMPLOYEES IN BOTH DEPARTMENTS AGAIN
-- NOTE THAT EMPLOYEE 472 IS NOW IN DEPARTMENT 5300
SELECT D.ROWID AS DROWID, D.DEPT_ID_0410,
E.EMP_ID_0415, E.FKEY_DEPT_EMPLOYEE
FROM DEPARTMENT D INNER JOIN EMPLOYEE E ON "DEPT-EMPLOYEE"
WHERE D.DEPT_ID_0410 IN (100,5300);
*+
*+ DROWID DEPT_ID_0410 EMP_ID_0415 FKEY_DEPT_EMPLOYEE
*+ ------ ------------ ----------- ------------------
*+ X'0125940100000008' 5300 371 X'0125940100000008'
*+ X'0125940100000008' 5300 366 X'0125940100000008'
*+ X'0125940100000008' 5300 321 X'0125940100000008'
*+ X'0125940100000008' 5300 472 X'0125940100000008'
*+ X'0125A40100000008' 100 30 X'0125A40100000008'
*+ X'0125A40100000008' 100 471 X'0125A40100000008'
*+ X'0125A40100000008' 100 1 X'0125A40100000008'
*+
*+ 7 rows processed