Page 1 of 2

Merge records of the input file using SORT

PostPosted: Mon May 26, 2008 8:57 pm
by pulcinella
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)

Re: join two files

PostPosted: Mon May 26, 2008 10:00 pm
by dick scherrer
Hello,

Please post the jcl and control statements you are submitting as well as the spooled output from the run.

Re: join two files

PostPosted: Tue May 27, 2008 10:59 am
by pulcinella
//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

Re: Join two files using DFSORT

PostPosted: Tue May 27, 2008 10:15 pm
by skolusu
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)                                                 
/*

Re: Join two files using DFSORT

PostPosted: Wed May 28, 2008 12:42 pm
by pulcinella
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

Re: Join two files using DFSORT

PostPosted: Wed May 28, 2008 8:42 pm
by skolusu
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.

Re: Join two files using DFSORT

PostPosted: Thu May 29, 2008 11:28 am
by pulcinella
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?)
/*

Re: Join two files using DFSORT

PostPosted: Fri May 30, 2008 2:56 am
by skolusu
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...

Re: Join two files using DFSORT

PostPosted: Fri May 30, 2008 11:32 am
by pulcinella
Yes, thank's very much skolusu.
At the first moment I don't understand how work it.

Re: Join two files using DFSORT

PostPosted: Mon Jun 02, 2008 9:14 pm
by pulcinella
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