How to list non-sql network schema record element names using a SQL query?

Document ID : KB000054182
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

How to list non-sql network schema record element names using a SQL query?

Answer:

Clients have requested a method to list all non-sql network schema record names using SQL. Currently, there are no reports that provide this function. Instead if the site has the SQL option; there are a series of steps that can accomplish this request.

Here the steps on how to list non-sql "column names" for a record online via OCF or in batch using IDMSBCF:

  1. Set the dictionary via a DCUF command:
     DCUF SET DICTNAME SYSDICT 
  2. Create a schema for the non-sql schema
     CREATE SCHEMA DAPSQL FOR NONSQL SCHEMA SYSDICT.EMPSCHM VERSION 100 
    DBNAME EMPDEMO;
  3. Next create a generic sql schema
     CREATE SCHEMA DAPVIEW; 
  4. This step then creates the dictionary structure in the catlog
     CREATE VIEW DAPVIEW.RECVIEW AS 
    SELECT * FROM DAPSQL.EMPLOYEE; (Employee is the record name)
  5. Lastly, do the select to get the column names
     SELECT * FROM SYSTEM.COLUMN WHERE SCHEMA = 'DAPVIEW'; 




    *+ NAME NUMBER SCHEMA
    *+ ---- ------- -------
    *+ BIRTH_DAY_0415 20 DAPVIEW
    *+
    *+ BIRTH_MONTH_0415 19 DAPVIEW
    *+
    *+ BIRTH_YEAR_0415 18 DAPVIEW
    *+
    *+ EMP_CITY_0415 5 DAPVIEW
    *+
    *+ EMP_FIRST_NAME_0415 2 DAPVIEW
    *+
    *+ EMP_ID_0415 1 DAPVIEW
    References:

    CA IDMS Database SQL Option Reference Guide,
    9.0 Accessing Non-SQL Defined Databases;
    9.2 SQL Schema Considerations;
    9.3 SQL DML Statement Operating on Non-SQL Defined Records
    9.4 SQL Access to Non-SQL Databases
    9.4.1 SQL Schemas for Non-SQL Databases
    Appendix E.31 System Tables, SYSCA Views