Merge records of the input file using SORT



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

Merge records of the input file using SORT

Postby pulcinella » Mon May 26, 2008 8:57 pm

Good morning,

The next example is similar at the topic f=28&t=596 but i don't get the output correctly...

I need join of two files...

input 1 (25 position)

uuuuuuuoooooooooooooooooo
uuuuuuupppppppppppppppppp
vvvvvvvrrrrrrrrrrrrrrrrrr
vvvvvvvyyyyyyyyyyyyyyyyyy
xxxxxxxaaaaaaaaaaaaaaaaaa
xxxxxxxbbbbbbbbbbbbbbbbbb
xxxxxxxcccccccccccccccccc
xxxxxxxzzzzzzzzzzzzzzzzzz
xxxxxxxyyyyyyyyyyyyyyyyyy
yyyyyyyeeeeeeeeeeeeeeeeee
yyyyyyyffffffffffffffffff
zzzzzzziiiiiiiiiiiiiiiiii
zzzzzzziiiiiiiiiiiiiiiiii


input 2 (16 position)

uuuuuuuRRRRRRRRR
xxxxxxxYYYYYYYYY
yyyyyyyPPPPPPPPP
ZZZZZZZddddddddd


And I need obtain an outful file with 34 position with the keys is the first 7 position (the key for first file has duplicates; the second file no duplicates).

output (25+9=34 position)

uuuuuuuoooooooooooooooooo (input 1) RRRRRRRRR (input 2)
uuuuuuupppppppppppppppppp (input 1) RRRRRRRRR (input 2)
vvvvvvvrrrrrrrrrrrrrrrrrr (input 1) 9 blanks because not coincidence
vvvvvvvyyyyyyyyyyyyyyyyyy (input 1) 9 blanks because not coincidence   
xxxxxxxaaaaaaaaaaaaaaaaaa (input 1) YYYYYYYYY (input 2)         
xxxxxxxbbbbbbbbbbbbbbbbbb (input 1) YYYYYYYYY (input 2)
xxxxxxxcccccccccccccccccc (input 1) YYYYYYYYY (input 2)
xxxxxxxzzzzzzzzzzzzzzzzzz (input 1) YYYYYYYYY (input 2)
xxxxxxxyyyyyyyyyyyyyyyyyy (input 1) YYYYYYYYY (input 2)
yyyyyyyeeeeeeeeeeeeeeeeee (input 1) PPPPPPPPP (input 2)
yyyyyyyffffffffffffffffff (input 1) PPPPPPPPP (input 2)
zzzzzzziiiiiiiiiiiiiiiiii (input 1) ddddddddd (input 2)
zzzzzzziiiiiiiiiiiiiiiiii (input 1) ddddddddd (input 2)
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: join two files

Postby dick scherrer » Mon May 26, 2008 10:00 pm

Hello,

Please post the jcl and control statements you are submitting as well as the spooled output from the run.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: join two files

Postby pulcinella » Tue May 27, 2008 10:59 am

//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD * (--16 positions)
uuuuuuuRRRRRRRRR
xxxxxxxYYYYYYYYY
yyyyyyyPPPPPPPPP
ZZZZZZZddddddddd
//IN2 DD * (--78 positions)
uuuuuuuoooooooooooooooooo...............
uuuuuuupppppppppppppppppp...............
vvvvvvvrrrrrrrrrrrrrrrrrr...............
vvvvvvvyyyyyyyyyyyyyyyyyy...............
xxxxxxxaaaaaaaaaaaaaaaaaa...............
xxxxxxxbbbbbbbbbbbbbbbbbb
xxxxxxxcccccccccccccccccc
xxxxxxxzzzzzzzzzzzzzzzzzz...............
xxxxxxxyyyyyyyyyyyyyyyyyy...............
yyyyyyyeeeeeeeeeeeeeeeeee
yyyyyyyffffffffffffffffff...............
zzzzzzziiiiiiiiiiiiiiiiii...............
zzzzzzziiiiiiiiiiiiiiiiii
//T1 DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)
//T2 DD DSN=&&T2,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN1) USING(CTL1)
COPY FROM(IN2) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(89,9,CH) -
WITHALL WITH(1,88) USING(CTL3) KEEPNODUPS
SORT FROM(T2) USING(CTL4)
/*
//CTL1CNTL DD *
OUTFIL FNAMES=T1,BUILD=(88X,1,7,8,9)
//CTL2CNTL DD *
OUTFIL FNAMES=T1,BUILD=(1,78,1,7,78,1,9X)
//CTL3CNTL DD *
OUTFIL FNAMES=T2,OMIT=(1,88,CH,EQ,C' '),
BUILD=(1,88,95,9)
//CTL4CNTL DD *
SORT FIELDS=(79,7,CH,A)
OUTFIL FNAMES=OUT,BUILD=(1,78,85,9)
/*

OUTPUT ERROR

ICE055I 0 INSERT 0, DELETE 0
ICE054I 0 RECORDS - IN: 736583, OUT: 736583
ICE227I 0 T1 : DELETED = 0, REPORT = 0, DATA = 736583
ICE228I 0 T1 : TOTAL IN = 736583, TOTAL OUT = 736583
ICE052I 0 END OF DFSORT
ICE200I 0 IDENTIFIER FROM CALLING PROGRAM IS 0002
ICE143I 0 BLOCKSET COPY TECHNIQUE SELECTED
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AN
ICE000I 0 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 18:01 ON MON MAY
OUTFIL FNAMES=T1,BUILD=(1,78,1,7,78,1,9X)
ICE146I 0 END OF STATEMENTS FROM CTL2CNTL - PARAMETER LIST STATEMENTS FOLLOW
DEBUG NOABEND,ESTAE
OPTION MSGDDN=DFSMSG,LIST,MSGPRT=ALL,RESINV=0,SORTDD=CTL2,SORTIN=IN2,D
NALLOC
SORT FIELDS=COPY
ICE201I E RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE027A 9 END OF T1 FIELD BEYOND MAXIMUM RECORD LENGTH
ICE751I 0 C5-K26318 C6-K90007 C7-K90000 C8-K23476 E9-K90007 E7-K24705
ICE052I 3 END OF DFSORT
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join two files using DFSORT

Postby skolusu » Tue May 27, 2008 10:15 pm

pulcinella,

The following DFSORT/ICETOOL will give you the desired results.

//STEP0100 EXEC PGM=ICETOOL                                   
//TOOLMSG  DD SYSOUT=*                                         
//DFSMSG   DD SYSOUT=*                                         
//IN1      DD *                                               
UUUUUUURRRRRRRRR                                               
XXXXXXXYYYYYYYYY                                               
YYYYYYYPPPPPPPPP                                               
ZZZZZZZDDDDDDDDD                                               
//IN2      DD *                                               
UUUUUUUOOOOOOOOOOOOOOOOOO                                     
UUUUUUUPPPPPPPPPPPPPPPPPP                                     
VVVVVVVRRRRRRRRRRRRRRRRRR                                     
VVVVVVVYYYYYYYYYYYYYYYYYY                                     
XXXXXXXAAAAAAAAAAAAAAAAAA                                     
XXXXXXXBBBBBBBBBBBBBBBBBB                                     
XXXXXXXCCCCCCCCCCCCCCCCCC                                     
XXXXXXXZZZZZZZZZZZZZZZZZZ                                     
XXXXXXXYYYYYYYYYYYYYYYYYY                                     
YYYYYYYEEEEEEEEEEEEEEEEEE                                     
YYYYYYYFFFFFFFFFFFFFFFFFF                                     
ZZZZZZZIIIIIIIIIIIIIIIIII                                     
ZZZZZZZIIIIIIIIIIIIIIIIII                                     
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE) 
//OUT      DD SYSOUT=*                                         
//TOOLIN   DD *                                               
  COPY FROM(IN1) USING(CTL1)                                   
  COPY FROM(IN2) USING(CTL2)                                   
  SPLICE FROM(T1) TO(OUT) ON(01,07,CH) -                       
  WITHALL WITH(01,25) USING(CTL3) KEEPNODUPS KEEPBASE         
//CTL1CNTL DD *                                               
  OUTFIL FNAMES=T1,BUILD=(1,7,18X,8,9)                         
//CTL2CNTL DD *                                               
  OUTFIL FNAMES=T1,BUILD=(1,25,9X)                             
//CTL3CNTL DD *                                               
  OUTFIL FNAMES=OUT,OMIT=(8,18,CH,EQ,C' '),                   
  BUILD=(1,34)                                                 
/*
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: Join two files using DFSORT

Postby pulcinella » Wed May 28, 2008 12:42 pm

The solution is not valid... I obtain the same files in input and output. Is not variation.
The output is:

UUUUUUUOOOOOOOOOOOOOOOOOO
UUUUUUUPPPPPPPPPPPPPPPPPP
VVVVVVVRRRRRRRRRRRRRRRRRR
VVVVVVVYYYYYYYYYYYYYYYYYY
XXXXXXXAAAAAAAAAAAAAAAAAA
XXXXXXXBBBBBBBBBBBBBBBBBB
XXXXXXXCCCCCCCCCCCCCCCCCC
XXXXXXXZZZZZZZZZZZZZZZZZZ
XXXXXXXYYYYYYYYYYYYYYYYYY
YYYYYYYEEEEEEEEEEEEEEEEEE
YYYYYYYFFFFFFFFFFFFFFFFFF
ZZZZZZZIIIIIIIIIIIIIIIIII
ZZZZZZZIIIIIIIIIIIIIIIIII
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join two files using DFSORT

Postby skolusu » Wed May 28, 2008 8:42 pm

pulcinella wrote:The solution is not valid... I obtain the same files in input and output. Is not variation.


pulcinella,

If you had run the job AS IS I have shown you would get the desired results. Pay attention to the DISP parameter of the temp dataset T1. It has MOD which appends the data from both files. You obviously missed that and you are not getting the desired results. Make sure that you copy the JCL as is and re-run your job before you deem the solution as not valid.
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: Join two files using DFSORT

Postby pulcinella » Thu May 29, 2008 11:28 am

skolusu, excuse me,

are you reason. The example is valid... I would know this parameters because i need incorporated an other case

WITHALL WITH(01,25) USING(CTL3) KEEPNODUPS KEEPBASE (25 is the longer of the second file true?)
//CTL1CNTL DD *
OUTFIL FNAMES=T1,BUILD=(1,7,18X,8,9) (18x in the diference with the second file?)
//CTL2CNTL DD *
OUTFIL FNAMES=T1,BUILD=(1,25,9X) (25 is the longer of the second file and 9x the new field?
//CTL3CNTL DD *
OUTFIL FNAMES=OUT,OMIT=(8,18,CH,EQ,C' '), (18 i don't know what's mind
BUILD=(1,34) (25+9, true?)
/*
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join two files using DFSORT

Postby skolusu » Fri May 30, 2008 2:56 am

WITHALL WITH(01,25) USING(CTL3) KEEPNODUPS KEEPBASE (25 is the longer of the second file true?)


yes

/CTL1CNTL DD *
OUTFIL FNAMES=T1,BUILD=(1,7,18X,8,9) (18x in the diference with the second file?)


I am creating the file to the larger file with key in the first 7 bytes and rest of the bytes pad with spaces. Put the contents to be populated on to the first file at the end

//CTL2CNTL DD *
OUTFIL FNAMES=T1,BUILD=(1,25,9X) (25 is the longer of the second file and 9x the new field?


Yes we pad with spaces for the 9 bytes you want to populate from the second file

//CTL3CNTL DD *
OUTFIL FNAMES=OUT,OMIT=(8,18,CH,EQ,C' '), (18 i don't know what's mind
BUILD=(1,34) (25+9, true?)
/*


Look the first CTl1CNTL. We create the second file with 18 spaces . We use this field to eliminate the unmatched records from file 2. i.e if you have record which did not match any record in file 1 , we don't want that in the output, Using the Omit condition we eliminate such records as we deliberately padded the second file with spaces after the key

Hope this helps...
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: Join two files using DFSORT

Postby pulcinella » Fri May 30, 2008 11:32 am

Yes, thank's very much skolusu.
At the first moment I don't understand how work it.
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join two files using DFSORT

Postby pulcinella » Mon Jun 02, 2008 9:14 pm

Excuse me skolusu If i disturb. If you said open a new post I understand...
My question is If I want Join the same files but the files have duplicates... I explain the case

I need join two files know it
the input file1 have 70 positions and the key is the first 54 position
clave A x(54) position
field A1 x(08) position
field A2 x(04) position
field A3 x(03) position
field A4 x(01) position
the input file2 have 109 positions and the key is the first 54 position. His structure is:
clave B x(54) position
field B1 x(10) position
field B2 x(45) position
the output file have 135 position with the next structure
clave (A=B) + field A1 + field A2 + field A3 + field A4 + field B1 + field B2

The clave A and clave B must be equals and have duplicates. (I put an example. I don't
write all fields because only are importante the first 54 position that is the principal keys

field1 (70 position)

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA.............
AAAAAAAAABBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB.............
AAAAAAAAACCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC.............
AAAAAAAAADDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD.............
BBBBBBBBBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA.............
BBBBBBBBBCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC.............
BBBBBBBBBDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD.............
CCCCCCCCCBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB.............
CCCCCCCCCEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE.............

field2 (109 position)

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAxxxxxxxxxxxxx
AAAAAAAAADDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDxxxxxxxxxxxxx
BBBBBBBBBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAxxxxxxxxxxxxx
BBBBBBBBBCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCxxxxxxxxxxxxx
BBBBBBBBBDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDxxxxxxxxxxxxx
CCCCCCCCCEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEExxxxxxxxxxxxx

field output (125 position)

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA.............xxxxxxxxxxxxx
AAAAAAAAADDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD.............xxxxxxxxxxxxx
BBBBBBBBBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA.............xxxxxxxxxxxxx
BBBBBBBBBCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC.............xxxxxxxxxxxxx
BBBBBBBBBDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD.............xxxxxxxxxxxxx
CCCCCCCCCEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE.............xxxxxxxxxxxxx


Could be:

COPY FROM(IN1) USING(CTL1)
COPY FROM(IN2) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(01,54,CH) -
WITHALL WITH(01,109) USING(CTL3) KEEPNODUPS KEEPBASE
//CTL1CNTL DD *
OUTFIL FNAMES=T1,BUILD=(1,54,55X,55,16)
//CTL2CNTL DD *
OUTFIL FNAMES=T1,BUILD=(1,109,16X)
//CTL3CNTL DD *
OUTFIL FNAMES=OUT,OMIT=(55,55,CH,EQ,C' '),
BUILD=(1,125)
/*

Thanks very much
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post