book
Article ID: 91886
calendar_today
Updated On:
Issue/Introduction
SQL practice....against Dynamic System Tables (DST).
How to find the DSN and VOLSER for Area PMF in DBID 001 using the CA Datacom Dynamic System Tables (DST).
Environment
z/os, CA Datacom/DB SQL 14.0 and above
Resolution
Solution 1:
SELECT T1.DBID, T1.AREA_NAME, T1.DATASET_NAME, T1.DEVICE, T2.VOLSER
FROM SYSADM.DIR_DATASET T1, SYSADM.DIR_VOLUMES T2
WHERE (T1.DBID = 1
AND T1.DBID = T2.DBID
AND T1.AREA_NAME = T2.AREA_NAME
AND T1.AREA_NAME = 'PMF');
Result 1:
DBID, <1>
AREA_NAME, <PMF>
DATASET_NAME, < P.SY.DBMUFDV.PMF001 >
DEVICE, < 3390 >
VOLSER, <DPR055>
Solution 2 using a UNION:
SELECT DATASET_NAME
FROM SYSADM.DIR_DATASET
WHERE (DBID = 1
AND AREA_NAME = 'PMF')
UNION
SELECT VOLSER
FROM SYSADM.DIR_VOLUMES
WHERE (DBID = 1
AND AREA_NAME = 'PMF');
Result 2:
DATASET_NAME, < DPR055 >
DATASET_NAME, < P.SY.DBMUFDV.PMF001 >
Solution 3 using a Left outer JOIN:
SELECT T1.DATASET_NAME, T2.VOLSER
FROM SYSADM.DIR_DATASET T1, SYSADM.DIR_VOLUMES T2
WHERE (T1.DBID = 1
AND T2.DBID = 1
AND T1.AREA_NAME = 'PMF'
AND T2.AREA_NAME = 'PMF');
Result 3:
DATASET_NAME, < P.SY.DBMUFDV.PMF001 >
VOLSER, <DPR055>
Additional Information
More on Dynamic System Tables can be found at the following URL:
https://docops.ca.com/ca-datacom/15-1/en/reference/system-tables-reference/dynamic-system-tables