SQL query for LISTD that lists Archive Date and Time.

Products

• Disk Backup and Restore - MVS
• DISK BACKUP AND RESTORE- ADD-ON OPTIO
• DISK BACKUP AND RESTORE

Issue/Introduction

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 ?

Environment

Release: Disk™ Backup and Restore

Resolution

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
  ...