SQL indexes on network-defined records

Products

• IDMS
• IDMS - Database

Issue/Introduction

Is it possible to use CREATE INDEX to define a native SQL index on a network-defined schema record type?

Environment

Release: All supported releases.
Component: SQL Option.

Resolution

No.

When an SQL schema is defined for a network schema and database, that schema "exposes" the records in the network schema to the SQL world. It plays no other role, and no SQL DDL can be used to relate other SQL-defined database objects to those records.

The only way in which SQL- and network- defined databases can work together is that they can be referenced in the same SQL DML. Example:

SELECT E.EMP_ID, E.EMP_LNAME, D.DEPT_ID_0410, D.DEPT_NAME_0410
  FROM DEMOEMPL.EMPLOYEE E INNER JOIN EMPNET.DEPARTMENT D
    ON D.DEPT_ID_0410=E.DEPT_ID;
*+
*+ EMP_ID  EMP_LNAME             DEPT_ID_0410
*+ ------  ---------             ------------
*+   2598  Jacobs                        5100
*+   3778  Ferndale                      5100
*+   2145  Catlin                        5200
*+   2781  Thurston                      5200
*+   4358  Robinson                      5200
*+   3411  Pèrot                         5200
*+   2894  Griffin                       5200
*+
*+ DEPT_NAME_0410
*+ --------------
*+ BRAINSTORMING
*+ BRAINSTORMING
*+ THERMOREGULATION
*+ THERMOREGULATION
*+ THERMOREGULATION
*+ THERMOREGULATION
*+ THERMOREGULATION
*+
*+ 7 rows processed

If a new index is required on a network record, it must be defined in the traditional way - as a system-owned indexed SET in the network schema.

Note that it must be MANDATORY AUTOMATIC otherwise SQL will not use it. The reason for this is that non-MA indexes are not guaranteed to index every occurrence of the record type.

Additional Information

IDMS: The three types of SCHEMA explained