SPLICE with duplicate records

In this Mainframe Forum - You can post your queries on DFSORT, ICETOOL , SyncSort & JCL Utilities

Moderators: Frank Yaeger, Moderator Group

pepejcl
Member
Posts: 12
Joined: Mon Feb 09, 2009 2:11 pm
Location: Madrid, SPAIN

SPLICE with duplicate records

Post by pepejcl » Fri Jan 29, 2010 12:43 pm

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.

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Fri Jan 29, 2010 8:51 pm

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

pepejcl
Member
Posts: 12
Joined: Mon Feb 09, 2009 2:11 pm
Location: Madrid, SPAIN

Post by pepejcl » Mon Feb 01, 2010 2:27 pm

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.

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Tue Feb 02, 2010 12:24 am

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

pepejcl
Member
Posts: 12
Joined: Mon Feb 09, 2009 2:11 pm
Location: Madrid, SPAIN

Post by pepejcl » Tue Feb 02, 2010 2:26 pm

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.

pepejcl
Member
Posts: 12
Joined: Mon Feb 09, 2009 2:11 pm
Location: Madrid, SPAIN

Post by pepejcl » Tue Feb 02, 2010 4:49 pm

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.

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Tue Feb 02, 2010 9:09 pm

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

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Tue Feb 02, 2010 9:17 pm

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

pepejcl
Member
Posts: 12
Joined: Mon Feb 09, 2009 2:11 pm
Location: Madrid, SPAIN

Post by pepejcl » Tue Feb 02, 2010 9:39 pm

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.

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Wed Feb 03, 2010 5:57 am

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

pepejcl
Member
Posts: 12
Joined: Mon Feb 09, 2009 2:11 pm
Location: Madrid, SPAIN

Post by pepejcl » Wed Feb 03, 2010 5:10 pm

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.

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Fri Feb 05, 2010 10:05 pm

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

pepejcl
Member
Posts: 12
Joined: Mon Feb 09, 2009 2:11 pm
Location: Madrid, SPAIN

Post by pepejcl » Tue Feb 16, 2010 10:19 pm

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 :lol: 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..)

pepejcl
Member
Posts: 12
Joined: Mon Feb 09, 2009 2:11 pm
Location: Madrid, SPAIN

Post by pepejcl » Wed Feb 17, 2010 2:34 pm

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.

iam_ksairam
Member
Posts: 1
Joined: Tue Apr 27, 2010 11:34 am

Post by iam_ksairam » Tue Apr 27, 2010 11:43 am

:D 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 ---).

Post Reply

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



Other References
Mainframe Tools and others