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

Need to find matched records with unmatched fields on second
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 216
Location: USA
PostPosted: Fri Apr 10, 2009 3:06 pm
 Reply with quote
Hi

I have two input files of FB/80. Key is (1,1CH). I need to write FILE1 only records into OUT1; FILE2 only records into OUT2;

The records that are matched on the Key(1,1CH) should be compared against another field again (11,5,CH). Then if the second key is unmatched then they record of FILE2 of the matched record on key(1,1CH) should be written to OUT3.

I will explain with an example.
Code:

//IN1     DD *   input File1
----+----1----+----2----+---
3 amar    11111             
1 vijay     44444             
2 suganya 77777             
4 subbu   66666             
5 brahma  32546             
8 qqqqqq  43568             
9 eeeeee  21234             

//IN2     DD *   input File2
4 subbu   32140             
6 yyyyyyy 22222             
7 xxxxxx  33333             
5 brahma  32546             
8 qqqqqq  43567             
9 eeeeee  21234     


OUT1 is FILE 1 only records

Code:

1 vijay   44444
2 suganya 77777
3 amar    11111


OUT2 is FILE 2 only Records
Code:

6 yyyyyyy 22222
7 xxxxxx  33333


OUT3 is matched records of FILE2 against FILE1 on key(1,1CH) but unmatched on key(11,5,CH)
Code:

4 subbu   32140 
8 qqqqqq  43567



Kindly let me know if you need more information


Regards
Amar
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 216
Location: USA
PostPosted: Fri Apr 10, 2009 3:27 pm
 Reply with quote
Skolusu,

As of I am getting the desired output with the following code. But can you please confirm is this an efficient code or please change it if we can reduce the passes and make it in more efficient manner. I am explaining this since in real application both the files contains 5 Million records each.

Code:

//S1     EXEC PGM=ICETOOL   
//TOOLMSG DD SYSOUT=*       
//DFSMSG  DD SYSOUT=*       
//*                         
//IN1     DD *   input File1
3 amar    11111             
1 vijay   44444             
2 suganya 77777             
4 subbu   66666             
5 brahma  32546             
8 qqqqqq  43568             
9 eeeeee  21234             
//IN2     DD *   input File2
4 subbu   32140             
6 yyyyyyy 22222             
7 xxxxxx  33333                                   
5 brahma  32546                                   
8 qqqqqq  43567                                   
9 eeeeee  21234                                                                   
//T1      DD DSN=&&T1,UNIT=SYSDA,SPACE=(TRK,(5,5)),
//           DISP=(MOD,PASS)                       
//T2      DD DSN=&&T2,UNIT=SYSDA,SPACE=(TRK,(5,5)),
//           DISP=(MOD,PASS)                       
//*                                               
//OUT12   DD SYSOUT=* Names in File1 and File2     
//OUT1    DD SYSOUT=* Names in File1 only         
//OUT2    DD SYSOUT=* Names in File2 only         
//*                                               
//TOOLIN DD *                                               
COPY FROM(IN1) TO(T1) USING(CTL1)                 
COPY FROM(IN2) TO(T1) USING(CTL2)                                     
SELECT FROM(T1) TO(OUT1) ON(1,1,CH) NODUPS  -
USING(CTL3)                                                           
SELECT FROM(T1) TO(T2) ON(1,1,CH) ALLDUPS                             
SELECT FROM(T2) TO(OUT12) ON(1,1,CH) ON(11,5,CH) -                   
NODUPS USING(CTL4)                                                   
/*                                                                   
//*                                                                   
//CTL1CNTL DD  *                                                     
INREC OVERLAY=(17:C'11')                                           
/*                                                                   
//CTL2CNTL DD  *                                                     
INREC OVERLAY=(17:C'22')                                           
/*                 
//CTL3CNTL DD *                                                   
 OUTFIL FNAMES=OUT1,INCLUDE=(17,2,CH,EQ,C'11'),BUILD=(1,15)
 OUTFIL FNAMES=OUT2,INCLUDE=(17,2,CH,EQ,C'22'),BUILD=(1,15)
/*                                                         
//CTL4CNTL DD *                                             
 OUTFIL FNAMES=OUT12,INCLUDE=(17,2,CH,EQ,C'22'),BUILD=(1,15)
/*   


Kindly suggest if we need to go for an effective solution. It is fine even if the above is better.


Regards
Amar
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Fri Apr 10, 2009 8:09 pm
 Reply with quote
V S Amarendra Reddy,

You really don't need that many passes of data. The following DFSORT/ICETOOL JCL will give you the desired results

Code:

//STEP0100 EXEC PGM=ICETOOL                                     
//TOOLMSG  DD SYSOUT=*                                         
//DFSMSG   DD SYSOUT=*                                         
//IN1     DD *                                                 
3 AMAR    11111                                                 
1 VIJAY   44444                                                 
2 SUGANYA 77777                                                 
4 SUBBU   66666                                                 
5 BRAHMA  32546                                                 
8 QQQQQQ  43568                                                 
9 EEEEEE  21234                                                 
//IN2     DD *                                                 
4 SUBBU   32140                                                 
6 YYYYYYY 22222                                                 
7 XXXXXX  33333                                                 
5 BRAHMA  32546                                                 
8 QQQQQQ  43567                                                 
9 EEEEEE  21234                                                 
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)   
//OUT1     DD SYSOUT=*                                         
//OUT2     DD SYSOUT=*                                         
//OUT3     DD SYSOUT=*                                         
//TOOLIN   DD *                                                 
  COPY FROM(IN1) USING(CTL1)                                   
  COPY FROM(IN2) USING(CTL2)                                   
  SPLICE FROM(T1) TO(OUT3) ON(1,1,CH) WITH(1,81) -             
  WITHALL KEEPNODUPS USING(CTL3)                               
//CTL1CNTL DD *                                                 
  OUTFIL FNAMES=T1,OVERLAY=(81:C'11',11,5)                     
//CTL2CNTL DD *                                                 
  OUTFIL FNAMES=T1,OVERLAY=(81:C'22',5X)                       
//CTL3CNTL DD *                                                 
  OUTFIL FNAMES=OUT1,BUILD=(1,80),INCLUDE=(81,2,ZD,EQ,11)       
  OUTFIL FNAMES=OUT2,BUILD=(1,80),INCLUDE=(81,2,ZD,EQ,22)       
  OUTFIL FNAMES=OUT3,BUILD=(1,80),                             
  INCLUDE=(81,2,ZD,EQ,21,AND,(11,5,CH,NE,83,5,CH))             
/*
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 216
Location: USA
PostPosted: Mon Apr 13, 2009 2:21 pm
 Reply with quote
Skolusu,

Thanks a lot for the response. I am getting the desired result. But here when we don't have any dups in FILE2 is it necessary to use WITHALL. How is it going to help us out in this requirement.


Regards
Amar
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Mon Apr 13, 2009 11:16 pm
 Reply with quote
V S Amarendra Reddy,

If you don't have duplicates in FILE2 you can omit the WITHALL
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 Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts To find whether record count are true... DFSORT/ICETOOL 6
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