How to find the DSN and VOLSER for Area PMF in DBID 001 using the CA Datacom Dynamic System Tables (DST).

Products

• Datacom
• DATACOM - AD

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