In order to view the data set name(s) for a tablespace, you type line command DSN next to a tablespace on panel ADB21S.
If you want to know the data set name(s) holding a table, you cannot type the DSN command next to the table. You have to go to the tablespace screen (panel ADB21S) by typing line command S next to the table and then typing the DSN command. This involves going to an extra screen.
When you choose a table from panel ADB21, you arrive at panel ADB21T. The tool uses this SQL to fill in the details on that screen -
FROM SYSIBM.SYSTABLES T
WHERE T.NAME LIKE 'xxxxxxx%' AND T.CREATOR LIKE 'xxxxxxx%' FOR FETCH ONLY
The SQL has already pulled back the database and tablespace name for that panel, so 2 components of the data set name(s) have already been obtained.
|Who would benefit from this IDEA?||Any user of the tool wanting to know data set name(s) for a table|
How should it work?
On panel ADB21T, enable the use of the DSN line command against a table.
When you enter the DSN command, the tool runs this SQL -
||TP.IPREFIX||'000'||TS.INSTANCE||'.'|| CASE WHEN TP.PARTITION BETWEEN 0
AND 999 THEN 'A' WHEN TP.PARTITION BETWEEN 1000 AND 1999 THEN 'B' WHEN
TP.PARTITION BETWEEN 2000 AND 2999 THEN 'C' WHEN TP.PARTITION BETWEEN
3000 AND 3999 THEN 'D' WHEN TP.PARTITION BETWEEN 4000 AND 4096 THEN 'E'
END || CASE WHEN TP.PARTITION = 0 THEN '001' ELSE
SUBSTR(DIGITS(TP.PARTITION), 3, 3) END AS DSNAME, ' ' AS DSEXIST,
TP.PARTITION, TP.VCATNAME, HEX(TP.VCATNAME) AS XVCATNME
FROM SYSIBM.SYSTABLEPART TP, (SELECT NAME, DBNAME, CASE WHEN INSTANCE =
1 THEN '1' ELSE '2' END AS INSTANCE
WHERE DBNAME = 'xxxxxxxx' AND NAME = 'xxxxxxxx'
UNION ALL SELECT NAME, DBNAME, CASE WHEN INSTANCE = 1 THEN '2' ELSE '1'
END AS INSTANCE
WHERE DBNAME = 'xxxxxxxx' AND NAME = 'xxxxxxxx' AND CLONE = 'Y' ) AS TS
WHERE TS.DBNAME = TP.DBNAME AND TS.NAME = TP.TSNAME AND TP.DBNAME =
'xxxxxxxx' AND TP.TSNAME = 'xxxxxxxx' ORDER BY TS.INSTANCE, TP.PARTITION
FOR FETCH ONLY
There doesn't seem to be any reason why that SQL cannot also be run from panel ADB21T. It doesn't rely on any information from panel ADB21S, because the database and tablespace name will already have been obtained when building panel ADB21T.