IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Matching two files to one: troubles with ICETOOL
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
warp22

New User


Joined: 13 Nov 2008
Posts: 11
Location: ALICANTE - Spain
PostPosted: Fri Nov 14, 2008 2:28 pm
 Reply with quote
Hi to everyone and thank you for your info. please sorry for my english icon_smile.gif

I have two input files with a key field in both. I need to make a match to get an output file with only the necessary fields: is the same structure of the second.

The file 1 contains a list of customers, and the file 2 all those clients and a number of fields below the key.

The result i need is in the output file should be all the records in the second file that exist in file 1.

An example:

File 1
<- KEY->
11111111999999 TEXT1 TEXT2 TEXT 3
22222222999999 TEXT1 TEXT2 TEXT 3
55555555999999 TEXT1 TEXT2 TEXT 3
66666666999999 TEXT1 TEXT2 TEXT 3

File 2
<- KEY->
111111110999 TEXT1 [...] TEXT2 TEXT3 [...]
111111110999 TEXT6 [...] TEXT6 TEXT10 [...]
333333330999 TEXTX [...]TEXT2 TEXT15 [...]
333333330999 TEXTX [...]TEXT? TEXT15 [...]
333333330999 TEXTYY [...]TEXTN TEXTH [...]
444444440999 TEXTZZ [...]TEXTB TEXT10 [...]
444444440999 TEXTZZ [...]TEXTB TEXT10 [...]
444444440999 TEXTZZ [...]TEXTB TEXT10 [...]
555555550999 TEXTZZ [...]TEXTB TEXT10 [...]
555555550999 TEXTZZ [...]TEXTB TEXT10 [...]
555555550999 TEXTZZ [...]TEXTB TEXT10 [...]

Being TEXTXX text or numeric fields...
Key an ON(1,8) on both files.

And the exit i want must be:

Exit
<- KEY->
111111110999 TEXT1 [...] TEXT2 TEXT3 [...]
111111110999 TEXT6 [...] TEXT6 TEXT10 [...]
555555550999 TEXTZZ [...]TEXTB TEXT10 [...]
555555550999 TEXTZZ [...]TEXTB TEXT10 [...]
555555550999 TEXTZZ [...]TEXTB TEXT10 [...]


I've been trying the sample of Splice Operator ('11 ','22' and'12 ', ...) from publibz.boulder.ibm.com but it is still not make it work.
I do not know how to use the WITH ..
Someone could help me please ... thank you very much
Back to top
View user's profile Send private message
nelson.pandian

Active User


Joined: 09 Apr 2008
Posts: 133
Location: Phoenix, AZ
PostPosted: Fri Nov 14, 2008 3:57 pm
 Reply with quote
I have assumed the RECFM=FB and LRECL=80. This DFSORT Job will give you desire output.


Code:
//STEP10A  EXEC PGM=ICETOOL                 
//*                                         
//TOOLMSG      DD  SYSOUT=*                 
//DFSMSG       DD  SYSOUT=*                 
//*                                         
//I1           DD  *                       
11111111999999 TEXT1 TEXT2 TEXT 3           
22222222999999 TEXT1 TEXT2 TEXT 3           
55555555999999 TEXT1 TEXT2 TEXT 3           
66666666999999 TEXT1 TEXT2 TEXT 3           
/*                                         
//*                                         
//I2           DD  *                       
111111110999 TEXT1 [...] TEXT2 TEXT3 [...] 
111111110999 TEXT6 [...] TEXT6 TEXT10 [...]
333333330999 TEXTX [...]TEXT2 TEXT15 [...] 
333333330999 TEXTX [...]TEXT? TEXT15 [...] 
333333330999 TEXTYY [...]TEXTN TEXTH [...] 
444444440999 TEXTZZ [...]TEXTB TEXT10 [...]
444444440999 TEXTZZ [...]TEXTB TEXT10 [...]       
444444440999 TEXTZZ [...]TEXTB TEXT10 [...]       
555555550999 TEXTZZ [...]TEXTB TEXT10 [...]       
555555550999 TEXTZZ [...]TEXTB TEXT10 [...]       
555555550999 TEXTZZ [...]TEXTB TEXT10 [...]       
/*                                                 
//*                                               
//T1           DD  DSN=&&T1,                       
//            DISP=(MOD,PASS),                     
//            UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE) 
//*                                               
//O1           DD  DSN=(OUTFILE --> 80 Length),       
//            DISP=(NEW,CATLG,DELETE),             
//            UNIT=(SYSDA,5),                     
//            SPACE=(TRK,(200,200),RLSE)         
//*                                               
//TOOLIN   DD  *    *** CONSTANT CONTROL CARDS ***
  COPY FROM(I1) TO(T1) USING(CTL1)                 
   COPY FROM(I2) TO(T1) USING(CTL2)                       
   SPLICE FROM(T1) TO(O1) ON(1,8,CH) KEEPNODUPS KEEPBASE -
     WITHALL WITH(1,81) USING(CTL3)                       
 /*                                                       
 //CTL1CNTL DD *                                         
   INREC OVERLAY=(81:C'11')                               
 /*                                                       
 //CTL2CNTL DD *                                         
   INREC OVERLAY=(81:C'22')                               
 /*                                                       
 //CTL3CNTL DD *                                         
   OUTFIL FNAMES=O1,INCLUDE=(81,2,CH,EQ,C'21'),BUILD=(1,80)           
 /*                                                       


OutPut:
Code:
111111110999 TEXT1 [...] TEXT2 TEXT3 [...]
111111110999 TEXT6 [...] TEXT6 TEXT10 [...]
555555550999 TEXTZZ [...]TEXTB TEXT10 [...]
555555550999 TEXTZZ [...]TEXTB TEXT10 [...]
555555550999 TEXTZZ [...]TEXTB TEXT10 [...]
Back to top
View user's profile Send private message
warp22

New User


Joined: 13 Nov 2008
Posts: 11
Location: ALICANTE - Spain
PostPosted: Thu Nov 20, 2008 6:08 pm
 Reply with quote
nelson.pandian thank you very much for the help and for such a rapid response
It works perfectly.

Thank you very much
Back to top
View user's profile Send private message
VIGNRSH
Warnings : 1
New User


Joined: 18 Mar 2007
Posts: 44
Location: New Jersey,USA
PostPosted: Wed Feb 04, 2009 11:39 am
 Reply with quote
Hi,
How do i need to modify the same program for this requirement?

File1:
Key pos (1,8)
Text Cols occurs in 20 len=10

File2:
Key pos (1,8)
Text Cols occurs in 40 len=30

Here all the records n File2 must be matched with file 1 ; File 2 may have duplicate KEYS and not duplicate Records. I need all those Duplicate key records in file2 to merged with a unique records in file1 .

Code:

File1:
Key123456  TextA
Key123457  TextB
Key123458  TextC
Key123459  TextD

File2:
Key123456  Text2
Key123456  Text2
Key123456  Text2
Key123457  Text3
Key123457  Text3
Key123458  Text4

o/p:
Key123456  TextA Text2
Key123456  TextA Text2
Key123456  TextA Text2
Key123457  TextB Text3
Key123457  TextB Text3
Key123458  TextC Text4





Thanks
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida
PostPosted: Wed Feb 04, 2009 2:11 pm
 Reply with quote
Hi,

Try using the below code :
O/P file breakup:
1-39 (from File1)
40 -69 (from File 2)
70 - 80 (from File1)

Code:

//STEP100  EXEC PGM=ICETOOL                 
//*                                         
//TOOLMSG      DD  SYSOUT=*                 
//DFSMSG       DD  SYSOUT=*                 
//*                                         
//I1           DD  *                       
Key123456  TextA
Key123457  TextB
Key123458  TextC
Key123459  TextD
/*                                         
//*             
                           
//I2           DD  *                       
Key123456        Text2
Key123456        Text2
Key123456        Text2
Key123457        Text3
Key123457        Text3
Key123458        Text4
/*                                                 
//*                                               
//T1           DD  DSN=&&T1,                       
//            DISP=(MOD,PASS),                     
//            UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE)
//*                                               
//O1           DD  DSN=(OUTFILE --> 80 Length),       
//            DISP=(NEW,CATLG,DELETE),             
//            UNIT=(SYSDA,5),                     
//            SPACE=(TRK,(200,200),RLSE)         
//*                                               
//TOOLIN   DD  *   
  COPY FROM(I1) TO(T1) USING(CTL1)                 
   COPY FROM(I2) TO(T1) USING(CTL2)                       
   SPLICE FROM(T1) TO(O1) ON(1,8,CH) KEEPNODUPS -
     WITHALL WITH(40,30) with(81,1) USING(CTL3)                       
 /*                                                       
 //CTL1CNTL DD *                                         
   INREC OVERLAY=(81:C'11')                               
 /*                                                       
 //CTL2CNTL DD *                                         
   INREC OVERLAY=(81:C'22')                               
 /*                                                       
 //CTL3CNTL DD *                                         
   OUTFIL FNAMES=O1,INCLUDE=(81,2,CH,EQ,C'21'),BUILD=(1,80)           
 /*                                                       


Thanks,
-Kapil.
Back to top
View user's profile Send private message
VIGNRSH
Warnings : 1
New User


Joined: 18 Mar 2007
Posts: 44
Location: New Jersey,USA
PostPosted: Wed Feb 04, 2009 8:04 pm
 Reply with quote
the o/p iam getting after executing this code!!
Code:

KEY123456  TEXTA
KEY123456  TEXTA
KEY123456  TEXTA
KEY123456  TEXTA
KEY123457  TEXTB
KEY123457  TEXTB
KEY123458  TEXTC


But iam getting the right hand side text of the file 2
TEXT2
TEXT2
TEXT2
TEXT2
TEXT3
TEXT3
TEXT4
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed Feb 04, 2009 9:59 pm
 Reply with quote
vignrsh,

The following DFSORT/ICETOOL JCL will give you the desired results

Code:

//STEP0100 EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//IN1      DD *                                                     
KK123456           TEXTA                                           
KK123457           TEXTB                                           
KK123458           TEXTC                                           
KK123459           TEXTD                                           
//IN2      DD *                                                     
KK123456                               TEXT2                       
KK123456                               TEXT2                       
KK123456                               TEXT2                       
KK123457                               TEXT3                       
KK123457                               TEXT3                       
KK123458                               TEXT4                       
//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(1,8,CH) WITHALL WITH(01,80) USING(CTL3)
//CTL1CNTL DD *                                                     
  OUTFIL FNAMES=T1,BUILD=(1,8,81:1,8,20,10)                         
//CTL2CNTL DD *                                                     
  OUTFIL FNAMES=T1,OVERLAY=(81:18X)                                 
//CTL3CNTL DD *                                                     
  OUTFIL FNAMES=OUT,BUILD=(01,19,89,10,40,30),                     
  INCLUDE=(1,8,CH,EQ,81,8,CH)                                       
/*
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts Compare 2 files(F1 & F2) and writ... JCL & VSAM 8
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Shift left VB record without x00 endi... DFSORT/ICETOOL 11
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us