How can I dynamically pass the result of TRUNC_TIMESTAMP of any kind in my JCL steps?

Question

I am looking to create a JCL stream that will do the following:

Step 1: Run a instream DB2

Param1 = SELECT TRUNC_TIMESTAMP(CURRENT TIMESTAMP,'MONTH') 
            FROM SYSIBM.SYSDUMMY1;`

Step 2:

Param2 = SELECT TRUNC_TIMESTAMP(CURRENT TIMESTAMP + 1 month,'MONTH')- 1 MICROSECOND) 
            FROM SYSIBM.SYSDUMMY1;

Step 3:

SELECT COUNT(*) 
   FROM  HLVQUAL.TABLE_NAME 
   WHERE CREATE_TS between Param1 AND Param2

I will highly appreciate any examples, how-tos and other helpful comments to resolve my issue.

score 3 · Answer 1 · answered Mar 27 '14 at 21:06

3

How about a single step using:

SELECT COUNT(*) 
FROM  HLVQUAL.TABLE_NAME
WHERE CREATE_TS BETWEEN
   TRUNC_TIMESTAMP(CURRENT TIMESTAMP, 'MONTH') AND
   TRUNC_TIMESTAMP(CURRENT TIMESTAMP + 1 MONTH, 'MONTH') - 1 MICROSECOND

answered Mar 27 '14 at 21:06

NealB

16,670
2
39
60

score 1 · Answer 2 · answered Mar 27 '14 at 12:14

Have you tried doing it with a single statement?

WITH T (TS1, TS2) AS (
  SELECT 
    TRUNC_TIMESTAMP(CURRENT TIMESTAMP,'MONTH'),
    TRUNC_TIMESTAMP(CURRENT TIMESTAMP + 1 month,'MONTH')- 1 MICROSECOND
  FROM SYSIBM.SYSDUMMY1
)
SELECT COUNT(*) 
FROM  HLVQUAL.TABLE_NAME, T
WHERE CREATE_TS between T.TS1 AND T.TS2

score 0 · Answer 3 · answered Mar 27 '14 at 11:49

There are utilities to execute your SQL in batch, DSNTEP4 and Syncsort are two examples. Route the result of step 1 to a flat file. Do the same with step 2.

Write some control statements for your favorite reformatting utility (IEBGENER, SORT, etc.) to get just the resulting timestamp from each of the first two steps into a file.

Concatenate the static part of your SQL for step 3 with the resulting timestamp files.

How can I dynamically pass the result of TRUNC_TIMESTAMP of any kind in my JCL steps?

3 Answers3