Finder Object to show fields used in copybook

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

We would like a WSO Finder object that returns all fields used by a particular copybook.   Can you assist in providing SQL that can be used to accomplish this task?

Answer:

The following SQL is an example of returning all fields used in a copybook.    This particular SQL statement returns the copybook name and all elements contained therein.   This is only an example and can be tailored to meet your specific need. 

SELECT E.ENT_ID,                        

SUBSTR(C.COPYBOOK_NAME,1,8) AS COPYBOOK,

C.LANGUAGE,                             

SUBSTR(E.ELEMENT_NAME,1,15) AS FIELD,   

E.DATA_TYPE, E.LENGTH, I.SEQ_NUM,       

C.QUAL_DATASET_1 AS DATASET             

FROM  repowner.DBX_DES1_COPYBOOK C,       

      repowner.DBX_DDL_ELEMENTS E,        

      repowner.DBX_DDL_ELEMENTS R,        

      repowner.DBX_PRI_STR_MAPS I,        

      repowner.DBX_XREF X1,               

      repowner.DBX_XREF X2,               

      repowner.DBX_XREF X3,               

      repowner.DBX_XREF X4,               

      repowner.DBX_XREF X5                

WHERE E.ENT_ID = X1.ENT_ID AND          

      X1.ENT_TYPE = 204                 

      AND X1.ENT_ID = X2.TARGET_ID      

      AND X2.ENT_TYPE = 25434           

      AND X2.ENT_ID = I.ENT_ID          

      AND X2.SOURCE_ID = R.ENT_ID       

      AND R.ENT_ID = X3.ENT_ID          

      AND X3.ENT_TYPE = 200             

      AND X3.ENT_ID = X4.TARGET_ID      

      AND X4.ENT_TYPE = 570             

      AND X4.SOURCE_ID = C.ENT_ID       

      AND C.ENT_ID = X5.ENT_ID          

      AND X5.ENT_TYPE = 557             

      AND C.COPYBOOK_NAME = 'ccc'  

      AND C.STATUS = 'sss'         

      ORDER BY I.SEQ_NUM                

;                                       

 

note. 

Change repowner to your repository creator 

Change 'ccc' to your copybook name

Change 'sss' to your status