Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Matching two files to one: troubles with ICETOOL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Matching two files to one: troubles with ICETOOL
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 http://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    Post subject:
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    Post subject: Thanks
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    Post subject:
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    Post subject: Reply to: Matching two files to one: troubles with ICETOOL
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Split 1 file into 10 output Files - S... Prasanth Kumar SYNCSORT 5 Sat Sep 16, 2017 12:02 am
No new posts JES2 job size field matching Windows ... SRICOBSAS All Other Mainframe Topics 4 Tue Sep 05, 2017 5:49 pm
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 21 Sun Aug 27, 2017 10:35 pm
No new posts Match two files using multiple keys a... santoshks1987 SYNCSORT 11 Fri Aug 18, 2017 10:50 am
No new posts Multiple VSAM files single output fil... Mohan Kothakota DFSORT/ICETOOL 11 Wed Aug 09, 2017 7:57 pm

Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us