Column LOCMODE in SYSTEM.TABLE explained

Products

• IDMS

Issue/Introduction

This article illustrates the various different potential values for the LOCMODE column in the system table SYSTEM.TABLE.

The value in this column is analogous to the LOCATION MODE (calc, via, direct) in a network-defined database.

Environment

Release : All supported releases.
Component. IDMS SQL Option

Resolution

Use this as input to OCF / IDMSBCF.
It assumes that "SQL" is a sand-box type SQL schema that you have for ad-hoc tables.
Schema SQL must have a DEFAULT AREA clause.

Note that this script creates no rows. Once a table is populated with data, there are limitations on what changes can be made to the table definition. By not creating any data, the various DDL statements can freely change the LOCMODE. 

SET SESSION CURRENT SCHEMA SQL;
-- Uncomment the next 3 line to re-run (If the entities exist).
-- DROP CONSTRAINT C_OWNER_1 FROM T1;
-- DROP TABLE T_OWNER;
-- DROP TABLE T1;
CREATE TABLE T1
       ( C1  UNSIGNED NUMERIC(4) NOT NULL );
SELECT LOCMODE FROM SYSTEM.TABLE WHERE SCHEMA='SQL' AND NAME='T1';
ALTER TABLE T1 DROP DEFAULT INDEX;
SELECT LOCMODE FROM SYSTEM.TABLE WHERE SCHEMA='SQL' AND NAME='T1';
CREATE UNIQUE CALC ON T1 ( C1 );
SELECT LOCMODE FROM SYSTEM.TABLE WHERE SCHEMA='SQL' AND NAME='T1';
DROP CALC FROM T1;
CREATE CALC ON T1 ( C1 );
SELECT LOCMODE FROM SYSTEM.TABLE WHERE SCHEMA='SQL' AND NAME='T1';
DROP CALC FROM T1;
CREATE TABLE T_OWNER
       ( C1  UNSIGNED NUMERIC(4) NOT NULL );
CREATE UNIQUE CALC ON T_OWNER ( C1 );
CREATE CONSTRAINT C_OWNER_1
       T1 (C1)
       REFERENCES T_OWNER (C1)
       LINKED CLUSTERED;
SELECT LOCMODE FROM SYSTEM.TABLE WHERE SCHEMA='SQL' AND NAME='T1';

This is the output of an OCF run of this script with explanations.

SET SESSION CURRENT SCHEMA SQL;
*+ Status = 0        SQLSTATE = 00000
-- UNCOMMENT THE NEXT 3 LINE TO RE-RUN (IF THE ENTITIES EXIST).
-- DROP CONSTRAINT C_OWNER_1 FROM T1;
-- DROP TABLE T_OWNER;
-- DROP TABLE T1;
CREATE TABLE T1
       ( C1  UNSIGNED NUMERIC(4) NOT NULL );
*+ Status = 0        SQLSTATE = 00000
SELECT LOCMODE FROM SYSTEM.TABLE WHERE SCHEMA='SQL' AND NAME='T1';
*+
*+ LOCMODE
*+ -------
*+ R       <== Initially R by default (via the ROWID index)
*+
*+ 1 row processed
ALTER TABLE T1 DROP DEFAULT INDEX;
*+ Status = 0        SQLSTATE = 00000
SELECT LOCMODE FROM SYSTEM.TABLE WHERE SCHEMA='SQL' AND NAME='T1';
*+
*+ LOCMODE
*+ -------
*+ D      <== Becomes D (direct) after dropping the default index
*+
*+ 1 row processed
CREATE UNIQUE CALC ON T1 ( C1 );
*+ Status = 0        SQLSTATE = 00000
SELECT LOCMODE FROM SYSTEM.TABLE WHERE SCHEMA='SQL' AND NAME='T1';
*+
*+ LOCMODE
*+ -------
*+ U      <== Now U after defining a unique CALC key
*+
*+ 1 row processed
DROP CALC FROM T1;
*+ Status = 0        SQLSTATE = 00000
CREATE CALC ON T1 ( C1 );
*+ Status = 0        SQLSTATE = 00000
SELECT LOCMODE FROM SYSTEM.TABLE WHERE SCHEMA='SQL' AND NAME='T1';
*+
*+ LOCMODE
*+ -------
*+ H      <== Now H after making the CALC key non-unique
*+
*+ 1 row processed
DROP CALC FROM T1;
*+ Status = 0        SQLSTATE = 00000
CREATE TABLE T_OWNER
       ( C1  UNSIGNED NUMERIC(4) NOT NULL );
*+ Status = 0        SQLSTATE = 00000
CREATE UNIQUE CALC ON T_OWNER ( C1 );
*+ Status = 0        SQLSTATE = 00000
CREATE CONSTRAINT C_OWNER_1
       T1 (C1)
       REFERENCES T_OWNER (C1)
       LINKED CLUSTERED;
*+ Status = 0        SQLSTATE = 00000
SELECT LOCMODE FROM SYSTEM.TABLE WHERE SCHEMA='SQL' AND NAME='T1';
*+
*+ LOCMODE
*+ -------
*+ C      <== C after defining a constraint, same as VIA in a network database
*+
*+ 1 row processed