What is the corresponding SQL query command for the Disk™ Backup and Restore Files Data Base for a LISTD that lists Archive Date and Time ?
Release: Disk™ Backup and Restore
When Disk is set up with the FILES Data Base being used, then it is possible to set up a corresponding SQL query command onto the DSNINDEX part of the FILES Data Base instead of a LISTD and execute it via the DATACOM batch utility DBSQLPR. A general sample for this with the JCL is provided in member DBQDSI of the Disk CCUWJCL library.
Here a comprehensive sample for a SQL query command that lists only Data Set Name, Archive Key, Archive Date and Time for all Archive and Backup entries of all data sets ABC.**
The corresponding LISTD for this would be:
LISTD DSN=ABC./,DUPLICATES,FIELDS=(DSNAM,ARKEY,ATIME,ARCDT)
The corresponding SQL query command would be:
SELECT DSNDSNAM as Data_Set_Name,
DSNARVOL as Archive_Key,
HEX(DSNATIME) as Archive_Time,
DSNARCDT as Archive_Date_as_stored,
case when cast(substr(DSNARCDT,1,1) as integer without conversion) = 0
then ' '
else
Char(Date(substr(digits(cast(substr(DSNARCDT,1,1) as integer
without conversion) + 1900),7,4)
||
substr(digits(cast(substr(DSNARCDT,2,2) as smallint
without conversion)),3,3)),ISO)
end
as Archive_Date
FROM CADISK.dsnindex_660
WHERE DSNDSNAM LIKE 'ABC.%'
ORDER BY DSNDSNAM;
Sample SQL report:
DATA_SET_NAME ARCHIVE_KEY ARCHIVE_TIME ARCHIVE_DATE_AS_STORED ARCHIVE_DATE
________________________ ___________ ____________ ______________________ ____________
ABC.SMS.#1 @9996F 1438 740037 2016-02-24
ABC.SMS.#1 @9997O 1401 73000C 2015-01-12
ABC.SMS.#2 @999Y2 1438 740037 2016-02-24
...