Using SQL to limit JREPORT output by date and time.

Document ID : KB000021479
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction:

This Knowledge Document describes how to use SQL to identify the values needed to limit the output of a JREPORT by date and time.

Instructions:

Culprit input statements can be used to tailor the output generated in JREPORTs.

When a date and time is involved, the internal format of the date-timestamp value must be used.

For example, let's assume that you have an archive journal, and you want to run a JREPORT-002, but you only want to view the transactions that ended (or aborted) within a certain time frame, say 3 to 4 o'clock on the afternoon of January 27, 2012.

Using SQL, you can determine the internal hexadecimal values of those timestamps:-

   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-002:-

   JREPORT=002   
   SELECT WHEN TYPE EQ ('ENDJ' 'ABRT') AND   
   * DTESTAMP GE X'0166A80D2F000000' AND   
   * DTESTAMP LE X'0166A80E10000000'  

Additional Information:

CA IDMS SQL Reference Guide Appendix E "SYSTEM Tables and SYSCA Views", "SYSCA Objects", "Example", "SYSCA Pseudo Table SINGLETON_NULL".

CA IDMS SQL Reference Guide Chapter 5 "Functions", "Scalar Function", "Expansion of Scalar-function".

CA IDMS Reports Guide Chapter 8 "CA IDMS/DB Journal Reports--JREPORTS".