IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DFSORT to add 6 hours to time(tdate-time) for extarction


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
FNYD

New User


Joined: 11 May 2021
Posts: 14
Location: INDIA

PostPosted: Thu May 20, 2021 8:39 pm
Reply with quote

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
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1188

PostPosted: Fri May 21, 2021 4:13 am
Reply with quote

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
View user's profile Send private message
FNYD

New User


Joined: 11 May 2021
Posts: 14
Location: INDIA

PostPosted: Fri May 21, 2021 10:28 am
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2772
Location: NYC,USA

PostPosted: Fri May 21, 2021 6:59 pm
Reply with quote

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 ?
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1188

PostPosted: Fri May 21, 2021 7:04 pm
Reply with quote

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
View user's profile Send private message
FNYD

New User


Joined: 11 May 2021
Posts: 14
Location: INDIA

PostPosted: Fri May 21, 2021 8:29 pm
Reply with quote

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
View user's profile Send private message
FNYD

New User


Joined: 11 May 2021
Posts: 14
Location: INDIA

PostPosted: Fri May 21, 2021 8:30 pm
Reply with quote

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
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1188

PostPosted: Sat May 22, 2021 12:12 am
Reply with quote

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
View user's profile Send private message
FNYD

New User


Joined: 11 May 2021
Posts: 14
Location: INDIA

PostPosted: Mon May 24, 2021 1:01 pm
Reply with quote

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
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1188

PostPosted: Mon May 24, 2021 3:11 pm
Reply with quote

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
View user's profile Send private message
FNYD

New User


Joined: 11 May 2021
Posts: 14
Location: INDIA

PostPosted: Mon May 24, 2021 3:22 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2772
Location: NYC,USA

PostPosted: Tue May 25, 2021 1:06 am
Reply with quote

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
View user's profile Send private message
FNYD

New User


Joined: 11 May 2021
Posts: 14
Location: INDIA

PostPosted: Tue May 25, 2021 11:12 am
Reply with quote

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
View user's profile Send private message
Joerg.Findeisen

Active Member


Joined: 15 Aug 2015
Posts: 607
Location: Bamberg, Germany

PostPosted: Tue May 25, 2021 11:49 am
Reply with quote

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
View user's profile Send private message
FNYD

New User


Joined: 11 May 2021
Posts: 14
Location: INDIA

PostPosted: Wed May 26, 2021 3:28 pm
Reply with quote

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
View user's profile Send private message
Joerg.Findeisen

Active Member


Joined: 15 Aug 2015
Posts: 607
Location: Bamberg, Germany

PostPosted: Wed May 26, 2021 10:28 pm
Reply with quote

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
View user's profile Send private message
FNYD

New User


Joined: 11 May 2021
Posts: 14
Location: INDIA

PostPosted: Mon May 31, 2021 7:50 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Copy a DS Member to multiple users at... TSO/ISPF 8
No new posts Expected data is not coming up on Fir... CICS 2
No new posts To pull the file with Date and Time u... TSO/ISPF 17
No new posts UUID4 - extracting time element COBOL Programming 1
No new posts Cobol-db2 : SQL query is running for ... DB2 4
Search our Forums:

Back to Top