View previous topic :: View next topic
Author
Message
shiitiizz New User Joined: 12 Sep 2013Posts: 22 Location: India
Hi,
I have a requirement to merge data from two files, into one file.
I/p file one will have data like
Code:
000000000000
000659564715
004526422562
000009425035
011657232497
002803326490
000387610660
038594371066
004386175295
002615414485
000694934971
000146457750
000659179238
000000001056
000000000000
000526384935
I/p file two will have data like
Code:
000000000000
000660036583
004580610248
000009440828
011636927832
002799495964
000388057654
038590421443
004388351088
002617585666
000691804474
000148143819
000641399729
000000001050
000000000000
000525559433
And expected o/p file is
Code:
000000000000 000000000000
000659564715 000660036583
004526422562 004580610248
000009425035 000009440828
011657232497 011636927832
002803326490 002799495964
000387610660 000388057654
038594371066 038590421443
004386175295 004388351088
002615414485 002617585666
000694934971 000691804474
000146457750 000148143819
000659179238 000641399729
000000001056 000000001050
000000000000 000000000000
000526384935 000525559433
I have used Joinkeys, below is the JCL
Code:
//STEP020 EXEC PGM=SYNCSORT
//SYSOUT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SORTJNF1 DD DSN=FILE1,DISP=SHR
//SORTJNF2 DD DSN=FILE2,DISP=SHR
//OUT DD SYSOUT=*
//JNF1CNTL DD *
INREC OVERLAY=(13:SEQNUM,8,BI)
/*
//JNF2CNTL DD *
INREC OVERLAY=(13:SEQNUM,8,BI)
/*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,12,A),NOSEQCK
JOINKEYS FILE=F2,FIELDS=(1,12,A),NOSEQCK
REFORMAT FIELDS=(F1:1,12,F2:1,12)
OPTION COPY
OUTFIL FNAMES=OUT,BUILD=(1,12,X,1,12)
/*
But the o/p is coming as
Code:
----+----1----+----2----+
000000000000 000000000000
000000000000 000000000000
000000000000 000000000000
000000000000 000000000000
Could someone please assist me to correct the sort statement, to get the desired output.
Not able to get why the data from file1 and 2 is not appearing fully in output(Only 4 lines coming as output as shown above), and why my output record shows only zeroes.
Thanks
Back to top
Rohit Umarjikar Global Moderator Joined: 21 Sep 2010Posts: 3048 Location: NYC,USA
1.You don't need OUTFIL, adjust the REFORMAT
2.Your keys are wrong , use the inrec keys, 13,8.
Back to top
shiitiizz New User Joined: 12 Sep 2013Posts: 22 Location: India
Hi,
I did a correction as below
Code:
//STEP020 EXEC PGM=SYNCSORT
//SYSOUT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SORTJNF1 DD DSN=FILE1,DISP=SHR
//SORTJNF2 DD DSN=FILE2,DISP=SHR
//OUT DD SYSOUT=*
//JNF1CNTL DD *
INREC OVERLAY=(13:SEQNUM,8,BI)
/*
//JNF2CNTL DD *
INREC OVERLAY=(13:SEQNUM,8,BI)
/*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(13,8,A),NOSEQCK
JOINKEYS FILE=F2,FIELDS=(13,8,A),NOSEQCK
REFORMAT FIELDS=(F1:1,12,F2:1,12)
OPTION COPY
OUTFIL FNAMES=OUT,BUILD=(1,12,X,1,12)
/*
but the o/p is appending data from i/p file one alone
result
Code:
000000000000 000000000000
000659564715 000659564715
004526422562 004526422562
000009425035 000009425035
011657232497 011657232497
002803326490 002803326490
000387610660 000387610660
038594371066 038594371066
004386175295 004386175295
002615414485 002615414485
000694934971 000694934971
000146457750 000146457750
000659179238 000659179238
000000001056 000000001056
000000000000 000000000000
000526384935 000526384935
I will post my solution if I manage to get, a kind request to spot out the mistake please
Back to top
shiitiizz New User Joined: 12 Sep 2013Posts: 22 Location: India
Hi Rohit,
Thanks for your quick response
I did the below change and managed to get desired result
Code:
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(13,8,A),NOSEQCK
JOINKEYS FILE=F2,FIELDS=(13,8,A),NOSEQCK
REFORMAT FIELDS=(F1:1,12,F2:1,12)
OPTION COPY
OUTFIL FNAMES=OUT,BUILD=(1,12,X,13,12)
/*
O/p as
Code:
000000000000 000000000000
000659564715 000660036583
004526422562 004580610248
000009425035 000009440828
011657232497 011636927832
002803326490 002799495964
000387610660 000388057654
038594371066 038590421443
004386175295 004388351088
002615414485 002617585666
000694934971 000691804474
000146457750 000148143819
000659179238 000641399729
000000001056 000000001050
000000000000 000000000000
000526384935 000525559433
Thanks for your time and attention
Back to top
Bill Woodger Moderator Emeritus Joined: 09 Mar 2011Posts: 7309 Location: Inside the Matrix
Code:
JOINKEYS FILE=F1,FIELDS=(13,8,A),NOSEQCK
JOINKEYS FILE=F2,FIELDS=(13,8,A),NOSEQCK
Look at the output from your two input tasks, I suspect you'll find that the data is being sorted into sequence number order (the order it is already in).
Code:
JOINKEYS FILE=F1,FIELDS=(13,8,A),SORTED,NOSEQCK
JOINKEYS FILE=F2,FIELDS=(13,8,A),SORTED,NOSEQCK
You've spotted the problem, which was that you were copying the same data (1,12) twice. So it was the same.
Since you want the data formatted like that, and since you can't include constants in a REFORMAT statement, you can do something like this:
Code:
...
* put a nice comment as to why, 1,1 at position 13 will be changed to a blank
REFORMAT FIELDS=(F1:1,12,F1:1,1,F2:1,12)
OPTION COPY
INREC OVERLAY=(13:X)
And then use SORTOUT.
I'm suggesting that because you have an eight-byte binary sequence number, which implies a considerable amount of data. If you do have a considerable amount of data, you don't want to toss it around more than necessary - so let the REFORMAT do what it can (positions of the data you need) and then just OVERLAY the blank on the unnecessary data.
Back to top
Please enable JavaScript!