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 

Merge 2 files

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


Joined: 30 Jan 2007
Posts: 20
Topics: 12
PostPosted: Mon Apr 09, 2007 9:35 am    Post subject: Merge 2 files Reply with quote
Hi,
I have 2 files. In file 1 the first 27 chars is a key. And its unique. In file 2 the first 7 chars forms the key and is unique. The first 7 chars of file 1 and file 2 match. My output is the merge of the 2 files. My problem is the first 7 chars of the file 1 is not unique. I tried using SYNCSORT. So when I try to sum only 1 entry from file1 ends up in the output file.
The file descriptions are as given below
File 1 Data:
Code:

ABCD123TYPEDESCRIPT    000001111
ABCD123TYPE2345        000001234
CDEF435TYPE3           000005454

File 1 Format: 1-7 char, 8-27 char, 28-32 zeroes, 33-37 number

File 2:
Code:

ABCD123                     123450000
CDEF435                     123460000

File 2 Format: 1-7 char, 8-27 spaces, 28-32 number, 33-37 zeroes

Output:
Code:

ABCD123TYPEDESCRIPT    123451111
ABCD123TYPE2345        123451234
CDEF435TYPE3           123465454

Output 2 Format: 1-7 char, 8-27 char, 28-32 number, 33-37 number

Can some one please help.
This is the solution I tried and didnt work when file 1 has more than 1 record with the same first 7 characters.
Code:

  SORT FIELDS=(1,7,CH,A)                             
  SUM FIELDS=(8,8,FL,16,8,FL,24,4,FL,28,9,ZD)

Thanks
Sub
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: Mon Apr 09, 2007 4:39 pm    Post subject: Reply with quote
sub,

try this job.


Code:

//STEP0100 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*                   
//SORTJNF1 DD *                         
ABCD123TYPEDESCRIPT        000001111     
ABCD123TYPE2345            000001234     
CDEF435TYPE3               000005454     
//SORTJNF2 DD *                         
ABCD123                    123450000     
CDEF435                    123460000     
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *                         
  JOINKEYS FILES=F1,FIELDS=(01,07,A)   
  JOINKEYS FILES=F2,FIELDS=(01,07,A)   
  REFORMAT FIELDS=(F1:01,27,           
                   F2:28,05,           
                   F1:33,04),FILL=X'FF'
  SORT FIELDS=COPY                     
  JOIN UNPAIRED                         
  OUTFIL OMIT=(01,01,BI,EQ,X'FF',OR,   
               28,01,BI,EQ,X'FF')       
/*


Hope this helps...

Cheers

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


Joined: 05 Sep 2003
Posts: 483
Topics: 48
PostPosted: Tue Apr 10, 2007 2:52 am    Post subject: Reply with quote
You can try this untested EZT solution.

Code:
//EZT01   EXEC PGM=EZTPA00
//SYSPRINT  DD SYSOUT=*
//SYSSNAP   DD SYSOUT=*
//SYSOUT    DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=D
//EZTVFM    DD UNIT=SYSDA,SPACE=(CYL,(5,1))
//*
//IN1       DD *
ABCD123TYPEDESCRIPT        000001111
ABCD123TYPE2345            000001234
CDEF435TYPE3               000005454
//IN2       DD *
ABCD123                    123450000
CDEF435                    123460000
//OUT1      DD DSN=A.B.C,DISP=SHR
//SYSIN     DD *
  FILE IN1
    IN1-A               001 007 A
    IN1-B               008 020 A
    IN1-C               028 005 N
    IN1-D               033 004 N

  FILE IN2
    IN2-A               001 007 A
    IN2-B               008 020 A
    IN2-C               028 005 N
    IN2-D               033 004 N

  FILE OUT1
    OUT1-A              001 007 A
    OUT1-B              008 020 A
    OUT1-C              028 005 N
    OUT1-D              033 004 N

JOB INPUT (IN1 KEY (IN1-A)  +
            IN2 KEY (IN2-A))

   IF MATCHED

   OUT1-A = IN1-A
   OUT1-B = IN1-B
   OUT1-C = IN2-C
   OUT1-D = IN1-D
   PUT OUT1
   END-IF
/*
Back to top
View user's profile Send private message Send e-mail
cyber_snake
Beginner


Joined: 24 Dec 2006
Posts: 10
Topics: 1
PostPosted: Sun Apr 15, 2007 8:50 pm    Post subject: Reply with quote
Hi there, i have question related to this one, what if i dont have a key... sample below..

File 1;
Code:

  field1  field2 
  1234    5678
  1234    5678
  1234    5678

File 2:
Code:

  field1  field2  field3  field4
  0001                      data1
  0002                      data2
  0003                      data3



I want to move field1 and field2 of File1 to field2 and field3 of File2

Thanks....
Back to top
View user's profile Send private message AIM Address
cyber_snake
Beginner


Joined: 24 Dec 2006
Posts: 10
Topics: 1
PostPosted: Mon Apr 16, 2007 1:20 am    Post subject: Reply with quote
any updates regarding this scenario???
Back to top
View user's profile Send private message AIM Address
Nic Clouston
Advanced


Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home
PostPosted: Mon Apr 16, 2007 2:56 am    Post subject: Reply with quote
cyber_snake wrote:
any updates regarding this scenario???
obviously not. This could be due to 2 things:

1 - this is a separate topic
2 - you don't give enough info - e.g. the fields of which record in file 1 are moved to which record of file 2. What if the number of records in file 1 does not match the number of records in file 2 etc etc
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.
Back to top
View user's profile Send private message
cyber_snake
Beginner


Joined: 24 Dec 2006
Posts: 10
Topics: 1
PostPosted: Mon Apr 16, 2007 3:20 am    Post subject: Reply with quote
Thanks Nic for the reply, The movable data for each field is the same rec length, meaning... field1 of file1 is the same with field2 of file2, and so on... The total rec of each file is also the same... I only want to know if ICETOOL can handle this scenario.... I already have temp solution for this by creating seqnum for each file and in the last step i use easytrieve for moving the fields with Key criteria...
Back to top
View user's profile Send private message AIM Address
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Mon Apr 16, 2007 5:29 am    Post subject: Reply with quote
cyber_snake,

what is the lrecl and recfm of the 2 files?
what is format & length of the fields to be merged

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Mon Apr 16, 2007 10:05 am    Post subject: Reply with quote
Quote:
I only want to know if ICETOOL can handle this scenario


The original poster asked for a Syncsort solution. Are you looking for a Syncsort solution or a DFSORT solution?

You really should have started a new topic for this question.

Have a look at the method discussed in the "Join fields from two files record-by-record" Smart DFSORT Trick at:

http://www.ibm.com/servers/storage/support/software/sort/mvs/tricks/
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
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