IBM Mainframe Forum

Hi,

i have to build a loadfile for DB2 Load-Utility with DFSort. So far no problem. But in the origin file is a timestamp (extract from Oracel) as local TSD with a offset
(difference to UTC). Store should be the TSD as UTC TSD.

example: local TSD with offset 1:30
2011-11-01T03:04:5:678-1:30

result should be : 2011-11-01-03-04.05.678000 - 1.5 = 2011-11-01-01-34.05.678000

It is possible to do this conversion with DFSORT? How can i subtract the offset from the origin TSD?

Regards

spini,

It is possible to perform the time arithmetic. However I need more details. Your example shows the seconds portion as edited i.e 5 instead of 05. is that a typo?

1. What is the LRECL and RECFM of the input file
2. What is the position of the date and time fields
3. Does the offset to subtract change from record to record or is it a constant of 90 minutes?
4. Is the time format always HH:MM:SS and does it follow 24 hour format or 12 hour format?
5. If the time is between 00:00 Am and 01:29 AM and if you subtract 90 minutes , you would go back to the previous day. Do you need the date to be adjusted to previous day?
6. Please run the following job and post the //SYSOUT messages so I can see what level of DFSORT you have:

Hi Skolusu,
thanks..
Below the answers to yours questions:

yes, is a typo..

2011-11-01T03:04:05:678-1:30


1.LRECL = 274 RECFM= FB
2.The timestamp beginn at the 29 position
----+----1----+----2----+----3----+----4----+----5----+----6-
********************************* Top of Data ***************
URAA 1234567890123456782011-01-12T03:04:05:678-01:30DEV

3. The offset change from record to record.
4. we need 24 hours format
5. yes, the date have to be adjusted
6.
BLOCKSET COPY TECHNIQUE SELECTED
VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
- CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 11:31 ON MON JAN 17, 2011
OPTION COPY 00200005
RECORD TYPE IS F - DATA STARTS IN POSITION 1
C5-K51707 C6-K51707 C7-K54603 C8-K51707 E9-K51707 C9-BASE E5-K55337 E7-K51707
ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICEAM1 ENVIRONMENT SELECTED
U25012B .S1 . , INPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
MAIN STORAGE = (MAX,6291456,6278238)
MAIN STORAGE ABOVE 16MB = (6200798,6200798)
OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,VS
OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ERET=ABEND,MS
OPTIONS: VIO=N,RESDNT=ALL ,SMF=NO ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=
OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT=N,COBEXIT=
OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=Y,DSA=0
OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE ,EXITCK=S,PARMDD
OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE
OPTIONS: NULLOUT=RC0

regards
spini

Before looking for a DFSORT solution, it is possible to retrieve this data from DB2 with the TS already modified.
If you have any control over the extract process, you should consider letting DB2 do your conversion for you.

According to the OP the origin of the file is Oracle, but your idea holds good - it is much easier to unload the data in the right format to start with if you have control over how it gets extracted...

Hi Steve, yes I did notice that.. I work with DB2 for z/OS, so I did a quick search on doing this for Oracle data, and the DB2 functions appear quite similar.

..as far as a DFSORT solution goes, with my limited experience anything I could come up with would be to tear the TS down, convert it to a numeric format, apply some 'modular' math to it, then reconvert it back into a TS format for the Load utility. That's quite a "frankenstein's monster" approach, and because I've seen some very elegant solutions offered to similar problems on this forum, I'll let someone more experienced help on that end.

Thanks all,

but sadly i don't have conrol about the extract process, so i need a solution with the DFSort..

Understood. I'm also interested in what the best DFSORT solution will look like.

Spini,

The following DFSORT JCL will give you the desired results. I assumed the offset to time starts at 48 and has the format HH:MM

The logic is quite simple. Convert the time (HH:MM:SS) to seconds and subtract the offset converting it to seconds.

If the number is negative then we need to offset the date by 1. We use the SUBDAYS function for this. And also we add 86,400 (24 hours) to the negative number so that we have the right time on the previous day.

Using OUTREC we convert back the seconds to HH:MM:SS format.