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
 

 

Combine two files with a key (SPLICE?)

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Combine two files with a key (SPLICE?)
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    Post subject:
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    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Sep 23, 2008 10:09 pm    Post subject:
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    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Sep 23, 2008 11:36 pm    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Sep 24, 2008 12:14 am    Post subject:
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    Post subject:
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: 2276
Location: @my desk

PostPosted: Wed Sep 24, 2008 8:44 pm    Post subject:
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    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 Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Dynamic split of files under groups sril.krishy DFSORT/ICETOOL 4 Mon Apr 17, 2017 1:09 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm
No new posts Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm


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