IDMS SQL & the relational option

Two kinds of table living side by side

The SQL option adds an SQL engine, an optimiser and a catalog to IDMS. What it addresses falls into two categories that look identical from a JDBC connection and behave rather differently underneath.

CREATE SCHEMA EMPSQL
    FOR NONSQL SCHEMA EMPSCHM VERSION 100
    DBNAME EMPDB;

-- now the network records look like tables
SELECT EMP_ID_0415, EMP_LAST_NAME_0415, STATUS_0415
  FROM EMPSQL.EMPLOYEE
 WHERE STATUS_0415 = '01';

No data moves. No records are copied. The SQL engine navigates the same pointers your COBOL program does — it simply generates the navigation for you instead of making you write it.

What the pointer-linked records look like through SQL

Asking the database to describe itself

IDMS SQL keeps its metadata in catalog tables under the SYSTEM schema. They are queryable with ordinary SQL, which makes them an excellent second source of metadata alongside the dictionary.

-- every table visible to you
SELECT SCHEMA, NAME, TABLE_TYPE
  FROM SYSTEM.TABLE
 ORDER BY SCHEMA, NAME;

-- the full column list for one table, in order
SELECT NAME, DATATYPE, LENGTH, SCALE, NULLS
  FROM SYSTEM.COLUMN
 WHERE TABLE_NAME = 'EMPLOYEE'
   AND SCHEMA     = 'EMPSQL'
 ORDER BY SEQ_NO;

-- declared referential constraints
SELECT NAME, REFERENCING_TABLE, REFERENCED_TABLE
  FROM SYSTEM.CONSTRAINT;

-- indexes
SELECT NAME, TABLE_NAME, UNIQUE_RULE
  FROM SYSTEM.INDEX;

Catalog table and column names vary by release. Run SELECT * FROM SYSTEM.TABLE WHERE SCHEMA = 'SYSTEM' on your system to discover the actual layout before scripting against it.

Referential constraints, and the linked variety

SQL-defined tables support ordinary declared referential constraints. IDMS additionally lets a constraint be implemented physically as a set — pointers rather than value matching — which gives relational syntax with network performance characteristics.

CREATE TABLE EMPSQL.PROJECT
  ( PROJ_ID      UNSIGNED NUMERIC(6)   NOT NULL,
    PROJ_NAME    CHAR(40)              NOT NULL,
    DEPT_ID      UNSIGNED NUMERIC(4)   NOT NULL,
    BUDGET       DECIMAL(11,2),
    START_DATE   DATE,
    CONSTRAINT PROJ_PK PRIMARY KEY (PROJ_ID) );

CREATE INDEX PROJ_DEPT_NDX ON EMPSQL.PROJECT (DEPT_ID);

CREATE TABLE EMPSQL.TASK
  ( TASK_ID      UNSIGNED NUMERIC(8)   NOT NULL,
    PROJ_ID      UNSIGNED NUMERIC(6)   NOT NULL,
    DESCRIPTION  VARCHAR(120),
    CONSTRAINT TASK_PK PRIMARY KEY (TASK_ID),
    CONSTRAINT TASK_PROJ FOREIGN KEY (PROJ_ID)
        REFERENCES EMPSQL.PROJECT (PROJ_ID) );

The point to take away: an IDMS installation can have genuinely relational tables sitting next to network records in the same database, in the same areas, under the same journals and the same recovery. When you inventory a source system, do not assume everything is CODASYL.

Why a schema change can force a code rebuild

Static SQL embedded in a program is compiled into an access module: a stored, named object containing the chosen access paths, analogous to a Db2 package. Dynamic SQL — which is what a JDBC client issues — is optimised at execution time instead.

CREATE ACCESS MODULE EMPSQL.PAYROLL01
    FOR PROGRAM PAYROL01
    DEFAULT SCHEMA EMPSQL
    ISOLATION LEVEL CURSOR STABILITY;

-- after a schema change, plans must be regenerated
DROP ACCESS MODULE EMPSQL.PAYROLL01;

The practical consequence for you: if the source team changes an index or a schema, static SQL programs may need their access modules recreated. That is a change window, and a change window is something your replication cutover plan has to be aware of.

Connecting from a Linux or Windows machine

CA IDMS Server provides JDBC and ODBC drivers plus a server component. From a Linux or Windows process you connect over TCP/IP to a listener, naming the node, dictionary and database.

jdbc:idms://mfhost:3709/EMPDB?dictname=APPLDICT&node=SYSTEM71

-- and then perfectly ordinary SQL
SELECT e.EMP_ID_0415,
       e.EMP_LAST_NAME_0415,
       d.DEPT_NAME_0410,
       p.SALARY_AMOUNT_0420
  FROM EMPSQL.EMPLOYEE e
  JOIN EMPSQL.DEPARTMENT d ON ...
  JOIN EMPSQL.EMPOSITION p ON ...
 WHERE e.STATUS_0415 = '01';

URL syntax, ports and property names are installation- and release-specific. Treat the above as shape, not as something to paste.

Lab — generate a target table from a network record

Pick a record and a key strategy, and read the generated DDL and the warnings. This is exactly the decision you will make for every record type in scope.

What it will not do, before you plan around it

• It is a licensed option and may not be installed. Confirm before designing around it.
• Not every network structure maps. Repeating groups, redefines and variable-length structures may be excluded from the SQL view or exposed in a form that needs interpretation.
• Performance is not free. The SQL optimiser works with the access paths the network schema happens to provide. A predicate with no supporting path becomes a sweep, exactly as it would in DML.
• External run units are consumed. Every JDBC connection counts against MAXERUS.
• Locking still happens at the IDMS level. An SQL query readying an area participates in the same lock manager as everything else.

What this means for replication

• Use SQL for metadata and validation, not for bulk. The catalog gives you column types and lengths in machine-readable form; validation queries give you an independent check on the target.
• The SQL view is a good sanity check on your own model. If IDMS's own mapping of a record to a table disagrees with the model you designed from the schema, one of you has misread something.
• Do not assume the SQL view is complete. Structures it cannot represent are still real data that still changes. Cross-check the SQL column list against the schema element list.
• Null handling is your decision to document. Write the sentinel-to-null mapping table early, get it agreed by the business, and put it in version control alongside the DDL.
• Watch the run-unit budget. A validation harness that opens twenty connections is twenty external run units competing with production batch.

Checkpoint