search cancel

JREPORT record selection based on date and time

book

Article ID: 21479

calendar_today

Updated On:

Products

IDMS IDMS - Database

Issue/Introduction

Journal records contain datetime stamps. This article describes how to limit the output of journal reports based on these values.

Environment

Release: All supported releases
Component: IDMS

Resolution

Culprit input statements can be used to tailor the output generated in JREPORTs. When a date and time is involved, the internal 8-byte format of the datetime stamp value must be used.

For example, assuming that you have an archive journal, and you want to run a JREPORT 008, but you only want to view the records that have a datetime stamp between 3 to 4 o'clock on the afternoon of January 27, 2012. First you must determine the internal representation of these two values. There are two ways to do this.

1. Using the online LOOK command from the ENTER NEXT TASK CODE: prompt.

LOOK EXTERNAL DATETIME=2012-01-27-15.00.00.000000
IDMSLOOK  -  OPSYS=z/OS         Release 19.0 Service pack 4   tape GJJ04I
     EXTERNAL DATETIME=2012-01-27-15.00.00.000000

         Internal datetime stamp=X'0166A80D2F000000'

LOOK EXTERNAL DATETIME=2012-01-27-16.00.00.000000
IDMSLOOK  -  OPSYS=z/OS         Release 19.0 Service pack 4   tape GJJ04I
     EXTERNAL DATETIME=2012-01-27-16.00.00.000000

         Internal datetime stamp=X'0166A80E10000000'

2. Using SQL in OCF.

   SELECT HEX(TIMESTAMP('2012-01-27-15.00.00.0000')) FROM SYSCA.SINGLETON_NULL;
   *+
   *+ HEX(FUNCTION)
   *+ -------------
   *+ 0166A80D2F000000
   *+
   *+ 1 row processed
   SELECT HEX(TIMESTAMP('2012-01-27-16.00.00.0000')) FROM SYSCA.SINGLETON_NULL;
   *+
   *+ HEX(FUNCTION)
   *+ -------------
   *+ 0166A80E10000000
   *+
   *+ 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 the JREPORT 008. The syntax below extracts the records based on UTC. In order to extract records based on local time, use the DTESTAMP field instead of GMT. The same procedure can be used for any journal reports.

JREPORT=008
SELECT WHEN GMT GE X'0166A80D2F000000' AND
* GMT LE X'0166A80E10000000'