I have two queries below. One table has one row for each
the other table has many rows for each one in the first table. Can someone show me how I can join these tables and get the results I'm looking for?
SELECT A.NAME AS TABLE_NAME ,
',',
B.NAME AS TBL_SPACE,
',',
B.DBNAME AS DB_NAME,
',',
B.BPOOL AS TBL_POOL,
',',
C.STORNAME AS TBL_STOG,
','
FROM SYSIBM.SYSTABLES A,
SYSIBM.SYSTABLESPACE B,
SYSIBM.SYSTABLEPART C
WHERE A.TSNAME = B.NAME
AND A.DBNAME = B.DBNAME
AND C.TSNAME = A.TSNAME
AND C.DBNAME = A.DBNAME
AND A.DBNAME NOT LIKE 'DSN%'
AND A.DBNAME NOT LIKE 'PTDB%'
Query two:
SELECT DISTINCT A.NAME , A.TBNAME, B.STORNAME, A.BPOOL
FROM SYSIBM.SYSINDEXES A,
SYSIBM.SYSINDEXPART B
WHERE A.NAME = B.IXNAME
AND A.DBNAME NOT LIKE 'BMC%'
AND A.DBNAME NOT LIKE 'DSN%'
AND A.DBNAME NOT LIKE 'PTDB%'
ORDER BY A.BPOOL, A.NAME
Thanks so much.
I am not sure what you are looking for with these 2 SQLs. If in your enviroment you don't have partitioned table spaces you are fine, but if you do have them, then the first SQL would bring multiple rows per table. Assuming you don't have partitioned TSs, then the following SQL could be a starting point for you:
SELECT A.NAME AS TABLE_NAME ,',',
B.NAME AS TBL_SPACE,',',
B.DBNAME AS DB_NAME,',',
B.BPOOL AS TBL_POOL,',',
C.STORNAME AS TBL_STOG,',',
IX.NAME , IX.TBNAME, IX.STORNAME, IX.BPOOL
FROM
SYSIBM.SYSTABLES A,
SYSIBM.SYSTABLESPACE B,
SYSIBM.SYSTABLEPART C
INNER JOIN
(SELECT DISTINCT A.NAME , A.TBNAME, B.STORNAME, A.BPOOL, A.TBCREATOR
FROM SYSIBM.SYSINDEXES A,
SYSIBM.SYSINDEXPART B
WHERE A.NAME = B.IXNAME
AND A.DBNAME NOT LIKE 'BMC%'
AND A.DBNAME NOT LIKE 'DSN%'
AND A.DBNAME NOT LIKE 'PTDB%') IX
ON A.NAME = IX.TBNAME AND A.CREATOR = IX.TBCREATOR
WHERE A.TSNAME = B.NAME
AND A.DBNAME = B.DBNAME
AND C.TSNAME = A.TSNAME
AND C.DBNAME = A.DBNAME
AND A.DBNAME NOT LIKE 'DSN%'
AND A.DBNAME NOT LIKE 'PTDB%'
ORDER BY IX.BPOOL, IX.NAME
Hope this helps
regards
Paulo
Discuss This Question: 1  Reply