How to create a listing of all fields in all databases

Document ID : KB000124936
Last Modified Date : 18/01/2019
Show Technical Document Details
Introduction:
When researching field usage or the impact of field changes within a company, it is helpful to have a comprehensive overview of the data definitions in use. For example, you might want to know how the customer number is defined in every table, or you might want to know what the actual field name is for every date field.

Having this information in one place can be quite useful for this and other research.
Instructions:
There are many different ways to retrieve basic information about every field in every table within the CA Datacom Datadictionary. Here are two easy and common jobs to run.

1. List all the fields in the Datadictionary, with their basic attributes. In addition, display a hierarchy of tables within each database, and in reverse, list all the tables in name sequence, and show the database to which they are associated.
/*
//  JCLLIB ORDER=<<your Datacom CUSPROC>>
//*
//* - - - - - -C- - - - - - - - - - - - - - - - - - - - - - - - - - -7-
//DDUTL     EXEC PGM=DDUTILTY,REGION=0M 
//          INCLUDE MEMBER=B15STLIB     
//          INCLUDE MEMBER=B15DDOUT     
//SYSIN        DD *                                    
+USR DATACOM-INSTALL,NEWUSER                           
-COM ----------------------------------------------    
-COM PRODUCE FIELD REPORT OF ALL FIELDS                
-RPT FIELD,TABLE,ALL(PROD)                             
-END                                                   
-COM ----------------------------------------------    
-COM PRODUCE REPORT OF ALL DATABASES AND THEIR TABLES  
-DEF PATH,PATH1                                        
-DEF TRACE,DATABASE.AREA,$INTERNAL,SKIP                
-DEF TRACE,AREA.TABLE,$INTERNAL                        
-END                                                   
-RPT START,DATABASE,ALL(PROD),PATH1                    
-RPT INDENT                                            
-END                                                   
-COM ----------------------------------------------    
-COM PRODUCE REPORT OF ALL TABLES WITH THEIR DATABASE  
-DEF PATH,PATH2                                        
-DEF TRACE,TABLE.AREA,$INTERNAL,SKIP                   
-DEF TRACE,AREA.DATABASE,$INTERNAL                     
-END                                                   
-RPT START,TABLE,ALL(PROD),PATH2                       
-RPT INDENT                                            
-END                                                   
/*

2. Produce a display of COBOL data elements in a single file that can be printed, or can be used to create a Copybook library, with one table in each PDS member (this JCL uses SYSOUT for the listing).

//*
//  JCLLIB ORDER=<<your Datacom CUSPROC>>
//*
//* - - - - - -C- - - - - - - - - - - - - - - - - - - - - - - - - - -7-
//* RUN DBSQLPR TO GENERATE DDUTILTY CONTROL CARDS FOR COPYBOOKS
//DBSQLPR  EXEC PGM=DBSQLPR,REGION=0M,
//             PARM='NOTYPE,NOECHO,NOCOLHDR,AUTHID=SYSADM,PRTWIDTH=80'
//         INCLUDE MEMBER=B15STLIB
//         INCLUDE MEMBER=B15DDOUT
//OPTIONS  DD  DUMMY
//STDOUT   DD  DISP=(,PASS),UNIT=SYSDA,LRECL=80,BLKSIZE=0,RECFM=FB,
//             SPACE=(CYL,(1,1))
//SYSIN    DD  *
SELECT   '-UTL COPY,TABLE,' || TRIM(DRT.OCCURRENCE) ||
         '(PROD),T' || SUBSTR(DIGITS(DRT.DBID),2,4) || DRT.TABLE_NAME
    FROM SYSADM.DIR_TABLE DRT  WHERE DRT.STATUS = 'P' ;
/*
//* - - - - - -C- - - - - - - - - - - - - - - - - - - - - - - - - - -7-
//* IEBGENER TO ELIMINATE THE BLANK COLUMN 1 FROM ABOVE OUTPUT
//GENER    EXEC  PGM=IEBGENER,REGION=0M
//SYSIN    DD   *
 GENERATE MAXFLDS=1
 RECORD FIELD=(80,2,,1)
/*
//SYSPRINT DD  SYSOUT=*
//SYSUT1   DD  DSN=*.DBSQLPR.STDOUT,DISP=(OLD,DELETE)
//SYSUT2   DD  DISP=(,PASS),UNIT=SYSDA,LRECL=80,BLKSIZE=0,RECFM=FB,
//             SPACE=(CYL,(1,1))
//*
//* - - - - - -C- - - - - - - - - - - - - - - - - - - - - - - - - - -7-
//* DDUTILTY STEP TO PRODUCE COBOL COPYBOOK MEMBERS FOR ALL TABLES
//COPYBOOK EXEC PGM=DDUTILTY
//         INCLUDE MEMBER=B15STLIB
//         INCLUDE MEMBER=B15DDOUT
//SYSPUNCH DD  SYSOUT=*  <<-- CHANGE TO PS FILE FOR IEBUPDTE LOAD
//SYSIN    DD  *
-USR  DATACOM-INSTALL,NEWUSER
-UTL LIBRARY,OS
-UTL LANGUAGE,COBOL85
//         DD  DSN=*.GENER.SYSUT2,DISP=(OLD,DELETE)
//*


Additional Information:
As always, please contact CA Technologies support for CA Datacom if you have further questions.