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 Usergroups Profile Log in to check your private messages Log in
 

 

Need to find matched records with unmatched fields on second

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 200
Location: USA

PostPosted: Fri Apr 10, 2009 3:06 pm    Post subject: Need to find matched records with unmatched fields on second
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: 200
Location: USA

PostPosted: Fri Apr 10, 2009 3:27 pm    Post subject:
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    Post subject:
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: 200
Location: USA

PostPosted: Mon Apr 13, 2009 2:21 pm    Post subject:
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    Post subject: Reply to: Need to find matched records with unmatched fields
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    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 Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts Sum Fields ballaswaroop DFSORT/ICETOOL 4 Tue May 02, 2017 11:07 am
No new posts To find out size allocated to a seque... ashek15 JCL & VSAM 15 Thu Apr 27, 2017 9:42 am


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