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

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

Clients have requested a method to list all non-sql network schema record names using SQL.

Environment

IDMS - All Supported Releases

Resolution

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 

Additional Information

SQL Reference