Convert Local TSD in UTC



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Convert Local TSD in UTC

Postby spini » Fri Jan 14, 2011 10:32 am

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
 
Posts: 6
Joined: Thu Jan 13, 2011 7:00 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Convert Local TSD in UTC

 

Re: Convert Local TSD in UTC

Postby skolusu » Fri Jan 14, 2011 11:35 pm

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
/*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Convert Local TSD in UTC

Postby spini » Mon Jan 17, 2011 4:33 pm

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
spini
 
Posts: 6
Joined: Thu Jan 13, 2011 7:00 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Convert Local TSD in UTC

Postby ricklennox » Mon Jan 17, 2011 7:25 pm

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.
ricklennox
 
Posts: 11
Joined: Thu Dec 16, 2010 5:59 am
Has thanked: 0 time
Been thanked: 0 time

Re: Convert Local TSD in UTC

Postby stevexff » Mon Jan 17, 2011 7:32 pm

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...
Steve
stevexff
 
Posts: 56
Joined: Wed Nov 10, 2010 7:48 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Convert Local TSD in UTC

Postby ricklennox » Mon Jan 17, 2011 7:39 pm

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.
ricklennox
 
Posts: 11
Joined: Thu Dec 16, 2010 5:59 am
Has thanked: 0 time
Been thanked: 0 time

Re: Convert Local TSD in UTC

Postby ricklennox » Mon Jan 17, 2011 7:46 pm

..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.
ricklennox
 
Posts: 11
Joined: Thu Dec 16, 2010 5:59 am
Has thanked: 0 time
Been thanked: 0 time

Re: Convert Local TSD in UTC

Postby spini » Mon Jan 17, 2011 7:53 pm

Thanks all,

but sadly i don't have conrol about the extract process, so i need a solution with the DFSort..
spini
 
Posts: 6
Joined: Thu Jan 13, 2011 7:00 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Convert Local TSD in UTC

Postby ricklennox » Mon Jan 17, 2011 7:58 pm

Understood. I'm also interested in what the best DFSORT solution will look like. ;)
ricklennox
 
Posts: 11
Joined: Thu Dec 16, 2010 5:59 am
Has thanked: 0 time
Been thanked: 0 time

Re: Convert Local TSD in UTC

Postby skolusu » Mon Jan 17, 2011 10:55 pm

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)))             

//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post