Search Object for PROGRAM(s) in WSO

Document ID : KB000108555
Last Modified Date : 03/08/2018
Show Technical Document Details
Question:
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
Answer:
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.