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

Combine two files with a key (SPLICE?)
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
rafaelantunes

New User


Joined: 10 May 2007
Posts: 32
Location: Portugal
PostPosted: Tue Sep 23, 2008 5:31 pm
 Reply with quote
I want to combine two files and produce one with the full information.

FILE1:
Code:
0001 AAAA 0001
0002 AAAA 0001
0001 BBBB 0001
0001 BBBB 0002
0002 BBBB 0001
0002 BBBB 0002
0003 CCCC 0003


FILE2:
Code:
9876 AAAA X
1234 CCCC X

Note: This file has no duplicates.

OUTPUT:
Code:
0001 AAAA 0001 X
0002 AAAA 0001 X
0001 BBBB 0001
0001 BBBB 0002
0002 BBBB 0001
0002 BBBB 0002
0003 CCCC 0003 X


The order of the records of the output doesn't matter.

Best regards
Back to top
View user's profile Send private message
hchinnam

New User


Joined: 18 Oct 2006
Posts: 73
PostPosted: Tue Sep 23, 2008 6:28 pm
 Reply with quote
Following JCL gave me the results you have asked.

Code:


//S0001    EXEC PGM=ICETOOL                                     
//DFSMSG   DD SYSOUT=*                                           
//SYSPRINT DD SYSOUT=*                                           
//SYSOUT   DD SYSOUT=*                                           
//TOOLMSG  DD SYSOUT=*                                           
//STATA    DD DSN=&&TEMP,                                       
//            SPACE=(CYL,(10,10)),                               
//            DISP=(MOD,CATLG),DCB=(LRECL=80,BLKSIZE=8000)       
//STATB    DD DSN=&&TEMP2,                                       
//            SPACE=(CYL,(10,10)),                               
//            DISP=(MOD,CATLG),DCB=(LRECL=80,BLKSIZE=8000)       
//DDIN1    DD *                                                 
0001 AAAA 0001                                                   
0002 AAAA 0001                                                   
0001 BBBB 0001                                                   
0001 BBBB 0002                                                   
0002 BBBB 0001                                                   
0002 BBBB 0002                                                   
0003 CCCC 0003                                                   
//DDIN2    DD *                                                 
9876 AAAA X                                                     
1234 CCCC X                                                     
//DDOUT    DD SYSOUT=*                                           
//OUT      DD SYSOUT=*                                           
//TOOLIN   DD *                                             
  COPY FROM(DDIN2) TO(STATA) USING(CTL2)                     
  COPY FROM(DDIN1) TO(STATA) USING(CTL1)                     
  SPLICE FROM(STATA) TO(STATB) ON(6,4,CH) WITH(1,15) -       
  KEEPNODUPS                                                 
  COPY FROM(DDIN1) TO(STATB)                                 
  SPLICE FROM(STATB) TO(OUT) ON(6,4,CH) WITH(1,15)   -       
  WITHALL                                                   
//CTL1CNTL DD *                                             
  SORT FIELDS=(6,4,CH,A)                                     
  SUM FIELDS=NONE                                           
//CTL2CNTL DD *                                             
  INREC BUILD=(1:1,10,16:11,1)                               



But this way we have to read the whole data thrice. May be some one can show you a way to do this with less I/O, But till then you can use this.
Back to top
View user's profile Send private message
rafaelantunes

New User


Joined: 10 May 2007
Posts: 32
Location: Portugal
PostPosted: Tue Sep 23, 2008 6:37 pm
 Reply with quote
Thanks hchinnam.

I'll try it.
Although I must say that "time" is too damn important. I really need a "fast" solution on this one.

Many thanks.

Hope to hear more solutions.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Tue Sep 23, 2008 10:09 pm
 Reply with quote
Here's a more efficient way to do what you asked for:

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD *
0001 AAAA 0001
0002 AAAA 0001
0001 BBBB 0001
0001 BBBB 0002
0002 BBBB 0001
0002 BBBB 0002
0003 CCCC 0003
/*
//IN2 DD *
9876 AAAA X
1234 CCCC X
2222 DDDD X
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(6,4,CH) KEEPNODUPS KEEPBASE -
  WITHALL WITH(1,14) WITH(81,1) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY(16:11,1,81:C'BB')
/*
//CTL2CNTL DD *
  INREC OVERLAY(81:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,OMIT=(81,2,CH,EQ,C'BB'),
    BUILD=(1,80)
/*
Back to top
View user's profile Send private message
rafaelantunes

New User


Joined: 10 May 2007
Posts: 32
Location: Portugal
PostPosted: Tue Sep 23, 2008 11:08 pm
 Reply with quote
arcvns wrote:
Hello,

You can give this a try.
Code:
//STEP1    EXEC PGM=ICETOOL                   
//DFSMSG   DD SYSOUT=*                         
//TOOLMSG  DD SYSOUT=*                         
//IN1      DD *                               
9876 AAAA X                                   
1234 CCCC X                                   
//IN2      DD *                               
0001 AAAA 0001                                 
0002 AAAA 0001                                 
0001 BBBB 0001                                 
0001 BBBB 0002                                 
0002 BBBB 0001                                 
0002 BBBB 0002                                 
0003 CCCC 0003                                 
//T1       DD DSN=&&T1,DISP=(MOD,PASS)         
//OUT      DD SYSOUT=*                         
//TOOLIN   DD *                               
  COPY FROM(IN1)  TO(T1) USING(CTL1)           
  COPY FROM(IN2)  TO(T1)                       
  SPLICE FROM(T1) TO(OUT) ON(6,4,CH) WITH(1,4) WITH(11,4) -
         WITHALL KEEPBASE KEEPNODUPS USING(CTL2)           
//CTL1CNTL DD *                                           
  INREC BUILD=(6:6,4,16:11,1)                             
//CTL2CNTL DD *                                           
  OUTFIL OMIT=(1,1,CH,EQ,C' ')                             
//*             

OUT
Code:
0001 AAAA 0001 X
0002 AAAA 0001 X
0001 BBBB 0001 
0001 BBBB 0002 
0002 BBBB 0001 
0002 BBBB 0002 
0003 CCCC 0003 X


(I hope "arcvns" don't be upset to put his code in here)

So I have 3 possible solutions.
Since efficiency is top priority could you give me some feedback on what solution is best?

I'll be using it with huge files (millions of records).

I think the "arcvns" solution is cleaner. Am I right?

Big thanks to all your help.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Tue Sep 23, 2008 11:36 pm
 Reply with quote
Where did you find that job from arcvns? It's not posted in this thread.

Arcvns's use of the first byte of the file with the 'X' as the id is a good idea for this particular situation and might be a bit more efficient (or it might not matter - hard to say). The only way to find out for sure is to run both jobs with your own data. However, note that arcvns assumed your records are 16 bytes long whereas I assumed your records are 80 bytes long (you never said), so be sure to adjust the jobs for the correct LRECL before running/comparing them.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Wed Sep 24, 2008 12:14 am
 Reply with quote
FWIW, if there are any records that have a blank in position 1, they will be dropped by Arcvns's job, but not by mine. Based on the example given, that's probably not a concern here, but I thought I should mention it just in case.
Back to top
View user's profile Send private message
rafaelantunes

New User


Joined: 10 May 2007
Posts: 32
Location: Portugal
PostPosted: Wed Sep 24, 2008 2:27 pm
 Reply with quote
Quote:
Where did you find that job from arcvns? It's not posted in this thread.


Arcvns send me his solution by PM.

I'll compare both solutions.

Once again many thanks.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Wed Sep 24, 2008 8:44 pm
 Reply with quote
Quote:
It's not posted in this thread
Frank,

As pointed out by you, it seemed improper to me to post my JCL here as I dont have DFSORT.
Quote:
I really need a "fast" solution on this one
But this made me 'PM' ing it. Sorry for the confusion icon_sad.gif
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 Merge two VSAM KSDS files into third ... JCL & VSAM 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