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
 

 

Match two files, create 3rd file w/fields from both I/Ps

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Jenifer Lewis

New User


Joined: 14 Sep 2009
Posts: 28
Location: Maine

PostPosted: Wed Sep 03, 2014 2:32 am    Post subject: Match two files, create 3rd file w/fields from both I/Ps
Reply with quote

I am looking for an ICETOOL solution to the following problem, as our DFSORT release (ICE201I F) is not recent enough to contain JOINKEYS.

Two comma-delimited files (LRECL=80) with no duplicates in either file:

File A:

1111111111111,AAAAAAAAAA,
2222222222222,BBBBBBBBBB,
3333333333333,CCCCCCCCCC,

File B:

1111111111111,XXXXXXXXXXXXXX,
3333333333333,YYYYYYYYYYYYYY,
3333333333333,ZZZZZZZZZZZZZZ,

I need to compare File A with File B, matching on first 13 bytes, then create a third comma-delimited output file consisting of File B's second field, File A's first field, and File A's second field.

Desired results:

XXXXXXXXXXXXXX,1111111111111,AAAAAAAAAA
YYYYYYYYYYYYYY,3333333333333,CCCCCCCCCC
ZZZZZZZZZZZZZZ,3333333333333,CCCCCCCCCC

Thank you for any help you can provide!
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Wed Sep 03, 2014 4:32 am    Post subject: Reply to: Match two files, create 3rd file w/fields from bot
Reply with quote

Well, there's SPLICE.

You say "no duplicates" but show something which looks pretty duplicate in your sample and expected results. Can you be a bit clearer, please?
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Sep 03, 2014 12:39 pm    Post subject:
Reply with quote

Bill has already pointed out, that there are duplicates in your sample. You can try the something similar to the below job which uses SPLICE and WITHALL to handle duplicates. If you don't have duplicates, you can remove the WITHALL parameter.

Code:
//STEP01   EXEC PGM=ICETOOL                               
//TOOLMSG  DD SYSOUT=*                                     
//DFSMSG   DD SYSOUT=*                                     
//IN1      DD *                                           
1111111111111,AAAAAAAAAA,                                 
2222222222222,BBBBBBBBBB,                                 
3333333333333,CCCCCCCCCC,                                 
//IN2      DD *                                           
1111111111111,XXXXXXXXXXXXXX,                             
3333333333333,YYYYYYYYYYYYYY,                             
3333333333333,ZZZZZZZZZZZZZZ,                             
//TMP1     DD DSN=&&TEMP1,UNIT=SYSDA,SPACE=(CYL,(5,5)),   
//            DISP=(MOD,PASS)                             
//OUT      DD SYSOUT=*                                     
//TOOLIN   DD *                                           
  COPY FROM(IN1) TO(TMP1) USING(CPY1)                     
  COPY FROM(IN2) TO(TMP1) USING(CPY2)                     
  SPLICE FROM(TMP1) TO(OUT) ON(16,13,CH) WITHALL WITH(1,14)
//CPY1CNTL DD *                                           
  OUTREC BUILD=(14X,15:C',',16:1,13,C',',30:15,10)         
//CPY2CNTL DD *                                           
  OUTREC BUILD=(1:15,14,15:C',',16:1,13,C',',30:10X)       


I don't have DFSORT, so this solution is NOT tested.
Back to top
View user's profile Send private message
Jenifer Lewis

New User


Joined: 14 Sep 2009
Posts: 28
Location: Maine

PostPosted: Wed Sep 03, 2014 5:34 pm    Post subject:
Reply with quote

I apologize for not being a bit clearer: there are no duplicates within either File A or File B.

The results file should contain only those records which are on both files.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Sep 03, 2014 5:52 pm    Post subject:
Reply with quote

@Jenifer: Did you try running the solution that I posted. Are you getting the desired output?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Wed Sep 03, 2014 6:05 pm    Post subject: Reply to: Match two files, create 3rd file w/fields from bot
Reply with quote

And what is this if not duplicate?
Code:

3333333333333
3333333333333



Did you try mistah kurtz's code? If someone gives you a SPLICE for nothing, you should at least try it.
Back to top
View user's profile Send private message
Jenifer Lewis

New User


Joined: 14 Sep 2009
Posts: 28
Location: Maine

PostPosted: Wed Sep 03, 2014 6:05 pm    Post subject:
Reply with quote

What a busy morning this has been, mistah kurtz!

Okay, I just tried it, and I'm getting everything from File B, reformatted of course. So when there is a match between the two input files, data in the second field of File A shows up in the third field of the results file, and when there is not, the third field contains just spaces.

The ultimate goal is to have a results file with only those records from File B which match File A. I can sort the results to exclude those records with spaces in the third file, but I'd rather do it all in one step.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Sep 03, 2014 6:45 pm    Post subject:
Reply with quote

you are getting below Output?
Code:
XXXXXXXXXXXXXX,1111111111111,AAAAAAAAAA
              ,2222222222222,BBBBBBBBBB
YYYYYYYYYYYYYY,3333333333333,CCCCCCCCCC
ZZZZZZZZZZZZZZ,3333333333333,CCCCCCCCCC
Back to top
View user's profile Send private message
Jenifer Lewis

New User


Joined: 14 Sep 2009
Posts: 28
Location: Maine

PostPosted: Thu Sep 04, 2014 1:48 am    Post subject:
Reply with quote

Here's the problem: I supplied a File B that would always find a match in File A, which is not reality-based in this application. My apologies.

(I apologize for my absence. I've spent nearly all day on a production problem for a legacy system caused by one of the business users messing with last night's batch schedule, not telling any of us what was going on, then complaining this morning that things didn't go well. To think I get paid to have all this fun. What made me think I could work on new development today? Silly me.)

Okay, so when I run the JCL you so helpfully provided with the test data I created for this example, it works fine. When I use the test files I created from the actual database, I get the results described in my prior message.

Here is File A in its entirety:

Code:
031745453MTOU,SOP-MTOU  , 
143184450DTOU,SOP-DTOU  , 
143184450STOU,SOP-DTOU  , 
205576411ME03,SOP-DTOU  , 
963409698MTOU,CEP-MTOU  , 
987654321ME03,SOP-DTOU  , 


Here is a smaller subset of File B:

Code:
0142671371277,02210042524013,    <-- doesn't match
0142671371277,02210115658013,    <-- doesn't match
031745453MTOU,02110539470001,   <-- match
031745453MTOU,05140491084001,   <-- match


Here are the results -- what the heck is that first record doing there?

Code:
02210115658013,0142671371277,       
02110539470001,031745453MTOU,SOP-MTOU
05140491084001,031745453MTOU,SOP-MTOU


I apologize again -- I just looked at the clock and I have to go home. I'll check back in the morning. Have a good night!

Code'd
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Sep 04, 2014 10:32 am    Post subject:
Reply with quote

Please have a look at the splice here as well and reformat as per the need. Also your example still shows duplicates and resulting into cartesian join.
http://pic.dhe.ibm.com/infocenter/zos/v1r13/index.jsp?topic=%2Fcom.ibm.zos.r13.iceg200%2Fice1cg6054.htm

Tricks
ftp://ftp.software.ibm.com/storage/dfsort/mvs/sorttrck.pdf
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Thu Sep 04, 2014 11:50 am    Post subject:
Reply with quote

Okay Jenifer. Assuming that the test data that you have shown us is truly representative of your actual data, try this. Hopefully this should work.

Code:
//STEP01   EXEC PGM=ICETOOL                                 
//TOOLMSG  DD SYSOUT=*                                       
//DFSMSG   DD SYSOUT=*                                       
//IN1      DD *                                             
031745453MTOU,SOP-MTOU  ,                                   
143184450DTOU,SOP-DTOU  ,                                   
143184450STOU,SOP-DTOU  ,                                   
205576411ME03,SOP-DTOU  ,                                   
963409698MTOU,CEP-MTOU  ,                                   
987654321ME03,SOP-DTOU  ,                                   
//IN2      DD *                                             
0142671371277,02210042524013,                               
0142671371277,02210115658013,                               
031745453MTOU,02110539470001,                               
031745453MTOU,05140491084001,                               
//TMP1     DD DSN=&&TEMP1,UNIT=SYSDA,SPACE=(TRK,(5,5)),     
//            DISP=(MOD,PASS)                               
//OUT      DD SYSOUT=*                                       
//TOOLIN   DD *                                             
  COPY     FROM(IN1)  TO(TMP1)   USING(CPY1)                 
  COPY     FROM(IN2)  TO(TMP1)   USING(CPY2)                 
  SPLICE   FROM(TMP1) TO(OUT)    ON(16,13,CH) -             
  WITHALL  WITH(1,14) WITH(41,1) USING(CPY3)                 
//CPY1CNTL DD *                                             
  OUTREC BUILD=(14X,15:C',',16:1,13,C',',30:15,10,40:C'11') 
//CPY2CNTL DD *                                             
  OUTREC BUILD=(1:15,14,15:C',',16:1,13,C',',30:10X,40:C'22')
//CPY3CNTL DD *                                             
  OUTFIL FNAMES=OUT,INCLUDE=(40,2,CH,EQ,C'12'),BUILD=(1,39) 
Back to top
View user's profile Send private message
Jenifer Lewis

New User


Joined: 14 Sep 2009
Posts: 28
Location: Maine

PostPosted: Thu Sep 04, 2014 7:17 pm    Post subject:
Reply with quote

Rohit Umarjikar, you are of course correct about the duplicates. I was thinking of the account numbers (second field) in File B as making each record unique, which they are, but there were definitely duplicate supplier numbers (first field) and they were what needed to be matched on File A. I apologize for any confusion my blinkered view caused.

I appreciate the link to the SPLICE command, and I will examine it to see where I was getting things wrong (quite possibly the very thing mentioned above). I have the "DFSORT tricks" manual but so many of the tricks I've tried are unsupported by my employer's current release that I have pretty much given up using it as a resource.

mistah kurtz, the tweaks you applied to the BUILD and SPLICE statements worked beautifully. Your help has been an invaluable life-saver during these days of ever-increasing work loads with no time to learn and hone new skills. I'm sorry I had such a hard time focusing on what you were trying to show me, and that I made it more difficult with my unclear problem statements.

I thank both of you from the bottom of my heart.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Thu Sep 04, 2014 9:09 pm    Post subject:
Reply with quote

You're welcome !
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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm


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