What is the corresponding SQL query command for a LISTD that lists Archive Date and Time for Archive and Backup entries ?

book

Article ID: 40659

calendar_today

Updated On:

Products

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

Issue/Introduction

Question:

What is the corresponding SQL query command for the CA Disk Files Data Base for a LISTD that lists Archive Date and Time for Archive and Backup entries ?

 

Answer:

When CA 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 CA 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

  ...

 

Additional Information:

As you can see in the above sample, while it is relatively simple to display the hexa-decimal contents of the Archve Time field,  it is much more difficult to display the contents of the date field DSNARCDT. All date fields used in the CA Disk DSNINDEX entries consist of two parts. The first byte contains the number of years since 1900, Byte 2 and 3 contain the day of the year number.

Please see also the corresponding idea on our Datacom community web-site under  https://communities.ca.com/ideas/235718254 and consider to vote on it.  

The idea is to provide a scalar function ‘CDB’ (Convert Discontinuous Binary) in Datacom SQL that would convert such CA Disk date fields into the default ISO DATE format.

The SQL query command would then just look like:

SELECT DSNDSNAM      as Data_Set_Name,              

       DSNARVOL      as Archive_Key,                

       HEX(DSNATIME) as Archive_Time,               

       CDB(DSNARCDT) as Archive_Date

  FROM CADISK.dsnindex_660

  WHERE DSNDSNAM LIKE 'ABC.%'

  ORDER BY DSNDSNAM;

 

Environment

Release: SMDI3900200-12.5-Disk-Backup and Restore-for z/OS
Component: