CA IDMS JREPORT Datetime Selection

book

Article ID: 140429

calendar_today

Updated On:

Products

CA IDMS CA IDMS - Database CA IDMS - ADS

Issue/Introduction

It may be desirable to run a Journal Report (JReport) which only selects journal records within a specific date-time range. There are mul;tiple date fields in the journal record layout, so it is confusing as to which one to use and what format the date should be in.  

 

This article provides an example of the syntax that should be used to pull all of the journal record types for a specific local date-time range.

Environment

Release : 19.0

Component : CA IDMS

Resolution

The first step in running a date/time based query against the journal records is to obtain a hex representation of the desired date(s). This can be done using a query like this one: 


SELECT
HEX(TIMESTAMP('2019-01-27-15.00.00.0000')) FROM SYSCA.SINGLETON_NULL;  


   *+  

   *+ HEX(FUNCTION)  

   *+ -------------  

   *+ 0167E7AD2F000000   

   *+  

   *+ 1 row processed  

   

(Note: SYSCA.SINGLETON_NULL is a dummy SQL table with one table
and one column, with the value NULL. Its purpose is to be used in situations
such as this where SQL is needed to generate certain values but no underlying
data tables need to be referenced.)


You can then use these values in the culprit input to SELECT in any JReport. In order to select all journal record types for a journal report based on a timestamp, the GMT field should be used in the comparison.   Here's an example of that syntax.  


SELECT
* GMT GE X'0167EF0D2F000000' AND 

* GMT LE X'01680BAD2F000000' 
 

This sample will select all journal records between 1/27/19 and 3/27/19, 3:00 pm both days. 

Additional Information

Knowledge article 21479 demonstrates this same principle against the DTESTAMP journal record field:

https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=21479&_ga=2.176787372.822652900.1574225083-82686564.1574225083

 

Information and instructions for submitting journal reports can be found here: 

http://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-mainframe-software/database-management/ca-idms/19-0/reporting/ca-idms-db-journal-reports-jreports.html