VSAM - Flat File comparison



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

VSAM - Flat File comparison

Postby sudhirbabu149 » Sat Oct 22, 2011 8:38 pm

Hi,

I need to compare one VSAM file with a flat file and write the matching and unmatched records in to 2 different files.
File1:
I have a VSAM File of LRECL=2014, which has Policy Number from (1,4) - BI (7 character - ZD)
File2:
A flat file which will just have the Policy Numbers (7 characters - ZD)

Can some one please suggest me if this is possible in a single JCL, using JOINKEYS.
If so, please let me know the logic.

The output should be two VSAM files, with the matching and unmatched records from File1

Thanks.
sudhirbabu149
 
Posts: 7
Joined: Sat Oct 22, 2011 6:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: VSAM - Flat File comparison

Postby dick scherrer » Mon Oct 24, 2011 10:08 am

Hello,

Suggest you try and see what happens. Learning by doing is a very good way to learn.

You might also refer to the DFSORT manual - Frank has posted the link to the manuals in several topics.

If something that is confusing happens, post what you tried and what happened.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: VSAM - Flat File comparison

Postby sudhirbabu149 » Mon Oct 24, 2011 1:36 pm

Hi,

I agree.. ;)
But I don't have access to MF as I am on vacation till next Monday. Neither I have work from home option :(
Will definitely try this out once I get access to work.

Thanks again for your inputs D.Sc
sudhirbabu149
 
Posts: 7
Joined: Sat Oct 22, 2011 6:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: VSAM - Flat File comparison

Postby dick scherrer » Mon Oct 24, 2011 10:11 pm

Hello,

Enjoy your vacation!

We'll be here when you get back :)

d
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: VSAM - Flat File comparison

Postby Frank Yaeger » Mon Oct 24, 2011 11:22 pm

sudhirbabu149,

Using VSAM files with DFSORT's JOINKEYS function is not much different from using non-VSAM files. The only catch is you have to decide whether to process the VSAM input files as fixed length or variable length, use TYPE=F or TYPE=V accordingly and set the positions correctly (first data byte starts in position 1 for TYPE=F whereas first data byte starts in position 5 for TYPE=V).

If you need more specific help, show an example of the records in each input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example.
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
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: VSAM - Flat File comparison

Postby sudhirbabu149 » Thu Oct 27, 2011 11:01 am

Hi Frank,

Thanks for your reply.
Here I am posting the details of the files.

Input file1 is a VSAM of RECFM:VB and LRECL:2014
Input File2 is a sequential file of RECFM:FB and LRECL:80

Comparison needed on Policy Numbers which is present in both the files.
In File1(VSAM) its from (1,4) - BI and in File2 its in (1,7) - ZD

Output should be two VSAM Files (VB) with same properties as of the Input VSAM file.

Below is an example,

INPUT VSAM FILE:

BIN1 JACOB S PAUL 89-3-4343
BIN2 STEVEN FINN 34905-H13-23
BIN3 SACHIN TENDULKAR 98-234-11
BIN3 RAHUL DRAVID 8903-43-2
BIN4 RICKY PONTING 59-34-J5
BIN4 RICKY PONTING 59-34-J5



INPUT POLICY FILE:

2345456 (BIN1)
2544688 (BIN4)
2794567
2789769
2676799


OUTPUT FILE 1 (MATCHED RECORDS):

BIN1 JACOB S PAUL 89-3-4343
BIN4 RICKY PONTING 59-34-J5
BIN4 RICKY PONTING 59-34-J5


OUTPUT FILE 2 (UN-MATCHED RECORDS):

BIN2 STEVEN FINN 34905-H13-23
BIN3 SACHIN TENDULKAR 98-234-11
BIN3 RAHUL DRAVID 8903-43-2



Thanks
sudhirbabu149
 
Posts: 7
Joined: Sat Oct 22, 2011 6:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: VSAM - Flat File comparison

Postby sudhirbabu149 » Thu Oct 27, 2011 11:22 am

Hi Frank,

I was planning to convert the Binary Policy number to ZD in one step and appending the converted ZD number at last, to be compared with the QSAM's Policy number (1,7)

//BINARY  DD DSN=VSAM.INPUT1, DISP=SHR
//ZONEDEC  DD DSN=BI-ZD.VSAM.INPUT,
              DISP=(NEW,CATLG,DELETE),
              DCB=(RECFM=VB,LRECL=2021)
//SYSIN    DD *                                   
  OPTION COPY                                     
  OUTREC FIELDS=(1:1,2014,
                     2015:1,4,BI,TO=ZD,LENGTH=7)
/*     


This is for FB Files though. Frank,Is this possible to do on VSAM files?

If so output file will be of LRECL - 2014+7(converted BI Policy Number) = 2021, which will be used in the 2nd SORT Step where we are using JOINKEYS.

Just an attempt to make this code more readable for the users. ;)
Please let me know if any more details needed.

Thanks.
sudhirbabu149
 
Posts: 7
Joined: Sat Oct 22, 2011 6:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: VSAM - Flat File comparison

Postby BillyBoyo » Thu Oct 27, 2011 11:53 am

Do you need to do a conversion of the number? If you do need to/want to, presumably it wouldn't have to be a seperate step?

If you do a seperate step to an intermediate file, that file needn't be VSAM, so you next step ends up with flat files in and VSAM out anyway?

I doubt that there is much in DFSort which "works on a flat file, doesn't work on vsam". Try it out, and let us know the details if you get a problem.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: VSAM - Flat File comparison

Postby Frank Yaeger » Fri Oct 28, 2011 12:01 am

You can use a DFSORT job like the following to do what you asked for:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=...  input file1 (VSAM/V)
//IN2 DD DSN=...  input file2 (FB/80)
//MATCH DD DSN=...  output file1 (VSAM/V)
//UNMATCH DD DSN=...  output file2 (VSAM/V)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(5,4,A),TYPE=V
  JOINKEYS F2=IN2,FIELDS=(1,4,A)
  JOIN UNPAIRED,F1
  REFORMAT FIELDS=(F1:1,4,?,F1:5)
  OPTION COPY
  OUTFIL FNAMES=MATCH,INCLUDE=(5,1,CH,EQ,C'B'),
    BUILD=(1,4,6)
  OUTFIL FNAMES=UNMATCH,INCLUDE=(5,1,CH,EQ,C'1'),
    BUILD=(1,4,6)
/*
//JNF2CNTL DD *
  INREC BUILD=(1,7,ZD,TO=BI,LENGTH=4)
/*


Note that I converted the ZD key in file2 to a BI key for the match. This makes more sense than converting the BI key in file1 to a ZD key.
If you were going to convert the BI key in file1 to a ZD key, you would want to put the converted value between the RDW and the first data byte. Putting it at the end of the VB record would unnecessarily increase the length of EVERY VB record to the maximum LRECL.
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
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: VSAM - Flat File comparison

Postby sudhirbabu149 » Fri Oct 28, 2011 12:05 pm

Thanks a lot Frank.

Didn't really thought of converting BI to ZD, which had made the logic look relatively simple. Avoiding all the confusions as you said. 8-)
Thanks for that Frank.

Will try this out and let you know the result shortly.



Thanks.
sudhirbabu149
 
Posts: 7
Joined: Sat Oct 22, 2011 6:59 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post