Adding records from two files into one file

IBM Mainframe Forums -> SYNCSORT

Quick References

View previous topic :: View next topic

Author

Message

shiitiizz

New User

Joined: 12 Sep 2013
Posts: 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

Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA

Posted: Mon Sep 19, 2016 8:53 pm

1.You don't need OUTFIL, adjust the REFORMAT 2.Your keys are wrong , use the inrec keys, 13,8.

shiitiizz

New User

Joined: 12 Sep 2013
Posts: 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

shiitiizz

New User

Joined: 12 Sep 2013
Posts: 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:

Thanks for your time and attention

Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 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.

View previous topic :: :: View next topic

View Bookmarks
All times are GMT + 6 Hours

Forum Index -> SYNCSORT

Topic		Forum	Replies
Similar Topics
	Compare 2 files and retrive records f...	DFSORT/ICETOOL	3
	Compare 2 files(F1 & F2) and writ...	JCL & VSAM	8
	FTP VB File from Mainframe retaining ...	JCL & VSAM	8
	Extract the file name from another fi...	DFSORT/ICETOOL	6
	How to split large record length file...	DFSORT/ICETOOL	10

Search our Forums:

IBMMainframes.com is not an official and/or affiliated with IBM^® in anyway
Board Rules \| FAQ \| Downloads \| Wiki \| SiteMap \| Contact Us