Search Object for PROGRAM(s) in WSO

Products

• Repository

Issue/Introduction

We need help with creating search object to be used in WSO that will eliminate many nested subordinate searches. Our users are looking to provide program name filtering and to get a result set back that shows everything about a program in one result set
Programs -> Calling programs -> Called programs -> IMS DB -> IMS PSB

Environment

Release: DATASH00200-7.2-Repository-Webstation Option
Component:

Resolution

The following SQL will return Program Name, Called Program(s), Calling Program(s), DBD(s) and PSB(s) using Program Name as the filtering criteria.

SELECT DISTINCT CHAR(PX.PGM_NAME,12) AS PROGRAM,
       CHAR(C.PGM_NAME,12) AS "CALLING PGM",    
       CHAR(CD.PGM_NAME,12) AS "CALLED PGM",    
       CHAR(I.IMS_DB_NAME,12) AS DBD,           
       CHAR(P.PSB_NAME,12) AS PSB               
FROM repowner.DBX_IMS_PROGRAM PX,                 
     repowner.DBX_IMS_PROGRAM C,                  
     repowner.DBX_IMS_PROGRAM CD,                 
     repowner.DBX_IMS_IMS_DB I,                   
     repowner.DBX_IMS_PSB P,                      
     repowner.DBX_IMS_PCB B,                      
     repowner.DBX_XREF X1,                        
     repowner.DBX_XREF X2,                        
     repowner.DBX_XREF X3,                        
     repowner.DBX_XREF X4,                        
     repowner.DBX_XREF X5,                        
     repowner.DBX_XREF X6,                        
     repowner.DBX_XREF X7,                        
     repowner.DBX_XREF X8,                        
     repowner.DBX_XREF X9,                        
     repowner.DBX_XREF XA,                        
     repowner.DBX_XREF XB                         
WHERE (?)                      
AND PX.ENT_ID = X1.ENT_ID                       
AND X1.ENT_TYPE = 151                           
AND X1.ENT_ID = X2.TARGET_ID                    
AND X2.ENT_TYPE = 218                           
AND X2.SOURCE_ID = C.ENT_ID                     
AND C.ENT_ID = X3.ENT_ID                        
AND X3.ENT_TYPE = 151                           
AND X1.ENT_ID = X4.SOURCE_ID                    
AND X4.ENT_TYPE = 218                           
AND X4.TARGET_ID = CD.ENT_ID                    
AND CD.ENT_ID = X5.ENT_ID                       
AND X5.ENT_TYPE = 151                           
AND X1.ENT_ID = X6.TARGET_ID                    
AND X6.ENT_TYPE = 162         
AND X6.SOURCE_ID = I.ENT_ID   
AND I.ENT_ID = X7.ENT_ID      
AND X7.ENT_TYPE = 152         
AND X7.ENT_ID = X8.TARGET_ID  
AND X8.ENT_TYPE = 175         
AND X8.SOURCE_ID = B.ENT_ID   
AND B.ENT_ID = X9.ENT_ID      
AND X9.ENT_TYPE = 156         
AND X9.ENT_ID = XA.TARGET_ID  
AND XA.ENT_TYPE = 161         
AND XA.SOURCE_ID = P.ENT_ID   
AND P.ENT_ID = XB.ENT_ID      
AND XB.ENT_TYPE = 157         
;                           

Note. Change all occurrences of repowner to your Repository creator.