Merging of two files with non-unique keys.

In this Mainframe Forum - You can post your queries on DFSORT, ICETOOL , SyncSort & JCL Utilities

Moderators: Frank Yaeger, Moderator Group

Post Reply
kunnu
Member
Posts: 18
Joined: Sat Jan 08, 2011 12:38 am
Location: Mumbai

Merging of two files with non-unique keys.

Post by kunnu » Wed Jun 15, 2011 5:42 pm

Hi,

Could you please help me in getting the desired output.

Explanation:
The records from the file-1 will be placed first and below that the file-2 will be placed. The output file should have matching records from both the files by overlaying the records from the file-2. If the file-1 has 1 record and the file-2 has 2 records of similar key then the output will be written as first record from the file-2(as overlaid) and second record again from the file-2 as it has got only 1 record in the file-1 and so on will occur for rest of the records. The output will also have the non-matching records from the file-1 i.e. the records which are present only in file-1 and not in file-2.

The length of each field DEPT, LOC and SAL can be assumed as 10 bytes. Input files can have RECFM=FB and LRECL=80,

Please refer to the below output structure for more clarity.

Thanks in advance

Code: Select all

Input:
File-1

DEPT       LOC      SAL
001        USA      1000
001        BRA      2000
002        ARG      3000
003        CAN      4000
004        CHI      5000
004        IND      6000
004        NEP      7000
004        JPN      8000 
005        AUS      9000

File-2

DEPT       LOC      SAL
001        UK       1001
002        HOL      2002
002        SWI      3003
004        GER      4004
005        FRA      5005 
005        BEL      6006
005        CRO      7007
005        ITL      8008

Output:

DEPT       LOC      SAL
001        UK       1001
001        BRA      2000
002        HOL      2002
002        SWI      3003
003        CAN      4000
004        GER      4004
004        CHI      5000
004        IND      6000
004        NEP      7000
004        JPN      8000
005        FRA      5005 
005        BEL      6006
005        CRO      7007
005        ITL      8008
006        ANT      9100







kunnu
Member
Posts: 18
Joined: Sat Jan 08, 2011 12:38 am
Location: Mumbai

Post by kunnu » Thu Jun 16, 2011 8:32 am

Actually in the above output structure I have mistakenly given the last record with DEPT as 006...that can be ignored....apologies.....could you please let me know the desired solution.

kunnu
Member
Posts: 18
Joined: Sat Jan 08, 2011 12:38 am
Location: Mumbai

Post by kunnu » Thu Jun 16, 2011 8:51 am

Ohhh GOD....guys one more mistake....i think my mind is not stable with this issue...please forgive me.....please have a look at the below output and ignore the above mentioned output.

Code: Select all


Output: 

DEPT       LOC      SAL 
001        UK       1001 
001        BRA      2000 
002        HOL      2002 
002        SWI      3003 
003        CAN      4000 
004        GER      4004 
004        IND      6000 
004        NEP      7000 
004        JPN      8000 
005        FRA      5005 
005        BEL      6006 
005        CRO      7007
005        ITL      8008 

kunnu
Member
Posts: 18
Joined: Sat Jan 08, 2011 12:38 am
Location: Mumbai

Post by kunnu » Thu Jun 16, 2011 2:33 pm

Guys please help me on this.

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Thu Jun 16, 2011 10:23 pm

Guys please help me on this
I'm in a different timezone then you are, so you have to be patient.

Your explanation of what you want is quite confusing. You need to do a better job explaining the rules for what you want to do more clearly before I can help you.

You didn't even state the "key" to match on. It appears to be DEPT? Is that correct?

It looks like you want to keep the FILE2 record and add any other excess records from FILE1. For example, if you have two FILE1 records and one FILE2 record, you want the first record from FILE2 and the second record from FILE1. But I don't know what you want to do in all situations, such as if you have multiple matches for a key in the two files. To clarify, tell me the output you would expect for this input:

Code: Select all

File1
006        MON      9001
006        TUE      9002
007        WED      9003
007        THU      9004
007        FRI      9005
008        SAT      9006
008        SUN      9007
009        MON      9008
011        TUE      9009
011        WED      9010
011        THU      9011
012        FRI      9012
012        SAT      9013

File2
006        AAA      5001
006        BBB      6002
007        CCC      5003
007        DDD      6004
008        EEE      5005
008        FFF      5006
008        GGG      5007
009        HHH      5008
010        III      5009
011        JJJ      5010
011        KKK      5011
011        LLL      5012
013        MMM      5013
013        NNN      5014
Please take your time and get the Output right the first time and explain the rules better.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

kunnu
Member
Posts: 18
Joined: Sat Jan 08, 2011 12:38 am
Location: Mumbai

Post by kunnu » Thu Jun 16, 2011 10:50 pm

Frank,

Extremely sorry for the inconvenience caused. Yes you are correct the key is DEPT to be matched on. Also the way you are assuming my output to be is exactly correct. I have provided you the expected output for the scenario you have mentioned. Please do have a look and let me know if you have any concerns. Also thanks for your effort on this.

Code: Select all

output:
006        AAA      5001
006        BBB      6002
007        CCC      5003
007        DDD      6004
007        FRI      9005
008        EEE      5005
008        FFF      5006
008        GGG      5007
009        HHH      5008
011        JJJ      5010
011        KKK      5011
011        LLL      5012
012        FRI      9012
012        SAT      9013

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Fri Jun 17, 2011 2:01 am

Here's a DFSORT JOINKEYS job that will do what you asked for:

Code: Select all

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB/80)
//IN2 DD DSN=... input file2 (FB/80)
//SORTOUT DD DSN=...  output file (FB/80)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,10,A,81,10,A),SORTED
  JOINKEYS F2=IN2,FIELDS=(1,10,A,81,10,A),SORTED
  JOIN UNPAIRED,F1,F2
  REFORMAT FIELDS=(F1:1,90,F2:1,90,?)
  OPTION COPY
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(171,10,ZD,EQ,1),PUSH=(182:181,1))
  OUTFIL OMIT=(182,1,CH,EQ,C'2'),
   IFOUTLEN=80,
   IFTHEN=(WHEN=(181,1,CH,EQ,C'1'),BUILD=(1,80)),
   IFTHEN=(WHEN=NONE,BUILD=(91,80))
/*
//JNF1CNTL DD *
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,10),PUSH=(81:SEQ=10))
/*
//JNF2CNTL DD *
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,10),PUSH=(81:SEQ=10))
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

kunnu
Member
Posts: 18
Joined: Sat Jan 08, 2011 12:38 am
Location: Mumbai

Post by kunnu » Fri Jun 17, 2011 8:50 pm

Thanks Frank for the given solution.....but unfortunately the given functions like JOINKEYS doesnt work on my machine as I dont have the latest version of Syncsort installed. Would you mind giving me the solution which would work by using simple ICETOOL. Please help me with this.

Thanks
Kunnu

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Fri Jun 17, 2011 10:04 pm

Sigh. I wish you had mentioned you use Syncsort in your first post.

The job I gave you works fine with DFSORT. It will NOT work with Syncsort no matter what version you have because Syncsort does NOT support some of the JOINKEYS functions I used that DFSORT does support.

I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others