BCF/OCF record list for nonsql schema

Document ID : KB000115878
Last Modified Date : 26/09/2018
Show Technical Document Details
Question:
How can I get a record/table list for an SQL schema that references a non-SQL schema?
 
Answer:
When you create an SQL schema for a non-SQL schema, IDMS does not create table definitions in the SQL catalog for all 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 you can issue the command SELECT * FROM SYSCA.ACCESSIBLE_TABLES
This will display the list of records in the  SQL schema (that we created pointing to our non-sql schema).
The SYSCA.ACCESSIBLE_TABLES view, joins the dictionary and catalog records.

This depends on the DICTNAME you specify for the non-SQL schema when the SQL schema is created. In our test system we have 2 dbnames, CJDTST and SYSDICT.

The dbname definitions for the 2 dictionaries are similar:
DBNAME CJDTST 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
 
If you go into OCF:
CONNECT TO CJDTST;
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.
 
If you then :
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 you won't be able to see schemas defined with a different DICTNAME than the one you are currently connected to.  You want to be connected to the DICTNAME that you named for the nonsql schema location.  This DICTNAME should contain the dictionary area DDLDML and the catalog areas DDLCAT/DDLCATX/DDLCATLOD.