BCF/OCF record list for nonsql schema

Products

• IDMS
• IDMS - Database

Issue/Introduction

This document describes how to generate a record/table list for an SQL schema that references a non-SQL schema. 

Environment

Release: All supported releases.
Component: SQL Option.

Resolution

When an SQL schema is created for a non-SQL schema, IDMS does not create table definitions in the SQL catalog for the records in the non-SQL schema. It simply references the non-SQL schema in the DDLDML area to access the definitions. So, to see the network records within OCF/BCF, issue the command SELECT * FROM SYSCA.ACCESSIBLE_TABLES. This will display the list of records in the SQL schema (that was created pointing to the non-sql schema).

The SYSCA.ACCESSIBLE_TABLES view joins the dictionary and catalog records.

This depends on the DICTNAME specified for the non-SQL schema when the SQL schema is created. For example, consider that there are two dbnames, XXXTST and SYSDICT. The dbname definitions for the two dictionaries are similar:

DBNAME XXXTST MATCH ON SUBSCHEMA IS OPTIONAL
SEGMENT APPLDICT 24 BIND count
SEGMENT EMPDEMO 0 BIND count
SEGMENT RRVSAMSG 0 BIND count
SEGMENT SYSMSG 0 BIND count
SEGMENT SYSSQL 29 BIND count
 
DBNAME SYSDICT MATCH ON SUBSCHEMA IS OPTIONAL
SEGMENT APPLDICT 10 BIND count
SEGMENT EMPDEMO 0 BIND count
SEGMENT SYSMSG 0 BIND count
SEGMENT SYSSQL 42 BIND count
 
Go into OCF:

CONNECT TO XXXTST;
CREATE SCHEMA EMPSCHM5
FOR NONSQL SCHEMA SYSDICT.EMPSCHM VERSION 100
DBNAME EMPDEMO;
SELECT * FROM SYSCA.ACCESSIBLE_TABLES WHERE SCHEMA='EMPSCHM5';

It does not show the EMPSCHM5 schema.
 
But then do this:

CONNECT TO SYSDICT;
SELECT * FROM SYSCA.ACCESSIBLE_TABLES WHERE SCHEMA='EMPSCHM5';

The report displays EMPSCHM5 schema with the list of record names.
 
The way the SYSCA.ACCESSIBLE_TABLES view is defined, the schemas defined with a different DICTNAME than the one currently connected to will not be visible. The session must be connected to the DICTNAME that was named for the nonsql schema location. This DICTNAME should contain the dictionary area DDLDML and the catalog areas DDLLCAT/DDLCATX/DDLCATLOD.