Using DFSORT to match and process two input files



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

Using DFSORT to match and process two input files

Postby webber » Thu Jul 08, 2010 2:45 pm

Hi all,
I´m having trouble achieving the complete output that i need.

The thing is, i need to receive two files, F1 and F2, and produce 3 output files.
The first file should contain the records that only are present in F1.
The second file should contain the records that only are present in F1.
The third file should contain the records that exist in both F1 and F2 but are differ.
The key is 01, 02, 03, ...

For example, assuming that F1 looks like this:

01 John 20
02 Rick 45
03 Anna 30

And F2 looks like this:

01 John 20
02 Rick 50
03 Frank 20

The desired output would be:

Output File 1 (only present in F1):
03 Anna 30

Output File 2 (only present in F2):
03 Frank 20

Output File 3 (updated/modified from F1 to F2 - should display what is present on F2):
02 Rick 50

Getting the first two files was simple using splice and searching here in the forum, but achieving the desired result for Output File 3 is more complicated.

Can anyone help?

Thank you,
Regards
webber
 
Posts: 8
Joined: Thu Jul 08, 2010 2:29 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Using DFSORT to match and process two input files

Postby webber » Thu Jul 08, 2010 2:48 pm

Correction:

The second file should contain the records that only are present in F2.
webber
 
Posts: 8
Joined: Thu Jul 08, 2010 2:29 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Using DFSORT to match and process two input files

Postby Frank Yaeger » Thu Jul 08, 2010 10:11 pm

It's not clear what rules or fields you want to use to get your output. For the 01 record, all three fields are the same. For the 02 record, only the first two fields are the same. For the 03 record, only the first field is the same. It's difficult to derive "rules" from that small set of examples, especially when you say "The key is 01, 02, 03, ...", but it's obvious that the other fields matter in some way as well (but how)?

Please show a better 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: Using DFSORT to match and process two input files

Postby webber » Thu Jul 08, 2010 11:13 pm

I´ll try to explain better.

The structure of the files should be seen as folow:

UserID Name Time

The two files show average login times for two different weeks.
What i want to do is put in three different files the users that logon last week but didn´t logon this week, the users that didn´t logon last week but logon this week, and the users for which any other field (except the UserID) changed. Basically, i want to put in the third file the users that had changed their name or their logon time.

As such, the output should reflect these rules:

Input files: F1 last week, F2 current week.

The first output file should contain the records that only are present in F1.
The second output file should contain the records that only are present in F2.
The third output file should contain the records that exist in both F1 and F2 but have been modified (i said that the UserID was the key, because i think you will have to make the comparison using that field).

The files shouldn´t have duplicates as the UserID is unique. The Name or Time can be duplicated of course, but depending on what you consider for key it may or may not be relevant.

The RECFM and LRECL may not be important as i will probably have to change them.
I just want to figure out how can i do this.

My first approach was to using splice and the UserID as key, see what UserIds are in F1 that aren´t in F2 and vice-versa, and write them to output files 1 and 2, but know i don´t know how to deal with the third case in which there hsa been a modification on the name or the time and therefore the record should be written to output file 3.

Thank you
webber
 
Posts: 8
Joined: Thu Jul 08, 2010 2:29 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Using DFSORT to match and process two input files

Postby Frank Yaeger » Thu Jul 08, 2010 11:20 pm

Your explanation doesn't help ... in fact, it's even more confusing. Do the new fields relate to the original fields and example, or are they a different set of fields. I don't see how the Userid, Name and Time fields relate to the rules you've given about weeks.

Again:

Please show a better 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.
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: Using DFSORT to match and process two input files

Postby webber » Thu Jul 08, 2010 11:34 pm

I will try to example:

F1:

ID01 John 30
ID02 Susan 40
ID03 Frank 10
ID04 Mario 15


F2:

ID01 JohnT 30
ID02 Susan 40
ID03 Frank 5
ID05 Charlie 3


Now, the rules:
The first output file should contain the records that only are present in F1.
The second output file should contain the records that only are present in F2.
The third output file should contain the records that exist in both F1 and F2 but have been modified

Output1 - Only present in F1 using the UserID for comparison to answer the question - Has a user been removed?:
ID04 Mario 15

Output2 - Only present in F2 using the UserID for comparison to answer the question - Has a user been added?:
ID05 Charlie 3

Output3 - Modified records using the entire 'line' to answer the question - Has been any modification?:
ID01 JohnT 30
ID03 Frank 5
webber
 
Posts: 8
Joined: Thu Jul 08, 2010 2:29 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Using DFSORT to match and process two input files

Postby Frank Yaeger » Fri Jul 09, 2010 1:54 am

Here's a DFSORT JOINKEYS job that will do what I think you're asking for. I assumed the input files have RECFM=FB and LRECL=15, but you can change the job appropriately for other attributes. You'll need the Nov, 2009 DFSORT PTF to use JOINKEYS. If your site doesn't have that PTF installed, ask your System Programmer to install it.

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/15)
//IN2 DD DSN=...  input file2 (FB/15)
//OUT1 DD SYSOUT=*
//OUT2 DD SYSOUT=*
//OUT3 DD SYSOUT=*
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,4,A),SORTED
  JOINKEYS F2=IN2,FIELDS=(1,4,A),SORTED
  JOIN UNPAIRED,F1,F2
  REFORMAT FIELDS=(F1:1,15,F2:1,15,?)
  OPTION COPY
  OUTFIL FNAMES=OUT1,INCLUDE=(31,1,CH,EQ,C'1'),BUILD=(1,15)
  OUTFIL FNAMES=OUT2,INCLUDE=(31,1,CH,EQ,C'2'),BUILD=(16,15)
  OUTFIL FNAMES=OUT3,
    INCLUDE=(31,1,CH,EQ,C'B',AND,
      (1,15,CH,NE,16,15,CH)),BUILD=(16,15)
/*
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: Using DFSORT to match and process two input files

Postby webber » Fri Jul 09, 2010 6:24 am

Thank you for your reply.

I had already researched into joinkeys, but the site doesn't have that PTF and it shouldn't be possible to install.

Without wanting to sound ungrateful, is there any other way of doing it?

Many thanks,
Regards
webber
 
Posts: 8
Joined: Thu Jul 08, 2010 2:29 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Using DFSORT to match and process two input files

Postby Frank Yaeger » Fri Jul 09, 2010 9:11 pm

You can use a DFSORT/ICETOOL job like this:

//S2 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/15)
//IN2 DD DSN=...  input file2 (FB/15)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT1 DD SYSOUT=*
//OUT2 DD SYSOUT=*
//OUT3 DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT1) ON(1,4,CH) WITH(16,11) WITH(27,1) -
  KEEPNODUPS USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(27:C'BB')
/*
//CTL2CNTL DD *
  INREC BUILD=(1,4,16:5,11,27:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT1,INCLUDE=(27,2,CH,EQ,C'BB'),BUILD=(1,15)
  OUTFIL FNAMES=OUT2,INCLUDE=(27,2,CH,EQ,C'VV'),BUILD=(1,4,16,11)
  OUTFIL FNAMES=OUT3,
    INCLUDE=(27,2,CH,EQ,C'VB',AND,
      5,11,CH,NE,16,11,CH),BUILD=(1,4,16,11)
/*
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: Using DFSORT to match and process two input files

Postby webber » Sat Jul 10, 2010 12:35 am

Thank you!

It doesn't seem to work very well but i will look into it further.
Meanwhile i managed to achieve the desired output using splice and making a set of comparisons.

Thank you again!
Regards
webber
 
Posts: 8
Joined: Thu Jul 08, 2010 2:29 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post