Page 1 of 2
SPLICE with duplicate records
Posted: Fri Jan 29, 2010 12:43 pm
by pepejcl
Hello everyone,
i've been playing lastly with ICETOOL and SPLICE option but i don't find the right way to solve my problem....i have 2 files, here it is an example:
FILE 1
-------
02 JUAN 11
04 PEDRO 11
05 JOSE 11
FILE 2
-------
01 PEDRO 22
02 JUAN 22
03 ALBERTO 22
04 PEDRO 22
04 PEDRO 22
05 JOSE 22
as you see the second file has duplicated records and would like to get a file not only with the records in file 1 that are in file 2 but with ALL the records that are in both files even if they are duplicated,
FINAL FILE
------------
02 JUAN 11
02 JUAN 22
04 PEDRO 11
04 PEDRO 22
04 PEDRO 22
05 JOSE 11
05 JOSE 22
so i don't want a file like this,
02 JUAN 12
04 PEDRO 12
05 JOSE 12
The final file is perfect for me because i can discriminate from which file a record is.........making an OUTREC in both files, the first one with 11 and the second with 22 and with SELECT option and ON(1,2,CH) ALLDUPS i get a solution.......but i wonder if there is a solution with SPLICE...
Thank you very much everyone.
Posted: Fri Jan 29, 2010 8:51 pm
by Frank Yaeger
I don't understand. Why do you want to use SPLICE instead of SELECT?
Are the 11 and 22 ids actually in the input file or are you putting them there to use for the SPLICE? Do they need to be in the output file?
What is the RECFM and LRECL of the input files?
What is the starting position, length and format of the relevant fields?
Posted: Mon Feb 01, 2010 2:27 pm
by pepejcl
You are right Frank, excuse me, i put two files 'FILE 1' and 'FILE 2' as an example but it was my fault not to describe the file format AND MOREOVER they were wrong
...in this case the key number is in position 1-2 and name in position 3-9 but this is only informative to understand the example,
Code: Select all
FILE 1
-------
02JUAN 11
04PEDRO 11
Code: Select all
FILE 2
-------
01PEDRO 22
02JUAN1 22
03ALBERTO22
04PEDRO1 22
04PEDRO2 22
05JOSE 22
06LUIS 22
06LUIS 22
Code: Select all
//*
//TEMPO DD DSN=APE.SGU1865.TEMPO,
// DISP=(MOD,CATLG,CATLG),
// SPACE=(CYL,(1,1),RLSE),UNIT=SYSDA,
// DCB=(RECFM=FB,LRECL=11,BLKSIZE=0)
//SAL DD DSN=APE.SGU1865.IGUALES,
// DISP=(NEW,CATLG,CATLG),
// SPACE=(CYL,(1,1),RLSE),UNIT=SYSDA,
// DCB=(RECFM=FB,LRECL=11,BLKSIZE=0)
//TOOLIN DD *
COPY FROM(FILE1) TO(TEMPO) USING(CTL1)
COPY FROM(FILE2) TO(TEMPO) USING(CTL2)
SELECT FROM(TEMPO) TO(SAL) ON(1,2,CH) ALLDUPS
//*
//CTL1CNTL DD *
OUTREC FIELDS=(1,9,C'11')
//*
//CTL2CNTL DD *
OUTREC FIELDS=(1,9,C'22')
...in this jcl with SELECT option i get the 'FINAL FILE'
Code: Select all
FINAL FILE
------------
02JUAN 11
02JUAN1 22
04PEDRO 11
04PEDRO1 22
04PEDRO2 22
06LUIS 22
06LUIS 22
..and this file don't work for me...I want a file like this,
Code: Select all
FINAL FILE
------------
02JUAN 11
02JUAN1 22
04PEDRO 11
04PEDRO1 22
04PEDRO2 22
so my question is only if with SPLICE i can get the file without '06LUIS' that is not in FILE 1.
Thank you again and sorry.
Posted: Tue Feb 02, 2010 12:24 am
by Frank Yaeger
Here's a DFSORT/ICETOOL job using SPLICE that will give the results you asked for:
Code: Select all
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//FILE1 DD DSN=... input file1
//FILE2 DD DSN=... input file2
//TEMPO DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//SAL DD DSN=... output file
//TOOLIN DD *
COPY FROM(FILE1) TO(TEMPO) USING(CTL1)
COPY FROM(FILE2) TO(TEMPO) USING(CTL2)
SPLICE FROM(TEMPO) TO(SAL) ON(1,2,CH) WITHALL KEEPNODUPS KEEPBASE-
WITH(1,10) USING(CTL3)
/*
//CTL1CNTL DD *
INREC OVERLAY=(10:C'11')
/*
//CTL2CNTL DD *
INREC OVERLAY=(10:C'22')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=SAL,INCLUDE=(10,2,SS,EQ,C'11,21'),
IFTHEN=(WHEN=(10,2,CH,EQ,C'21'),OVERLAY=(10:C'22'))
/*
Posted: Tue Feb 02, 2010 2:26 pm
by pepejcl
Your jcl rocks....This is the diference between a beginner (me) and a guru(you)...i only dare to correct the INREC statements:
Code: Select all
/*
//CTL1CNTL DD *
INREC OVERLAY=(1,9,10:C'11')
/*
//CTL2CNTL DD *
INREC OVERLAY=(1,9,10:C'22')
I'm gonna study this piece of code...you are saving me from now on lots of program lines...
Thank you very much.
P.D: I owe you another beer.
Posted: Tue Feb 02, 2010 4:49 pm
by pepejcl
may i ask you a final question?.....well, the worst scenario would be this:
Code: Select all
FILE 1
-------
02JUAN 11
02INMA 11
04PEDRO 11
07PEPELU 11
08CARMEN 11
08CARMEN 11
FILE 2
-------
01PEDRO 22
02JUAN1 22
03ALBERTO22
04PEDRO1 22
04PEDRO2 22
05JOSE 22
06LUIS 22
06LUIS 22
as you can see now we have in FILE 1 records with key that don't exist in FILE 2 and with the previous jcl we get a file like this:
Code: Select all
FINAL FILE
------------
02JUAN 11
02INMA 11
02JUAN1 22
04PEDRO 11
04PEDRO1 22
04PEDRO2 22
07PEPELU 11
08CARMEN 11
08CARMEN 11
....it works almost perfect, even with duplicate key 02 in FILE 1 we get a good FINAL FILE except keys 07 and 08 that i don't want....i would like to get this file:
Code: Select all
FINAL FILE
------------
02JUAN 11
02INMA 11
02JUAN1 22
04PEDRO 11
04PEDRO1 22
04PEDRO2 22
Thanks a lot and excuse me for disturbing.
Posted: Tue Feb 02, 2010 9:09 pm
by Frank Yaeger
i only dare to correct the INREC statements
The INREC statements I supplied are correct as written. Using 1,9 in OVERLAY is redundant and unnecesary since it will just "overlay" 1,9 with 1,9 - a NOP.
Posted: Tue Feb 02, 2010 9:17 pm
by Frank Yaeger
Again
Are the 11 and 22 ids actually in the input file or are you putting them there to use for the SPLICE? Do they need to be in the output file?
Posted: Tue Feb 02, 2010 9:39 pm
by pepejcl
About the INREC statement must be a problem with the DFSORT version installed at work because it leaves me blanks....
...and about the 11 and 22 ids are not in the input file at all...i'm using these entries in my jcl to understand the use of SPLICE command:
Code: Select all
//FILE1 DD *
02JUAN
02INMA
04PEDRO
07PEPELU
08CARMEN
08CARMEN
//*
//FILE2 DD *
01PEDRO
02JUAN1
03ALBERTO
04PEDRO1
04PEDRO2
05JOSE
06LUIS
06LUIS
....i only use them in the FINAL FILE to identify from which file a record come from...for the time being i've been reading examples and it seems and what i understand they are used for the SPLICE command, but in my case these ids are useful to be in the FINAL FILE but not mandatory....
Thx Frank.
P.D.: sorry my english.
Posted: Wed Feb 03, 2010 5:57 am
by Frank Yaeger
I don't understand what you mean about the INREC statement. OVERLAY=(1,9,10:C'11') and OVERLAY=(10:C'11') perform the identical function. You'd have to show me what you're doing and the difference you think you see before I could comment further.
I'm on vacation until Friday. I can help you with your new requirement then.
Posted: Wed Feb 03, 2010 5:10 pm
by pepejcl
Hello again Frank,
There is no problem with INREC statements Frank, don't worry about that, for me is the same the next two sentences,
Code: Select all
INREC OVERLAY=(1,9,10:C'11')
or
INREC OVERLAY=(1:1,9,10:C'11')
(the same case for C'22')
but where i'm working if i use the ones you used,
Code: Select all
...
INREC OVERLAY=(10:C'11')
...
INREC OVERLAY=(10:C'22')
i get the next result in the output file (with the files for the first requirement),
Code: Select all
11
11
22
22
22
22
22
22
22
22
instead of
02JUAN 11
02JUAN1 22
04PEDRO 11
04PEDRO1 22
04PEDRO2 22
as we expected
...that is only so i corrected your INREC statement to work for me, apart from that your jcl works perfect Frank........probably there is a different DFSORT version installed in your work....
...in any case i would appreciate if you give me a hand with my second requierement when you return from your vacations....till then, enjoy your holidays !!!
Thank you.
Posted: Fri Feb 05, 2010 10:05 pm
by Frank Yaeger
There is no version of DFSORT I know of that would give the result you show for those INREC statements. So there's something you're not showing that might make a difference in the solution I give you for your second requirement.
Please send me (
yaeger@us.ibm.com) the complete JESLOGs for the jobs with and without the 1,9 so I can see what's going on. That will also tell me which version/level of DFSORT you're using which will help with the solution you want.
Posted: Tue Feb 16, 2010 10:19 pm
by pepejcl
Hello everyone,
first of all i want to thank Frank for his help, it is priceless...finally i have found a solution for my second requirement that i will describe bellow.....if you read the whole post you will see that i had some problems with INREC OVERLAY statement but it was my fault...i have no problem at all finally, i was obfuscated replying that INREC OVERLAY statements didn't work for me but in fact i was using INREC FIELDS instead
i'm so so sorry Frank....
...and for my second requirement i have found a simple solution because now i don't need the records marked with '11' in my 'FINAL FILE' but the records found marked with '21' and not found marked with '22'....so with this piece of code:
Code: Select all
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//FILE1 DD DSN=... input file1
//FILE2 DD DSN=... input file2
//TEMPO DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//SAL DD DSN=... output file
//TOOLIN DD *
COPY FROM(FILE1) TO(TEMPO) USING(CTL1)
COPY FROM(FILE2) TO(TEMPO) USING(CTL2)
SPLICE FROM(TEMPO) TO(SAL) ON(1,2,CH) WITHALL KEEPNODUPS KEEPBASE-
WITH(1,10) USING(CTL3)
/*
//CTL1CNTL DD *
INREC OVERLAY=(10:C'11')
/*
//CTL2CNTL DD *
INREC OVERLAY=(10:C'22')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=SAL,INCLUDE=(10,2,SS,EQ,C'21,22')
/*
i have all my problems solved...i've been using BUILD=(1,9) to eliminate '21' and '22' marks, IFTHEN=(WHEN=NONE for other stuffs etc etc... redirecting the output in two files with other OUTFIL FNAMES...in the end this jcl rocks !!!...at my work place we have been doing some tasks lastly and everything worked perfect....As you can understand this jcl was only for educational purpose, an example, and not a real one with files that i use in my work (normally FILE1 and FILE2 has different lenght and i have to rebuild the records from FILE1 to match the key with FILE2 with INREC FIELDS), but it was very good to understand everything......
Thank you.
(..sorry my english..)
Posted: Wed Feb 17, 2010 2:34 pm
by pepejcl
Hello again,
finally i have found a solution for my second requirement...it is a three step SPLICE statements like this:
Code: Select all
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//FILE1 DD DSN=... input file1
//FILE2 DD DSN=... input file2
//TEMPO DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//TEMPO1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//TEMPO2 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//SAL DD DSN=... output file
//SAL1 DD DSN=... output file
//SAL2 DD DSN=... output file
//TOOLIN DD *
COPY FROM(FILE1) TO(TEMPO) USING(CTL1)
COPY FROM(FILE2) TO(TEMPO) USING(CTL2)
SPLICE FROM(TEMPO) TO(SAL) ON(1,2,CH) WITHALL KEEPNODUPS KEEPBASE-
WITH(1,10) USING(CTL3)
COPY FROM(SAL) TO(TEMPO1) USING(CTL1)
COPY FROM(FILE1) TO(TEMPO1) USING(CTL2)
SPLICE FROM(TEMPO1) TO(SAL1) ON(1,2,CH) WITHALL KEEPNODUPS KEEPBASE-
WITH(1,10) USING(CTL4)
COPY FROM(SAL1) TO(TEMPO2) USING(CTL1)
COPY FROM(FILE2) TO(TEMPO2) USING(CTL2)
SPLICE FROM(TEMPO2) TO(SAL2) ON(1,2,CH) WITHALL KEEPNODUPS KEEPBASE-
WITH(1,10) USING(CTL5)
/*
//CTL1CNTL DD *
INREC OVERLAY=(10:C'11')
/*
//CTL2CNTL DD *
INREC OVERLAY=(10:C'22')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=SAL,INCLUDE=(10,2,SS,EQ,C'21'),BUILD=(1,9)
/*
//CTL4CNTL DD *
OUTFIL FNAMES=SAL1,INCLUDE=(10,2,SS,EQ,C'21'),BUILD=(1,9)
/*
//CTL5CNTL DD *
OUTFIL FNAMES=SAL2,INCLUDE=(10,2,SS,EQ,C'11,21'),BUILD=(1,9)
/*
..i don't know if there is a better solution but this works for me...hope to you too
Regards.
Posted: Tue Apr 27, 2010 11:43 am
by iam_ksairam
Superbb!! , I thought that if there are dup records in both files then we can't use splice, but you showed a new approach (but it is a bit ---).