SPLICE with duplicate records
Moderators: Frank Yaeger, Moderator Group
SPLICE with duplicate records
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.
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.
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
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?
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?
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
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,
...in this jcl with SELECT option i get the 'FINAL FILE'
..and this file don't work for me...I want a file like this,
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.
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')
Code: Select all
FINAL FILE
------------
02JUAN 11
02JUAN1 22
04PEDRO 11
04PEDRO1 22
04PEDRO2 22
06LUIS 22
06LUIS 22
Code: Select all
FINAL FILE
------------
02JUAN 11
02JUAN1 22
04PEDRO 11
04PEDRO1 22
04PEDRO2 22
Thank you again and sorry.
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
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'))
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Your jcl rocks....This is the diference between a beginner (me) and a guru(you)...i only dare to correct the INREC statements:
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.
Code: Select all
/*
//CTL1CNTL DD *
INREC OVERLAY=(1,9,10:C'11')
/*
//CTL2CNTL DD *
INREC OVERLAY=(1,9,10:C'22')
Thank you very much.
P.D: I owe you another beer.
may i ask you a final question?.....well, the worst scenario would be this:
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:
....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:
Thanks a lot and excuse me for disturbing.
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
Code: Select all
FINAL FILE
------------
02JUAN 11
02INMA 11
02JUAN1 22
04PEDRO 11
04PEDRO1 22
04PEDRO2 22
07PEPELU 11
08CARMEN 11
08CARMEN 11
Code: Select all
FINAL FILE
------------
02JUAN 11
02INMA 11
02JUAN1 22
04PEDRO 11
04PEDRO1 22
04PEDRO2 22
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
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.i only dare to correct the INREC statements
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
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?
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
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:
....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.
...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
Thx Frank.
P.D.: sorry my english.
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
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.
I'm on vacation until Friday. I can help you with your new requirement then.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
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,
but where i'm working if i use the ones you used,
i get the next result in the output file (with the files for the first requirement),
...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.
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')
Code: Select all
...
INREC OVERLAY=(10:C'11')
...
INREC OVERLAY=(10:C'22')
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
...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.
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
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.
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.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
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:
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..)
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')
/*
Thank you.
(..sorry my english..)
Hello again,
finally i have found a solution for my second requirement...it is a three step SPLICE statements like this:
..i don't know if there is a better solution but this works for me...hope to you too
Regards.
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)
/*
Regards.
-
- Member
- Posts: 1
- Joined: Tue Apr 27, 2010 11:34 am
FREE TUTORIALS
Tutorials
Free tutorials from mainframegurukul
- JCL Tutorial
Covers all important JCL concepts. - Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3. - DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. - SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples - CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page