I am working on requirement where i need to unload data from db2 and in my where condition i have a check on rowtimestamp(date-time format 'yyyy-mm-dd-06.56.39.247407').
i have query like in where condition
where
rowdt > yyyy-mm-dd-06.56.39.247407 and rowdt <= yyyy-mm-dd-06.30.00.000000
the time in the above row i need to add + 6hours to it so that to extract data between that time frame (6hours frame)
expected :
rowdt <= yyyy-mm-dd-12.30.00.000000
i am using sort for dynamically reading datetimestamp values form file and using inrec overlay to override the values in rowtimestamp.
can anyone please help me how i can achive this by adding upto time by +6 hrs
req:
job is expected to run 4 times a day to extract data form db2 tables based on certain criteria and on updated timestamp (rowdt col from tables) so it will be like00:30 am then 06:30 am , 12:30pm , 6:30 pm run.
How your data extraction from DB2 is related to the forum on DFSORT/ICETOOL utilities???
Please, clarify. Your message is not clear.
Also: please, use code tags, and proper alignment, when posting any type of program code, or data.
---f------
my requirement is to increment the time by 6hours in 24hrs format.
the rowdt (col) has a value yyy-mm-dd-06.56.39.247407
i have a flat file which will keep the last updated datetime, whihc will have value like : yyyy-mm-dd-06.56.39.247407
i will use sort to overlay the query specfically where clause for the datetimestamp using data from above file.
similarly i want to use sort, where i can increment the time and override.
i have to give a range when extarcting data say
Code:
select *
from db2tables
where rowdt > yyyy-mm-dd-06.56.39.247407 and rowdt < = yyyy-mm-dd-06.56.39.247407(the time here 06.56.39, needs to be incremented by 6 hours ) ..
expected query would be
Code:
select *
from db2tables
where rowdt > yyyy-mm-dd-06.56.39.247407 and rowdt < = yyyy-mm-dd-12.56.39.247407
basically i'am looking for sort syntax to increment/override time by 6hrs.
i hope i have cleared my requirement
Use the Code tags
1) you did not learn how to use code tags, and code alignment
2) you have not clarified your initial message, not even a bit:
- after “using SORT”, how do you plan to “run query”?
- who, and how must substitute either “old timestamps”, or “new updated timestamps” into your query?
3) recalculation of timestamp values can be done either by SORT utility, or as part of SQL query, or by another way; it depends on: how do you plan to organize the whole process???
Looks like you are not interested for other people to pay any attention to your question, are you?
First of all: learn how to use code tags, and code alignment in your message!
Otherwise almost no-one has any desire to dig into the mess of words...
1) you did not learn how to use code tags, and code alignment
2) you have not clarified your initial message, not even a bit:
- after “using SORT”, how do you plan to “run query”?
- who, and how must substitute either “old timestamps”, or “new updated timestamps” into your query?
3) recalculation of timestamp values can be done either by SORT utility, or as part of SQL query, or by another way; it depends on: how do you plan to organize the whole process???
Looks like you are not interested for other people to pay any attention to your question, are you?
First of all: learn how to use code tags, and code alignment in your message!
Otherwise almost no-one has any desire to dig into the mess of words...
--F---
was not aware on code tag.
process inlcudes :
step1 : reading from flat file to get last saved timestamp from step6
step2 : control card which will have my query
step3 : using sort override teh rowdt(timstamp col) from the file in setp1 (this is dynamic requirement where my job will be running to get db2 extraact from few tables on certain where condition 4 times a day )
step4: execute the query using IKJEFT01
step5 : i will be writing the above output to flat file which will also have rowdt data (ordered by desc)
step 6 : use that same flat file above to take the rodt lastest tiemstamp and save it in file for next run
so tht the next run would take that timestamp as rast retrieved data and execute further from step1
i hope atleast you got little overview of how i am goign to execute. There may be other methods which i am not aware, well you can suggest me thts y i posted. this is what i have planned
timestamp it will be like rowdt > last run time and rowdt < nexxt run time which is + 6 hours
---thank you rohit----
ill execute and check the above sort condition and post it here
on db2 query i found something here , will check on it as well
ELECT CURRENT TIMESTAMP + 1 SECOND
SELECT CURRENT TIMESTAMP + 1 MINUTE
SELECT CURRENT TIMESTAMP + 1 HOUR
SELECT CURRENT TIMESTAMP + 1 DAY
SELECT CURRENT TIMESTAMP + 1 MONTH
SELECT CURRENT TIMESTAMP + 1 YEAR
In your "Step 6" - you need to save not the "latest timestamp", but it's value increased by 6 hours (or both, if ever needed).
That's it.
Nothing left to discuss on this pseudo-issue.
----f-----
i am facing one issue in sort card
whenver i am adding +6 to hours of time it is workin fine as per 2hrs format but when it goes to 24:30:00 (at nite am) i want it to be 00:30:00 .
instead of 24 it should change to 00
below is my query
the line which i am replacing in sort in query is :
rowdt > '2021-04-14-24.30.00.000000'
once it hits 24 it should convert it to 00
In your question you must mention exactly the problem you are faced, but not any related issue. This “discussion” continues for several days, but only the latest message finally clarified your issue.
In the same manner, I have intention to wait for several days before giving you the response.
In your question you must mention exactly the problem you are faced, but not any related issue. This “discussion” continues for several days, but only the latest message finally clarified your issue.
In the same manner, I have intention to wait for several days before giving you the response.
----f-----
i was trying on something and blocked up with some other solutions
Apologies pls..
can you provide me with solution
also if the rowdt > 2021-05-31-23.39.00.000000
when i am adding +6 to it is giving as
'2021-05-20-29.39.00.000000'
instead should be as '2021-05-20-05.39.00.000000'
also when it is >24 or >00 then it should increment day
this i have handled as
but if you consider above rowdt like
'2021-05-20-05.39.00.000000' then here for 05 it is not considering as greater then 00 neither 24 hrs....and hence my day is not getting incremented to next day
can you help ...
I would suggest you to write 4 jobs , schedule them every after 6 hours , change the control card and hardcode the time of 6 hours of gap in each Job. Very simple, clean and easy to manage. Don't get struck with DFSORT as its not required.
Use something like this in each job..
Code:
rowdt BETWEEN Current Date || '-12.00.00.000000' AND Current Date || '-06.00.00.000000'
I would suggest you to write 4 jobs , schedule them every after 6 hours , change the control card and hardcode the time of 6 hours of gap in each Job. Very simple, clean and easy to manage. Don't get struck with DFSORT as its not required.
Use something like this in each job..
Code:
rowdt BETWEEN Current Date || '-12.00.00.000000' AND Current Date || '-06.00.00.000000'
----f----
hi Rohit, initially i taught of similar concept , but then tried using dfsort and one jcl.
the rowdt will compare between last retrieved timestamp , which i will be storing in a flat file, like the last extract from db2 say its
2021-05-20-23.39.00.0
then next run it will be like
Code:
where rowdt >= '2021-05-20-23.39.00.000000' and rowdt <= '2021-05-20-23+6.39.00.000000'
it would yield :
where rowdt >= '2021-05-20-23.39.00.000000' and rowdt <= '2021-05-21-05.39.00.000000'
as its next day so date also need to be incremented .
----f----
hi thanks for your solution this is of help.....also i found another solution using sort card. parallelly working on it.
Also m handling leap year logic here , like is there a way i can calculate number of days of year and if its 366 then i can make changes to the day of feb month..
Joined: 15 Aug 2015 Posts: 1335 Location: Bamberg, Germany
FNYD wrote:
Also m handling leap year logic here , like is there a way i can calculate number of days of year and if its 366 then i can make changes to the day of feb month..
This is handled by using Y4T,ADDDAYS,+1,TOGREG=Y4T(-) in my code snippet.
Also m handling leap year logic here , like is there a way i can calculate number of days of year and if its 366 then i can make changes to the day of feb month..
This is handled by using Y4T,ADDDAYS,+1,TOGREG=Y4T(-) in my code snippet.
---f----
thank you for your support finally i choose to overlay teh feb 29 by using below sort
