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
 

 

Merging a Flatfile and a VSAM to a new Flatfile

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
cyrus.e.cabrera

New User


Joined: 25 Jul 2011
Posts: 14
Location: Philippines

PostPosted: Fri Sep 16, 2011 8:56 am    Post subject: Merging a Flatfile and a VSAM to a new Flatfile
Reply with quote

I have trouble finding ideas on how to merge two files only related by their "key" and an additional problem has been encountered because I will attempt to merge a FLATFILE and a VSAM.

Question #1: Is it possible to merge two different file formats using ICETOOL?

Question #2: Supposedly I have sample data:

FLATFILE(file1)
Code:
code-key     balance

00000001    2000.00
00000002    2345.00
00000003    1250.00
00000004    2300.00

and another file,

VSAM(file2)
Code:
code-key    username

0000002     johndoe
0000004     carltonx

What I want to accomplish is, merge this two files(ideally with ICETOOL, or anything that is feasible), so that it will look like this:

FILE3:
Code:
code-key   balance   username
0000001    2000.00   
0000002    2345.00   johndoe
0000003    1250.00   
0000004    2300.00   carltonx



Just in case it is not possible to merge them(because of file type conflict), kindly assume that they are both FLATFILE(s).

It's been bothering me for two days. I really need a hand on this. I would be greatly appreciated. Advance gratitude. icon_smile.gif
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Sep 16, 2011 10:41 am    Post subject:
Reply with quote

look at JOINKEYS
Back to top
View user's profile Send private message
ramsankar rajkumar

New User


Joined: 07 Jun 2011
Posts: 18
Location: Chennai, India

PostPosted: Fri Sep 16, 2011 11:36 am    Post subject:
Reply with quote

Copy VSAM file data to FLAT file and then merge.
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1703
Location: Australia

PostPosted: Fri Sep 16, 2011 11:41 am    Post subject:
Reply with quote

Hi,

I noticed the keys are of different length ?

Is this correct ?

Gerry
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Sep 16, 2011 12:52 pm    Post subject:
Reply with quote

Quote:
Copy VSAM file data to FLAT file and then merge.


i do not believe you have to do that.

in addition, if the keys are numeric, i don't believe there is a problem on the different lengths.
but in the event there is, using ICETOOL, you can reformat one of the files and then do the JOINKEYS.

cyrus.e.cabrera,

based on the very simple data that you have provided,
you can use this as a starting point:
Code:

//S1    EXEC  PGM=SORT
//SYSOUT DD SYSOUT=*
//MASTER DD DSN=...  Master input file (FB/280)
//KEY DD DSN=...     Key input file (FB/80)
//SORTOUT DD DSN=...  output file (FB/280)
//SYSIN DD *
  JOINKEYS F1=MASTER,FIELDS=(1,17,A)
  JOINKEYS F2=KEY,FIELDS=(1,17,A)
  JOIN                 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
  REFORMAT FIELDS=(F1:1,280)  <<<<<<<<<<<<<<<<<<<<<<
  OPTION COPY
/*


the above is copied from an example in the forum found by search on JOINKEYS.

check the manual for the JOIN options so that you get matched and unmatched

and based on your input file defintions, you will have to expand the REFORMAT options.

you should refer to Chapter 8. Joining Records in the DFSORT: Getting Started PDF, which is found here
Back to top
View user's profile Send private message
cyrus.e.cabrera

New User


Joined: 25 Jul 2011
Posts: 14
Location: Philippines

PostPosted: Fri Sep 16, 2011 12:55 pm    Post subject: Reply to: Merging a Flatfile and a VSAM to a new Flatfile
Reply with quote

Found the solution. YES. The keys are incorrect on file2. it should be the on the same length as the file1's key. I used JOINKEY as a method under SORT Utility. By adding (JOIN UNPAIRED, F1, F2) after the joining, I can retain all the records even if it did not match the key from file2. Thanks for the help guys. If anyone needs the code for reference, here it is:
Code:

//JMERGE EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=xxxx.TESTFILE,DISP=SHR
//IN2 DD DSN=xxxx.TESTFIL2,DISP=SHR
//SORTOUT DD DSN=xxxx.TESTFIL3,DISP=(,CATLG,DELETE),
//           UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,8,A)
  JOINKEYS F2=IN2,FIELDS=(1,8,A)
  JOIN UNPAIRED,F1,F2
  REFORMAT FIELDS=(F1:1,8,F1:9,6,F2:9,15)
  OPTION COPY
/*
//*
//JSORT1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=xxxx.TESTFIL3,DISP=SHR
//SORTOUT DD DSN=xxxx.TESTFIL4,DISP=(,CATLG,DELETE),
//           UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE)
//SYSIN DD *
  SORT FIELDS=(15,15,CH,A)
/*


By the way, one more question. icon_smile.gif

How can I redirect my output to the same input I provided?
Like for example, I used xxxx.file1 as an input to sorting and I want to redirect the output to xxxx.file1 itself.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7235

PostPosted: Fri Sep 16, 2011 1:00 pm    Post subject: Reply to: Merging a Flatfile and a VSAM to a new Flatfile
Reply with quote

Quote:
How can I redirect my output to the same input I provided?


Perhaps better to think of this in a seperate step. What if your first step were to fall over after starting to write to your input file...
Back to top
View user's profile Send private message
cyrus.e.cabrera

New User


Joined: 25 Jul 2011
Posts: 14
Location: Philippines

PostPosted: Fri Sep 16, 2011 1:01 pm    Post subject: Reply to: Merging a Flatfile and a VSAM to a new Flatfile
Reply with quote

dbzTHEdinosauer,

I just found the solution. Like you have said, I read the manual on SORT utilities provided by the IBM website.

I tried to simplify the data to avoid complication and confusion for other reader who has the same problem as mine. icon_biggrin.gif

Thank you for the effort you have provided for me to be able to start out.
Back to top
View user's profile Send private message
cyrus.e.cabrera

New User


Joined: 25 Jul 2011
Posts: 14
Location: Philippines

PostPosted: Fri Sep 16, 2011 1:13 pm    Post subject: Re: Reply to: Merging a Flatfile and a VSAM to a new Flatfil
Reply with quote

Bill Woodger wrote:
Quote:
How can I redirect my output to the same input I provided?


Perhaps better to think of this in a seperate step. What if your first step were to fall over after starting to write to your input file...


I agree Bill. I just realized it would be disastrous because you read your file then you write on it. It's erroneous logically I think. icon_biggrin.gif
Thank you for providing your comment.
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1703
Location: Australia

PostPosted: Fri Sep 16, 2011 2:59 pm    Post subject:
Reply with quote

Hi Dick,

I believe the length of the keys does matter

Code:

FIELDS   
                 .-,-----.       
                 V       |       
>>-FIELDS=----(----p,m,s-+--)----------------------------------><
 
 

Each pair of keys for the F1 and F2 files must match with respect to length and order, but can start in different positions. For example, if the first key for the F1 file is 5 bytes ascending and the second key for the F1 file is 3 bytes descending, the first key for the F2 file must be 5 bytes ascending and the second key for the F2 file must be 3 bytes descending.

If a variable-length record is too short to contain a key you specify, the short key value will be compared using binary zeros for the missing bytes.

p
specifies the starting position of the key. The first data byte of a fixed-length record is in position 1. The first data byte of a variable-length record is in position 5 after the 4-byte RDW. p can be 1 to 32752 but all fields must be completely contained within the first 32752 bytes of the record.

m
specifies the length of the key. The total length of all keys must not exceed 4080 bytes. All fields must be completely contained within the first 32752 bytes of the record.
The length for each pair of F1 and F2 keys must match.

s
specifies the order of the key. Use A for ascending or D for descending.




Gerry
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Sep 16, 2011 3:12 pm    Post subject:
Reply with quote

dbz wrote:
in addition, if the keys are numeric, i don't believe there is a problem on the different lengths.
but in the event there is, using ICETOOL, you can reformat one of the files and then do the JOINKEYS


no need to believe, you know,
i did not bother to look but provided a solution in the event that that a secondary solution was necessary.
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts BWO option in VSAM blayek CICS 3 Sat Nov 05, 2016 10:47 am
No new posts VSAM define for large file jerryte JCL & VSAM 9 Wed Oct 05, 2016 1:51 am
No new posts CICS VSAM RLS READ akodakka CICS 3 Tue Oct 04, 2016 3:09 pm
No new posts 2 vsam file compare and update a fiel... ABINAYATHULASI DFSORT/ICETOOL 6 Sun May 29, 2016 11:15 am
No new posts trying to define a VSAM Cluster in JCL t5590ag JCL & VSAM 10 Thu May 19, 2016 5:52 am


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