Search Object for DATABASE in WSO

Document ID : KB000108554
Last Modified Date : 02/08/2018
Show Technical Document Details
Question:
We need help with creating SQL to be used in WSO that will eliminate many nested subordinate searches. Our users are looking to provide database name filtering and to get a result set back that shows everything about a database in one result set.
Database -> Tablespace -> Table or views -> programs
Answer:
The following SQL will return Database Name, Tablespace(s), Table(s) and Program(s) using Database Name as the filtering criteria. 

SELECT CHAR(D.DATABASE_NAME,8) AS DATABASE, 
       CHAR(T.TABLESPACE_NAME,8) AS TBSPACE,
       CHAR(TBL.TB_NAME,30) AS TABLE,       
       CHAR(P.PGM_NAME,8) AS PROGRAM        
FROM repowner.DBX_DB2_DATABASE D,             
     repowner.DBX_DB2_TABLESPACE T,           
     repowner.DBX_DB2_TABLES TBL,             
     repowner.DBX_IMS_PROGRAM P,              
     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                     
WHERE (?)             
AND D.ENT_ID = X1.ENT_ID                    
AND X1.ENT_TYPE = 105                       
AND X1.ENT_ID = X2.TARGET_ID                
AND X2.ENT_TYPE = 109                       
AND X2.SOURCE_ID = T.ENT_ID                 
AND T.ENT_ID = X3.ENT_ID                    
AND X3.ENT_TYPE = 129                       
AND X3.ENT_ID = X4.TARGET_ID                
AND X4.ENT_TYPE = 110                       
AND X4.SOURCE_ID = TBL.ENT_ID               
AND TBL.ENT_ID = X5.ENT_ID                  
AND X5.ENT_TYPE = 126                       
AND X5.ENT_ID = X6.TARGET_ID                
AND X6.ENT_TYPE = 9824                      
AND X6.SOURCE_ID = P.ENT_ID                 
AND P.ENT_ID = X7.ENT_ID                    
AND X7.ENT_TYPE = 151                       
;                                           

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