Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Adding records from two files into one file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> SYNCSORT
View previous topic :: :: View next topic  
Author Message
shiitiizz

New User


Joined: 12 Sep 2013
Posts: 19
Location: India

PostPosted: Mon Sep 19, 2016 8:41 pm    Post subject: Adding records from two files into one file
Reply with quote

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
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Mon Sep 19, 2016 8:53 pm    Post subject:
Reply with quote

1.You don't need OUTFIL, adjust the REFORMAT
2.Your keys are wrong , use the inrec keys, 13,8.
Back to top
View user's profile Send private message
shiitiizz

New User


Joined: 12 Sep 2013
Posts: 19
Location: India

PostPosted: Mon Sep 19, 2016 8:55 pm    Post subject:
Reply with quote

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
View user's profile Send private message
shiitiizz

New User


Joined: 12 Sep 2013
Posts: 19
Location: India

PostPosted: Mon Sep 19, 2016 9:05 pm    Post subject:
Reply with quote

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 icon_smile.gif
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Mon Sep 19, 2016 9:15 pm    Post subject: Reply to: Adding records from two files into one file
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> SYNCSORT All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us