Fetching Previous Day's date in my JCL

In this Mainframe Forum - You can post your queries on JCL, OS/390 JCL, MVS JCL, z/OS JCL, JES2 & JES3

Moderators: Frank Yaeger, DikDude, Moderator Group

Post Reply
bhaskar.naidu
Member
Posts: 16
Joined: Mon Jul 06, 2009 5:25 pm

Fetching Previous Day's date in my JCL

Post by bhaskar.naidu » Mon Jul 06, 2009 5:30 pm

Hi,
In my JCL, I am using DNSTIAUL to fetch records from a table which has a huge number of records in which My where clause is trying to get the previous day's date using the timestamp from the table. This Query is taking a lot of time in executing the JCL.
Is there any way by which I can reduce the execution time?

Regards,
Bhaskar

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Mon Jul 06, 2009 5:47 pm

what's today?, physic day. what does your WHERE clause look like?
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Mon Jul 06, 2009 5:57 pm

and:
how many rows are in the table
how many rows do you expect to select
is the timestamp part of an index
how large are the rows - how many columns
what do you think is a 'long time'?
?????
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

bhaskar.naidu
Member
Posts: 16
Joined: Mon Jul 06, 2009 5:25 pm

Post by bhaskar.naidu » Mon Jul 06, 2009 6:06 pm

Answers to your queries:
how many rows are in the table - there are 15 million records in the table
how many rows do you expect to select - I am expecting around 2K rows from the select
is the timestamp part of an index - we are also thinking of this as a solution, but would appreciate if I get any different approach.

how large are the rows - how many columns - Columns are not more, there are around 10 columns, but there are 15 million rows
what do you think is a 'long time'? - it is taking around 45 mins to 65 mins.
?????[/quote]

My where clause is as given below:

WHERE DATE(AUDIT_UPDT_TS) = DATE(CURRENT DATE - 1 DAY)

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Mon Jul 06, 2009 6:21 pm

well your WHERE clause performs a scalar function on each and every row.
so I would change the clause to be
WHERE AUDIT_UPDT_TS BETWEEN (figure out the functions necessary to make CURRENT_DATE - 1 day concatenated with 00:00:00.000000)
AND (figure out the functions necessary to make CURRENT_DATE - 1 day concatenated with 23:59:59.999999)

db2 will generate the right side of the predicate once.

if this unload is important for the business, create a new index, obviously not unique.

but the change in the WHERE clause should help.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

bhaskar.naidu
Member
Posts: 16
Joined: Mon Jul 06, 2009 5:25 pm

Post by bhaskar.naidu » Tue Jul 07, 2009 10:38 am

Hi Dick, your query worked and is perfect for my requirement. Thanks a lot for your timely help.

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Tue Jul 07, 2009 1:20 pm

glad it helped. be interesting to know the difference in CPU time (not elapsed, please).

the difference is it the amount of time db2 spent manipulating the timestamp datatype to date datatype 15 million times.

and please, it is ROWS not records for db2.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

bhaskar.naidu
Member
Posts: 16
Joined: Mon Jul 06, 2009 5:25 pm

Post by bhaskar.naidu » Tue Jul 07, 2009 1:28 pm

Hi Dick,

I really dont know how to check for the amount of time db2 spent manipulating the timestamp datatype to date datatype. If you can tell me how to check that, I shall try it and let you know..

But the original query was taking a time of 18.89 secs for 1500 rows where as this revised query took just 7.26 secs.

Regards,
Bhaskar

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Tue Jul 07, 2009 1:50 pm

the 18.89 secs and 7.26 secs are what I was looking for. you mentioned 45 to 65 minutes, which I assumed was elapsed. Don't need to respond, but what is the new elapsed time.

and actually the time involved is for reading and qualifying rows from 15 million total.
Of course the number of selected will affect the time,
but the key here is 15 million rows had timestamp columns where each was converted in preparation for the WHERE condition.

As far as how much of the time db2 spent actually converting - not even going to guess.
we only know that a significant amount/portion/percentage of time was saved by not performing the scalar function on the column, 15 million times.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

bhaskar.naidu
Member
Posts: 16
Joined: Mon Jul 06, 2009 5:25 pm

Post by bhaskar.naidu » Tue Jul 14, 2009 10:36 am

Hi Dick,

In continuation with this conversation, my JCL is exexcuting DNSTIAUL utility to fetch records from a table for which you gave a solution to use

"WHERE AUDIT_UPDT_TS BETWEEN (figure out the functions necessary to make CURRENT_DATE - 1 day concatenated with 00:00:00.000000)
AND (figure out the functions necessary to make CURRENT_DATE - 1 day concatenated with 23:59:59.999999)"

So, I changed the Where clause as given below:

WHERE AUDIT_UPDT_TS BETWEEN (CHAR(CURRENT DATE - 1) concatenated with 00:00:00.000000) AND (CHAR(CURRENT DATE - 1 day) concatenated with 23:59:59.999999).

But then my DBA advised me that it is still using the CHAR function in DSNTIAUL which is not allowed in our organization as per the new standards.

So, my idea is to have a SORT utility in the first step where it would calculate this WHERE clause and get the hard-coded audit-updt-ts values replaced and prepare the query and put it in a control card or a dataset for my second step which would execute the DSNTIAUL.
could you please let me know whether we can do this using SORT or not.
If yes, please help me.

User avatar
arcvns
Member
Posts: 28
Joined: Sat May 30, 2009 10:19 pm
Location: Chennai, India

Post by arcvns » Thu Aug 06, 2009 3:40 pm

Hello,

May be it's too late, but you can generate the above WHERE condition using the below SyncSort job.

Code: Select all

//STEP1  EXEC PGM=SORT                                        
//SYSOUT   DD SYSOUT=*                                        
//SORTIN   DD *                                               
//SORTOUT  DD SYSOUT=*                                        
//SYSIN    DD *                                               
  OPTION COPY                                                 
  OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                       
         TRAILER1=('WHERE AUDIT_UPDT_TS BETWEEN',/,'(''',     
                   DATE=(4MD-)-1,'-00.00.00.000000'' AND ''', 
                   DATE=(4MD-)-1,'-23.59.59.999999'')')
SORTOUT would have this,

Code: Select all

WHERE AUDIT_UPDT_TS BETWEEN                                    
('2009-08-05-00.00.00.000000' AND '2009-08-05-23.59.59.999999')
Arun

User avatar
Natarajan
Moderator
Posts: 537
Joined: Fri Oct 10, 2008 12:57 pm
Location: chennai
Contact:

Post by Natarajan » Thu Aug 06, 2009 3:59 pm

Bhaskar has initiated separte thread for this... There is a answer from skolusu using DFSORT... it may be useful for those ,we is reading this thread.

http://www.mainframegurukul.com/Mainfra ... php?t=4290
Natarajan
Chennai

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others