SQL script to generate PRINT INDEX syntax

Products

• IDMS - Database

Issue/Introduction

This document contains an SQL script for generating PRINT INDEX syntax based on a supplied subschema and dbname.

Environment

Release : Any supported release.
Component : IDMS.

Resolution

This script assumes the following:

APPLDICT is the name of the dictionary/catalog which contains the schema/subschema definitions.
APPLSCHM is the name of an SQL schema which describes the IDMSNTWK network schema.

Example:

CREATE SCHEMA APPLSCHM
    FOR NONSQL SCHEMA SYSDIRL.IDMSNTWK VERSION 1
        DBNAME APPLDICT
    ;

The script is as follows.

You must populate the PRINTINDEX_DBS temporary table with a row for each subschema and dbname combination for which you want to generate the syntax.
You will need an INSERT statement for each subschema/dbname pairing (the ones in green below).

In this example, syntax will be generated for every index in subschema EMPSS01 for both dbnames EMPDEMO and EMPDEMO1.
The output of the last SELECT will be the required syntax.

CONNECT TO APPLDICT;
SET SESSION CURRENT SCHEMA APPLSCHM;
CREATE TEMPORARY TABLE PRINTINDEX_DBS
  ( SUBSCHEMA  CHAR(8) NOT NULL,
    DBNAME     CHAR(8) NOT NULL
  );
CREATE TEMPORARY TABLE SYSIPT
  ( SETNAME    CHAR(32) NOT NULL,
    DBNAME     CHAR(8) NOT NULL,
    SEQUENCE   SMALLINT NOT NULL,
    TXT        CHAR(60)
  );
INSERT INTO PRINTINDEX_DBS VALUES ( 'EMPSS01 ', 'EMPDEMO ' );
INSERT INTO PRINTINDEX_DBS VALUES ( 'EMPSS01 ', 'EMPDEMO1' );
INSERT INTO SYSIPT
SELECT SET_NAM_034, DBNAME, 1, 'PRINT INDEX ' || SET_NAM_034
FROM PRINTINDEX_DBS
INNER JOIN "SSOR-034" ON SS_NAM_034=SUBSCHEMA
INNER JOIN "SOR-046" ON "SOR-SSOR"
WHERE SET_MODE_046=21;
INSERT INTO SYSIPT
SELECT SET_NAM_034, DBNAME, 2,
  'DBNAME ' || DBNAME || ' USING ' || SUBSCHEMA || ' SUMMARY;'
FROM PRINTINDEX_DBS
INNER JOIN "SSOR-034" ON SS_NAM_034=SUBSCHEMA
INNER JOIN "SOR-046" ON "SOR-SSOR"
WHERE SET_MODE_046=21;
SELECT TXT FROM SYSIPT ORDER BY DBNAME, SETNAME, SEQUENCE;