Using IDMS VIRTUAL FOREIGN KEYS to achieve a LEFT JOIN

Products

• IDMS
• IDMS - Database

Issue/Introduction

This document describes how to use the new VIRTUAL FOREIGN KEYS feature of CA-IDMS r19.0 to perform a LEFT JOIN on a network-defined database with more than two records involved.

Historically, performing a LEFT JOIN on a network-defined database with more than two tables involved in the SELECT has been problematic.

Either it would return an error or an unexpected result.

Environment

The addition of Incremental Release 2 (with PTF RO80871) on CA-IDMS r19.0 has made this possible.

Resolution

The following examples use schema EMPNET with this definition and having it set as the CURRENT SCHEMA:

     CREATE SCHEMA EMPNET
         FOR NONSQL SCHEMA APPLDICT.EMPSCHM VERSION 100
             DBNAME EMPDEMO
         ;
     SET SESSION CURRENT SCHEMA EMPNET;
     *+ Status = 0        SQLSTATE = 00000

With two tables, a LEFT JOIN works either with the new JOIN syntax:

SELECT E.EMP_ID_0415, X.SKILL_LEVEL_0425
FROM EMPLOYEE E
LEFT JOIN EXPERTISE X ON "EMP-EXPERTISE"
WHERE EMP_ID_0415 BETWEEN 125 AND 150;
*+
*+ EMP_ID_0415  SKILL_LEVEL_0425
*+ -----------  ----------------
*+         127  04
*+         127  03
*+         149  <null>
*+
*+ 3 rows processed

or with the old PRESERVE syntax:

SELECT E.EMP_ID_0415, X.SKILL_LEVEL_0425
FROM EMPLOYEE E, EXPERTISE X
WHERE "EMP-EXPERTISE" AND EMP_ID_0415 BETWEEN 125 AND 150
PRESERVE EMPLOYEE;
*+
*+ EMP_ID_0415  SKILL_LEVEL_0425
*+ -----------  ----------------
*+         127  04
*+         127  03
*+         149  <null>
*+
*+ 3 rows processed

However, if you want to perform a LEFT JOIN in a SELECT involving more than two tables, it’s not as simple.

The new JOIN syntax gives an error:

SELECT D.DEPT_ID_0410, E.EMP_ID_0415, X.SKILL_LEVEL_0425
FROM DEPARTMENT D
INNER JOIN EMPLOYEE E ON "DEPT-EMPLOYEE"
LEFT JOIN EXPERTISE X ON "EMP-EXPERTISE"
WHERE DEPT_ID_0410=4000;
*+ Status = -4       SQLSTATE = 5000B        Messages follow:
*+ DB005000 T90 C0M324: Message for module IDMSOCF, SQL statement number : 1.
*+ DB005507 T90 C-4M324: Set name EMP-EXPERTISE reference incompatible with statement

The older PRESERVE syntax works but does not give the expected result. It returns NULLs in the fields from the higher-level record as well as the lower-level record:

SELECT D.DEPT_ID_0410, E.EMP_ID_0415, X.SKILL_LEVEL_0425
FROM DEPARTMENT D, EMPLOYEE E, EXPERTISE X
WHERE "DEPT-EMPLOYEE" AND "EMP-EXPERTISE" AND DEPT_ID_0410=4000
PRESERVE EMPLOYEE;
*+
*+ DEPT_ID_0410  EMP_ID_0415  SKILL_LEVEL_0425
*+ ------------  -----------  ----------------
*+       <null>           23  <null>
…
*+       <null>          479  <null>
*+         4000          127  04
*+         4000          127  03
*+       <null>          472  <null>
*+       <null>           45  <null>
*+       <null>           91  <null>
*+       <null>           28  <null>
*+       <null>            3  <null>
*+       <null>            1  <null>
*+         4000          120  04
*+         4000          120  04
*+         4000          120  03
*+       <null>           69  <null>
…
*+       <null>          106  <null>
*+         4000            7  04
*+       <null>          334  <null>
…
*+       <null>           74  <null>
*+         4000          119  04
*+         4000          119  04
*+       <null>           35  <null>
…
*+       <null>          301  <null>
*+
*+ 60 rows processed

As of r19.0 Incremental Release 2, provided by PTF RO80871, VIRTUAL FOREIGN KEY support has been added.

Among other things, this removes the need to use SET names to join records.
With the following schema defined and made current:

     CREATE SCHEMA EMPNETV
         FOR NONSQL SCHEMA APPLDICT.EMPSCHM VERSION 100
             DBNAME EMPDEMO
         WITH VIRTUAL KEYS
         ;
     SET SESSION CURRENT SCHEMA EMPNETV;
     *+ Status = 0        SQLSTATE = 00000

You can issue the following SELECT to get the desired result:

SELECT D.DEPT_ID_0410, E.EMP_ID_0415, X.SKILL_LEVEL_0425
FROM DEPARTMENT D
INNER JOIN EMPLOYEE E ON E.FKEY_DEPT_EMPLOYEE=D.ROWID
LEFT JOIN EXPERTISE X ON X.FKEY_EMP_EXPERTISE=E.ROWID
WHERE DEPT_ID_0410=4000;
*+
*+ DEPT_ID_0410  EMP_ID_0415  SKILL_LEVEL_0425
*+ ------------  -----------  ----------------
*+         4000          120  04
*+         4000          120  03
*+         4000          120  04
*+         4000            7  04
*+         4000          119  04
*+         4000          119  04
*+         4000          158  <null>
*+         4000          127  04
*+         4000          127  03
*+         4000          149  <null>
*+         4000          476  <null>
*+
*+ 11 rows processed

Note, the old PRESERVE syntax with the VIRTUAL FOREIGN KEYS support works but gives the same unexpected result mentioned above.