IDMS: How to DISCONNECT records with SQL

Products

IDMS IDMS - Database

Issue/Introduction

This document describes how member records can be DISCONNECTed and reCONNECTed to other set occurrences using SQL.

Environment

Release : 19.0
Component : CA IDMS/SQL Option

Resolution

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