Fetching Previous Day's date in my JCL
Moderators: Frank Yaeger, DikDude, Moderator Group
-
- Member
- Posts: 16
- Joined: Mon Jul 06, 2009 5:25 pm
Fetching Previous Day's date in my JCL
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
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
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 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'?
?????
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.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
-
- Member
- Posts: 16
- Joined: Mon Jul 06, 2009 5:25 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)
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)
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 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.
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.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
-
- Member
- Posts: 16
- Joined: Mon Jul 06, 2009 5:25 pm
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 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.
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.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
-
- Member
- Posts: 16
- Joined: Mon Jul 06, 2009 5:25 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
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
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 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.
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.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
-
- Member
- Posts: 16
- Joined: Mon Jul 06, 2009 5:25 pm
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.
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.
Hello,
May be it's too late, but you can generate the above WHERE condition using the below SyncSort job.SORTOUT would have this,
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'')')
Code: Select all
WHERE AUDIT_UPDT_TS BETWEEN
('2009-08-05-00.00.00.000000' AND '2009-08-05-23.59.59.999999')
Arun
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
http://www.mainframegurukul.com/Mainfra ... php?t=4290
Natarajan
Chennai
Chennai
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
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page