The RelationalSystemContents relationship query returns the names of all DB2/390 objects, all DB2 UDB objects or all Oracle objects which are contained in the specified Relational Database System.
View type: DAQRelationalSystem
Modifications:
The query uses a SQL UNION clause to provide a more compact and readable table. In order to change the search criteria, you must change it for each subselect statement in the UNION. Change the search criteria of the relationalSystem using the = (equals), IN or LIKE elements in the SQL WHERE clause. The WHERE clause may be changed to locate the Relational System which contains a specified relational database object by specifying the object name for the appropriate attribute.
The name format for the relational objects is specified in DataAtlas User's Guide.
#MODIFIED#
#VERSION#
#SQL STATEMENT#
--DA_RelationalSystemContents
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.creatorID as relatedObject,
'Creator/Schema' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.creatorID=p.baseName AND
p.partType='DSRRCollection'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.db2csTable..db2csTable as relatedObject,
'DB2 UDB Table' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.db2csTable..db2csTable=p.baseName AND
p.partType='DSRORDTable'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.db2csView..db2csView as relatedObject,
'DB2 UDB View' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.db2csView..db2csView=p.baseName AND
p.partType='DSRORDView'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.db2csIndex..db2csIndex as relatedObject,
'DB2 UDB Index' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.db2csIndex..db2csIndex=p.baseName AND
p.partType='DSRORDIndex'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.db2csTablespace..db2csTablespace as relatedObject,
'DB2 UDB Tablespace' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.db2csTablespace..db2csTablespace=p.baseName AND
p.partType='DSRORDTablespace'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.db2csDatabase..db2csDatabase as relatedObject,
'DB2 UDB Database' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.db2csDatabase..db2csDatabase=p.baseName AND
p.partType='DSRORDDatabase'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.db2390Table..db2390Table as relatedObject,
'DB2/390 Table' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.db2390Table..db2390Table=p.baseName AND
p.partType='DSRMRDTable'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.db2390View..db2390View as relatedObject,
'DB2/390 View' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.db2390View..db2390View=p.baseName AND
p.partType='DSRMRDView'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.db2390Index..db2390Index as relatedObject,
'DB2/390 Index' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.db2390Index..db2390Index=p.baseName AND
p.partType='DSRMRDIndex'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.db2390Tablespace..db2390Tablespace as relatedObject,
'DB2/390 Tablespace' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.db2390Tablespace..db2390Tablespace=p.baseName AND
p.partType='DSRMRDTablespace'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.db2390Database..db2390Database as relatedObject,
'DB2/390 Database' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.db2390Database..db2390Database=p.baseName AND
p.partType='DSRMRDDatabase'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.db2390AliasSynonym..db2390AliasOrSynonym as relatedObject,
'DB2/390 Alias/Synonym' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.db2390AliasSynonym..db2390AliasOrSynonym=p.baseName AND
p.partType='DSRAlternateName'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.db2390StorageGroup..db2390StorageGroup as relatedObject,
'DB2/390 Storage Group' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.db2390StorageGroup..db2390StorageGroup=p.baseName AND
p.partType='DSRMRDStorageGroup'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.db2390Bufferpool..db2390Bufferpool as relatedObject,
'DB2/390 Bufferpool' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.db2390Bufferpool..db2390Bufferpool=p.baseName AND
p.partType='DSRMRDBufferpool'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.oracleTable..oracleTable as relatedObject,
'Oracle Table' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.oracleTable..oracleTable=p.baseName AND
p.partType='DSROracleTable'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.oracleView..oracleView as relatedObject,
'Oracle View' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.oracleView..oracleView=p.baseName AND
p.partType='DSROracleView'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.oracleIndex..oracleIndex as relatedObject,
'OracleIndex' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.oracleIndex..oracleIndex=p.baseName AND
p.partType='DSROracleIndex'
UNION
SELECT DISTINCT
r.relationalSystem,
r.rdbProductName as product,
r.rdbProductVersion as version,
r.oracleTablespace..oracleTablespace as relatedObject,
'Oracle Tablespace' as partType,
p.lastUpdate,
p.addDate
FROM OUTER
DAQRelationalSystem r,
PartView p
WHERE
r.relationalSystem like '<>%' AND
r.oracleTablespace..oracleTablespace=p.baseName AND
