Datacom SQL metadata for database tables and columns

Products

• Datacom

Issue/Introduction

Is there an SQL query that gets the application database metadata?

We are using Datacom Server to get the metadata that will be imported into another database.

 

Environment

Release : 15.1

Component : CA DATACOM SQL

Resolution

Datacom does not store the CREATE statements metadata for SQL. 
However the information can be obtained from other system databases.

A list of SQL tables can be obtained by querying the directory dynamic system table DIR_TABLE.

SELECT SQL_AUTHID, TABLE_SQLNAME, SQL_INTENT FROM SYSADM.DIR_TABLE   
WHERE SQL_INTENT = 'Y' OR SQL_INTENT = 'R';                          

There is also a DIR_COLUMN dynamic system table but it does not have SQL information. 
Article 221686 shows a method of getting the SQL column names. For example to get the columns for the ORDERS table: 

SELECT * FROM SYSADM.ORDERS FETCH FIRST 1 ROWS ONLY;                  

Alternatively you can query the dataditionary FIELD table to get SQL column information:

SELECT AUTHID, AGR_SQLNAME, SEQNO, SQLNAME,                           
       TYPE, LENGTH, PRECISION, SIGN,                                 
       SEMANTIC_TYPE, NULL_INDICATOR                                  
 FROM SYSADM.FIELD                                                    
 WHERE ELM_NAME = ' ' AND STATUS = 'P' AND AGR_SQLNAME = 'ORDERS'     
 ORDER BY SEQNO;