Finder object to show what copybooks and programs contain particular fields

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

We would like a WSO Finder object that returns all copybooks and programs that contain certain fields. Can you assist in providing SQL that can be used to accomplish this task?

Answer:

The following SQL is an example of returning all copybooks and programs using a particular field.      This is only an example and can be tailored to meet your specific need. 

SELECT                                   

C.ENT_ID,'COPYBOOK',                     

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

C.LANGUAGE,                              

C.QUAL_DATASET_1                         

FROM repowner.DBX_DES1_COPYBOOK C,         

     repowner.DBX_DDL_ELEMENTS E,          

     repowner.DBX_DDL_ELEMENTS R,          

     repowner.DBX_XREF X1,                 

     repowner.DBX_XREF X2,                 

     repowner.DBX_XREF X3,                 

     repowner.DBX_XREF X4,                 

     repowner.DBX_XREF X5                  

WHERE C.ENT_ID = X1.ENT_ID AND           

      X1.ENT_TYPE = 557                  

      AND X1.ENT_ID = X2.SOURCE_ID       

      AND X2.ENT_TYPE = 570              

      AND X2.TARGET_ID = R.ENT_ID        

      AND R.ENT_ID = X3.ENT_ID           

      AND X3.ENT_TYPE = 200              

      AND X3.ENT_ID = X4.SOURCE_ID       

      AND X4.ENT_TYPE = 25434            

      AND X4.TARGET_ID = E.ENT_ID        

      AND E.ENT_ID = X5.ENT_ID           

      AND X5.ENT_TYPE = 204              

      AND (?)  

UNION                                    

SELECT                                   

P.ENT_ID, 'PROGRAM',                     

SUBSTR(P.PGM_NAME,1,8) AS CONTAINER,     

P.LANGUAGE,                              

P.QUAL_DATASET_1                         

FROM repowner.DBX_IMS_PROGRAM P,           

     repowner.DBX_DDL_ELEMENTS E,          

     repowner.DBX_XREF X1,                 

     repowner.DBX_XREF X2,               

     repowner.DBX_XREF X3                

WHERE P.ENT_ID = X1.ENT_ID AND         

      X1.ENT_TYPE = 151                

      AND X1.ENT_ID = X2.SOURCE_ID     

      AND X2.ENT_TYPE = 217            

      AND X2.TARGET_ID = E.ENT_ID      

      AND E.ENT_ID = X3.ENT_ID         

      AND X3.ENT_TYPE = 204            

      AND (?)

 

;                                      

note. 

Change repowner to your repository creator