|
View previous topic :: View next topic
|
| Author |
Message |
FNYD
New User
Joined: 11 May 2021
Posts: 14
Location: INDIA
|
|
|
|
Hi ,
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. |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2272
Location: USA
|
|
|
|
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. |
|
| Back to top |
|
|
FNYD
New User
Joined: 11 May 2021
Posts: 14
Location: INDIA
|
|
|
|
| sergeyken wrote: |
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 |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2272
Location: USA
|
|
|
|
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... |
|
| Back to top |
|
|
FNYD
New User
Joined: 11 May 2021
Posts: 14
Location: INDIA
|
|
|
|
| sergeyken wrote: |
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
| Code: |
//STEP1 EXEC PGM=IKJEFT1B,DYNAMNBR=20,TIME=120,REGION=3072K
//STEPLIB DD DSN=DBPT.DB2.SDSNLOAD,DISP=SHR
//ISPPROF DD UNIT=SYSDA,SPACE=(TRK,(9,1,4)),
// DCB=(LRECL=175,BLKSIZE=27825,RECFM=FB,DSORG=PO)
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//REPORT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DBP8)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB71) PARMS('SQL') -
LIB('DBP8.DB2.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSPUNCH DD DUMMY
//SYSREC00 DD DSN=AIS.D5750CPR.EDCSFILE,
// DISP=(NEW,CATLG,KEEP),
// SPACE=(CYL,(200,200),RLSE),
// UNIT=SYSDA,STORCLAS=SCNORMAL
//SYSIN DD *
SELECT * FROM tablename WHERE Condition;
|
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 |
|
| Back to top |
|
|
FNYD
New User
Joined: 11 May 2021
Posts: 14
Location: INDIA
|
|
|
|
| Rohit Umarjikar wrote: |
This is nothing new in DFSORT, All you got to do is do research .
ibmmainframes.com/about45058.html
Is it not possible to do in DB2 query itself ? |
---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 |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2272
Location: USA
|
|
|
|
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. |
|
| Back to top |
|
|
FNYD
New User
Joined: 11 May 2021
Posts: 14
Location: INDIA
|
|
|
|
| sergeyken wrote: |
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
| Code: |
OTION COPY
INREC IFOUTLEN=100,
IFTHEN=(WHEN=(42,6,FS,EQ,NUM),
OVERLAY=(33:+6,ADD,33,2,ZD,EDIT(TT),HIT=NEXT)),
IFTHEN=(WHEN=(42,6,FS,EQ,NUM,AND,33,2,EQ,C'24'),
OVERLAY=(33:C'00'))
also tried giving
OVERLAY=(33:C'00',ZD,EDIT(TT)))
|
| Quote: |
m getting errro as
INREC STATEMENT : SYNTAX ERROR
any help pls or anythign i am mssing |
[/code] |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2272
Location: USA
|
|
|
|
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. |
|
| Back to top |
|
|
FNYD
New User
Joined: 11 May 2021
Posts: 14
Location: INDIA
|
|
|
|
| sergeyken wrote: |
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
| Code: |
IFTHEN=(WHEN=(42,6,FS,EQ,NUM,AND,33,2,ZD,GE,00),
OVERLAY=(30:+1,ADD,30,2,ZD,EDIT(TT)))
|
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 ... |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
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' |
|
|
| Back to top |
|
|
FNYD
New User
Joined: 11 May 2021
Posts: 14
Location: INDIA
|
|
|
|
| Rohit Umarjikar wrote: |
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 . |
|
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1430
Location: Bamberg, Germany
|
|
|
|
May it be of help.
| Code: |
//WHATEVER EXEC PGM=ICEMAN
//SORTIN DD *
2021-05-24-18.56.39.247407
/*
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
INREC IFTHEN=(WHEN=(12,2,ZD,GE,+18),
OVERLAY=(1:1,10,UFF,TO=ZD,LENGTH=8,
1:1,8,Y4T,ADDDAYS,+1,TOGREG=Y4T(-)),HIT=NEXT),
IFTHEN=(WHEN=ANY,
OVERLAY=(12:(12,2,ZD,ADD,+6),MOD,+24,EDIT=(TT),LENGTH=2)),
IFTHEN=(WHEN=NONE,
OVERLAY=(12:(12,2,ZD,ADD,+6),MOD,+24,EDIT=(TT),LENGTH=2))
END
/* |
|
|
| Back to top |
|
|
FNYD
New User
Joined: 11 May 2021
Posts: 14
Location: INDIA
|
|
|
|
| Joerg.Findeisen wrote: |
May it be of help.
| Code: |
//WHATEVER EXEC PGM=ICEMAN
//SORTIN DD *
2021-05-24-18.56.39.247407
/*
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
INREC IFTHEN=(WHEN=(12,2,ZD,GE,+18),
OVERLAY=(1:1,10,UFF,TO=ZD,LENGTH=8,
1:1,8,Y4T,ADDDAYS,+1,TOGREG=Y4T(-)),HIT=NEXT),
IFTHEN=(WHEN=ANY,
OVERLAY=(12:(12,2,ZD,ADD,+6),MOD,+24,EDIT=(TT),LENGTH=2)),
IFTHEN=(WHEN=NONE,
OVERLAY=(12:(12,2,ZD,ADD,+6),MOD,+24,EDIT=(TT),LENGTH=2))
END
/* |
|
----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.. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1430
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. |
|
| Back to top |
|
|
FNYD
New User
Joined: 11 May 2021
Posts: 14
Location: INDIA
|
|
|
|
| Joerg.Findeisen wrote: |
| 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. |
---f----
thank you for your support finally i choose to overlay teh feb 29 by using below sort
| Code: |
OVERLAY=(
61:01,04,ZD,MOD,+4,EDIT=(T),
63:01,04,ZD,MOD,+100,EDIT=(TTT),
67:01,04,ZD,MOD,+400,EDIT=(TTT)),HIT=NEXT),
IFTHEN=(WHEN=(67,3,ZD,EQ,0,OR,
61,1,ZD,EQ,0,AND,63,3,ZD,GT,0)),
OVERLAY=(pos to of month:C'29'))
|
|
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
