MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Combine data from 2 files without a key in Easytrieve

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
jwdaughtrey
Beginner


Joined: 17 Nov 2014
Posts: 4
Topics: 1
PostPosted: Tue Nov 18, 2014 7:56 am    Post subject: Combine data from 2 files without a key in Easytrieve Reply with quote
I have two files, no fields in common. I need to combine information from File A with File B. For every record in File A, I need to add information from every record in File B.
How can this be accomplished in Easytrieve?
Back to top
View user's profile Send private message
William Collins
Supermod


Joined: 03 Jun 2012
Posts: 437
Topics: 0
PostPosted: Tue Nov 18, 2014 9:10 am    Post subject: Reply with quote
It's easy with JOINKEYS in SORT. Easytrieve's matching doesn't work the same way. To do it in Easytrieve, you'd have to store all the records in a table.
Back to top
View user's profile Send private message
jwdaughtrey
Beginner


Joined: 17 Nov 2014
Posts: 4
Topics: 1
PostPosted: Tue Nov 18, 2014 10:23 am    Post subject: Reply with quote
I'm not understanding. How can I join them when there are no fields in common to join on?
Back to top
View user's profile Send private message
William Collins
Supermod


Joined: 03 Jun 2012
Posts: 437
Topics: 0
PostPosted: Tue Nov 18, 2014 10:54 am    Post subject: Reply with quote
You make the criteria. You add (or use if existing) a byte, temporarily, to each record on both files. That byte only has one value. You specify that byte for the JOINKEYS. Eh, voila! Full Cartesian Join.

However, you want information from every record on file two updates on every record on file one. Didn't read that fully before. What types of volumes do you have for each file? Fixed, or variable records? I think if you can use ICETOOL's RESIZE to get file two into a single (or not many) conglomerated record it should ease the coding (in SORT or in Easytrieve Plus).

Can you show some sample representative data and expected output?
Back to top
View user's profile Send private message
jwdaughtrey
Beginner


Joined: 17 Nov 2014
Posts: 4
Topics: 1
PostPosted: Tue Nov 18, 2014 11:02 am    Post subject: Reply with quote
can't show too much representative data, proprietary information and all. But, can can mock up a similar situation:
File A Account Shares Last Trade File B LastName FirstName

What I want to see (and yes, this is just to illustrate what I have to do, the information has nothing to do with accounts and names)
FileO Account LastName FirstName

File A can have thousands of records, File B can have anywhere from 2 to 1000 records.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Tue Nov 18, 2014 12:01 pm    Post subject: Reply with quote
jwdaughtrey,

As William pointed out with easytrieve you need to read the file entirely as table and then perform the match for every record.

However it is quite simple with DFSORT Joinkeys. we add a psuedo key at the end of every record in both files. Remember that this joining of every record with every other record in file 2 will result in a Cartesian join. Here is a sample.

Code:

//STEP0100 EXEC PGM=SORT                             
//SYSOUT   DD SYSOUT=*                               
//INA      DD *                                     
AAAAA                                               
BBBBB                                               
CCCCC                                               
//INB      DD *                                     
XXXXX                                               
YYYYY                                               
ZZZZZ                                               
//SORTOUT  DD SYSOUT=*                               
//SYSIN    DD *                                     
  OPTION COPY                                       
  JOINKEYS F1=INA,FIELDS=(81,1,A),SORTED,NOSEQCK     
  JOINKEYS F2=INB,FIELDS=(81,1,A),SORTED,NOSEQCK     
  REFORMAT FIELDS=(F1:1,30,F2:1,30)                 
//*                                                 
//JNF1CNTL DD *                                     
  INREC OVERLAY=(81:X)                               
//*                                                 
//JNF2CNTL DD *                                     
  INREC OVERLAY=(81:X)                               
//*                                                 


The output from this job is

Code:

AAAAA                         XXXXX       
AAAAA                         YYYYY       
AAAAA                         ZZZZZ       
BBBBB                         XXXXX       
BBBBB                         YYYYY       
BBBBB                         ZZZZZ       
CCCCC                         XXXXX       
CCCCC                         YYYYY       
CCCCC                         ZZZZZ       

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54
PostPosted: Tue Nov 18, 2014 12:11 pm    Post subject: Reply with quote
If you need One to every record then

Code:

//S1   EXEC  PGM=SORT                               
//SYSOUT DD SYSOUT=*                               
//INA DD *                                         
12                                                 
34                                                 
56                                                 
//INB DD *                                         
12                                                 
45                                                 
67                                                 
//SORTOUT  DD SYSOUT=*                             
//SYSIN    DD *                                     
  OPTION COPY                                       
  JOINKEYS F1=INA,FIELDS=(3,1,A),SORTED,NOSEQCK     
  JOINKEYS F2=INB,FIELDS=(3,1,A),SORTED,NOSEQCK     
  REFORMAT FIELDS=(F1:1,2,F2:1,2)                   
//*                                                 
//JNF1CNTL DD *                                     
  INREC OVERLAY=(3:C' ') 
//*                       
//JNF2CNTL DD *           
  INREC OVERLAY=(3:C' ') 
//*                       


If you need One to one record.

Code:

//S1   EXEC  PGM=SORT                                 
//SYSOUT DD SYSOUT=*                                 
//INA DD *                                           
12                                                   
34                                                   
56                                                   
//INB DD *                                           
12                                                   
45                                                   
67                                                   
//SORTOUT  DD SYSOUT=*                               
//SYSIN    DD *                                       
  OPTION COPY                                         
  JOINKEYS F1=INA,FIELDS=(3,8,A),SORTED,NOSEQCK       
  JOINKEYS F2=INB,FIELDS=(3,8,A),SORTED,NOSEQCK       
  REFORMAT FIELDS=(F1:1,2,F2:1,2)                     
//*                                                   
//JNF1CNTL DD *                                       
  INREC OVERLAY=(3:SEQNUM,8,ZD)   
//*                               
//JNF2CNTL DD *                   
  INREC OVERLAY=(3:SEQNUM,8,ZD)   
//*                               


Thanks
Magesh
Back to top
View user's profile Send private message
William Collins
Supermod


Joined: 03 Jun 2012
Posts: 437
Topics: 0
PostPosted: Tue Nov 18, 2014 12:31 pm    Post subject: Reply with quote
Whoops. Wrong topic...

Whoops whoops, wasn't wrong topic, but late to the party.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Tue Nov 18, 2014 12:35 pm    Post subject: Reply with quote
jwdaughtrey,

There are a couple of DFSORT smart tricks which shows the usage of Joinkeys

    Join fields from two files on a key
    Join fields from two files record-by-record
    Cartesian join
    Create files with matching and non-matching records


Check this link

http://www-01.ibm.com/support/docview.wss?rs=114&uid=isg3T7000094

Here is a detailed explanation of Joinkeys.

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA60/4.0?
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54
PostPosted: Tue Nov 18, 2014 1:38 pm    Post subject: Reply with quote
Sorry william, I didnt notice kolusu's response..

Agree I am late to the party

Regards
Magesh
Back to top
View user's profile Send private message
jwdaughtrey
Beginner


Joined: 17 Nov 2014
Posts: 4
Topics: 1
PostPosted: Tue Nov 18, 2014 2:49 pm    Post subject: Reply with quote
Thank you for the responses, they have been helpful and illuminating!
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group