Search Object for DATABASE in WSO

Products

• Repository

Issue/Introduction

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

Environment

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

Resolution

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.