Page 1 of 2

Convert Local TSD in UTC

PostPosted: Fri Jan 14, 2011 10:32 am
by spini
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

Re: Convert Local TSD in UTC

PostPosted: Fri Jan 14, 2011 11:35 pm
by skolusu
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:
//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTIN DD *
RECORD
//SORTOUT DD DUMMY
//SYSIN    DD    *
    OPTION COPY
/*

Re: Convert Local TSD in UTC

PostPosted: Mon Jan 17, 2011 4:33 pm
by spini
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

Re: Convert Local TSD in UTC

PostPosted: Mon Jan 17, 2011 7:25 pm
by ricklennox
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.

Re: Convert Local TSD in UTC

PostPosted: Mon Jan 17, 2011 7:32 pm
by stevexff
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...

Re: Convert Local TSD in UTC

PostPosted: Mon Jan 17, 2011 7:39 pm
by ricklennox
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.

Re: Convert Local TSD in UTC

PostPosted: Mon Jan 17, 2011 7:46 pm
by ricklennox
..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.

Re: Convert Local TSD in UTC

PostPosted: Mon Jan 17, 2011 7:53 pm
by spini
Thanks all,

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

Re: Convert Local TSD in UTC

PostPosted: Mon Jan 17, 2011 7:58 pm
by ricklennox
Understood. I'm also interested in what the best DFSORT solution will look like. ;)

Re: Convert Local TSD in UTC

PostPosted: Mon Jan 17, 2011 10:55 pm
by skolusu
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.



//STEP0100 EXEC PGM=SORT         
//SYSOUT   DD SYSOUT=*           
//SORTIN   DD DSN=your input FB 274 byte file,DISP=SHR
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                       
  SORT FIELDS=COPY                                                   
  INREC IFTHEN=(WHEN=INIT,                                           
  OVERLAY=(275:(35,2,ZD,MUL,+3600,ADD,38,2,ZD,MUL,+0060,ADD,41,2,ZD),
                SUB,(48,2,ZD,MUL,+3600,ADD,51,2,ZD,MUL,+0060),       
                ZD,LENGTH=8)),                                       
  IFTHEN=(WHEN=(275,8,ZD,LE,0),                                       
  OVERLAY=(275:275,8,ZD,ADD,+86400,ZD,LENGTH=8,                       
           283:24,10,UFF,M11,LENGTH=8,                               
           024:283,8,Y4T,SUBDAYS,+1,TOGREG=Y4T(-)))                   
                                                                     
  OUTREC IFOUTLEN=274,IFTHEN=(WHEN=INIT,                             
  OVERLAY=(35:275,8,ZD,DIV,+3600,EDIT=(TT),                           
           38:(275,8,ZD,MOD,+3600),DIV,+60,EDIT=(TT),                 
           41:((275,8,ZD,MOD,+3600),MOD,+60),EDIT=(TT)))             

//*