Home      Mainframe Forum      Mainfarme Tutorials      IBM Manuals      Mainframe Interview Questions      Mainframe Books      IT News     SiteMap     Downloads


 Home     Mainframe Forum     FAQFAQ    SearchSearch  RegisterRegister   ProfileProfile    Log in to check your private messagesLog in to check your private messages    Log inLog in 
   		  
 
MAINFRAME - TIP OF THE DAY : programming pearls - Debug only code - comments can lie.

Google
 
Web mainframegurukul.com


Visit mainframe jobs page to search mainframes jobs on top job websites.


SPLICE with duplicate records

 
Post new topic   Reply to topic    mainframegurukul.com Forum Index -> DFSORT , ICETOOL & Utilities
  View previous topic :: View next topic  
Author Message
pepejcl
Member


Joined: 09 Feb 2009
Posts: 12
Location: Madrid, SPAIN
PostPosted: Fri Jan 29, 2010 12:43 pm    Post subject: SPLICE with duplicate records Reply with quote
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.
Back to top
View user's profile Send private message

Frank Yaeger
Moderator


Joined: 18 Feb 2006
Posts: 443
Location: San Jose, CA
PostPosted: Fri Jan 29, 2010 8:51 pm    Post subject: Reply with quote
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/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
pepejcl
Member


Joined: 09 Feb 2009
Posts: 12
Location: Madrid, SPAIN
PostPosted: Mon Feb 01, 2010 2:27 pm    Post subject: Reply with quote
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 Confused ...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:

FILE 1
-------
02JUAN    11
04PEDRO   11


Code:

FILE 2
-------
01PEDRO  22
02JUAN1  22
03ALBERTO22
04PEDRO1 22
04PEDRO2 22
05JOSE   22
06LUIS   22
06LUIS   22


Code:
//*                                             
//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:

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:

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.
Back to top
View user's profile Send private message
Frank Yaeger
Moderator


Joined: 18 Feb 2006
Posts: 443
Location: San Jose, CA
PostPosted: Tue Feb 02, 2010 12:24 am    Post subject: Reply with quote
Here's a DFSORT/ICETOOL job using SPLICE that will give the results you asked for:

Code:

//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/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
pepejcl
Member


Joined: 09 Feb 2009
Posts: 12
Location: Madrid, SPAIN
PostPosted: Tue Feb 02, 2010 2:26 pm    Post subject: Reply with quote
Your jcl rocks....This is the diference between a beginner (me) and a guru(you)...i only dare to correct the INREC statements:

Code:

/*
//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.
Back to top
View user's profile Send private message
pepejcl
Member


Joined: 09 Feb 2009
Posts: 12
Location: Madrid, SPAIN
PostPosted: Tue Feb 02, 2010 4:49 pm    Post subject: Reply with quote
may i ask you a final question?.....well, the worst scenario would be this:

Code:

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:

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:

FINAL FILE
------------

02JUAN   11
02INMA   11
02JUAN1  22
04PEDRO  11
04PEDRO1 22
04PEDRO2 22


Thanks a lot and excuse me for disturbing.
Back to top
View user's profile Send private message
Frank Yaeger
Moderator


Joined: 18 Feb 2006
Posts: 443
Location: San Jose, CA
PostPosted: Tue Feb 02, 2010 9:09 pm    Post subject: Reply with quote
Quote:
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/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Frank Yaeger
Moderator


Joined: 18 Feb 2006
Posts: 443
Location: San Jose, CA
PostPosted: Tue Feb 02, 2010 9:17 pm    Post subject: Reply with quote
Again

Quote:
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/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
pepejcl
Member


Joined: 09 Feb 2009
Posts: 12
Location: Madrid, SPAIN
PostPosted: Tue Feb 02, 2010 9:39 pm    Post subject: Reply with quote
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:

//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.
Back to top
View user's profile Send private message
Frank Yaeger
Moderator


Joined: 18 Feb 2006
Posts: 443
Location: San Jose, CA
PostPosted: Wed Feb 03, 2010 5:57 am    Post subject: Reply with quote
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/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
pepejcl
Member


Joined: 09 Feb 2009
Posts: 12
Location: Madrid, SPAIN
PostPosted: Wed Feb 03, 2010 5:10 pm    Post subject: Reply with quote
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:

  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:

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

         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.
Back to top
View user's profile Send private message
Frank Yaeger
Moderator


Joined: 18 Feb 2006
Posts: 443
Location: San Jose, CA
PostPosted: Fri Feb 05, 2010 10:05 pm    Post subject: Reply with quote
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/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
pepejcl
Member


Joined: 09 Feb 2009
Posts: 12
Location: Madrid, SPAIN
PostPosted: Tue Feb 16, 2010 10:19 pm    Post subject: Reply with quote
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 Laughing 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:

//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..)
Back to top
View user's profile Send private message
pepejcl
Member


Joined: 09 Feb 2009
Posts: 12
Location: Madrid, SPAIN
PostPosted: Wed Feb 17, 2010 2:34 pm    Post subject: Reply with quote
Hello again,

finally i have found a solution for my second requirement...it is a three step SPLICE statements like this:

Code:

//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 Smile

Regards.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    mainframegurukul.com Forum Index -> DFSORT , ICETOOL & Utilities All times are GMT + 5 Hours
Page 1 of 1



 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Related topics
 Topics   Replies   Author   Views   Last Post 
No new posts two reports to compare , both different format - URGENT 1 ar3 865 Wed Jul 15, 2009 7:57 pm
Frank Yaeger View latest post
No new posts If both the JOBCAT & STEPCAT statements are coded in a s 1 Krishna 1369 Tue May 12, 2009 6:43 pm
Krishna View latest post
No new posts Commit both in DB2 and MQ 3 jackie 2198 Fri Jul 27, 2007 3:52 pm
dbzTHEdinosauer View latest post
No new posts How to use both lower and upper case letters in cobol? 1 Divya 4209 Fri Mar 09, 2007 10:27 pm
Veera View latest post
No new posts Stop Run in both called and calling programs !!! 2 arrbee 3814 Fri Mar 10, 2006 12:13 pm
jaydeeppal View latest post
 



This widget requires Flash Player 9 or better







Go to top of the page
 

Online ABEND Reference ||  JCL References ||  COBOL References ||  VSAM References ||  Tutorials by Drona Series ||  SQL tutorial ||  BOOKS  ||  DB2 INTERVIEW QUESTIONS ||  COBOL INTERVIEW QUESTIONS  ||  JCL INTERVIEW QUESTIONS ||  JCL2 INTERVIEW QUESTIONS ||  VSAM INTERVIEW QUESTIONS ||  CICS INTERVIEW QUESTIONS  ||  Online tutorials ||  Online ABEND Reference ||  JCL References ||  COBOL References ||  VSAM References ||  Tutorials by Drona Series ||  SQL tutorial ||  BOOKS  ||  SiteMap  ||  Expeditor Tutorial  ||  FILE-AID Tutorial  ||  Changeman Tutorial  ||  COBOL   ||  DB2   ||  JCL  ||  CICS  ||  VSAM  ||  DB2 Interview Questions ( 110 )   || Simple JCL Tutorials  || JCL Tutorial from MainframeGurukul.com   || Simple JCL Tutorial - Chapter1 ;|| Mainframe Forum - Tutorials  || Mainframe Tutorials

Drona Educational Forums - Mainframe Cobol DB2 CICS Board
Powered by phpBB