2 files -multiple match in each, seperate items on output



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

2 files -multiple match in each, seperate items on output

Postby GRAMBO » Fri Jul 01, 2011 5:12 pm

I have 2 files of FB, LREL 150
I want to check for duplicates in the 2 files, for the first 100 characters only
I would like the matched items to be removed to "report1"
When record is in file 1 only - I would like to write the record out to "file1".
When record is in file 2 only - I would like to write the record out to "file1" with static details (eg. "XXXXXX") appended to the record in position 110.
When there are multiple records in file 1 with multiple matching records in file 2 - then match only the "pairs" to the report, and write out the "unpaired" record to the "file1", adding static details as above if the record is on input file2 only.

Trailer totals on record count and 35,11 would be ideal as well for the output file. This something splice can do, maybe more than 1 step required?

Thanks for looking.

For example.
0000000000000011111111111111121000000000000004R MATCH1 12345 CCCCCCCCCCC
0000000000000011111111111111411 00000000005Q MATCH2 12345 DDDDDDDDDDD
0000000000000011111111111111411 00000000005Q MATCH2 12345 DDDDDDDDDDD
0000000000000011111111111111411 00000000005S MATCH3 12345 FFFFFFFFFFF
0000000000000011111111111111411 00000000005NO MATCH 12345 DDDDDDDDDDD

0000000000000011111111111111121000000000000004R MATCH1 12345 CCCCCCCCCCC R
0000000000000011111111111111121000000000000004R MATCH1 12345 CCCCCCCCCCC R
0000000000000011111111111111411 00000000005Q MATCH2 12345 DDDDDDDDDDD Q
0000000000000011111111111111411 00000000005S MATCH3 12345 FFFFFFFFFFF S
0000000000000011111111111111411 00000000005S MATCH3 12345 FFFFFFFFFFF S
0000000000000011111111111111411 00000000005NO MATCH 12345 EEEEEEEEEEE


REPORT
0000000000000011111111111111121000000000000004R MATCH1 12345 CCCCCCCCCCC
0000000000000011111111111111121000000000000004R MATCH1 12345 CCCCCCCCCCC R
0000000000000011111111111111411 00000000005Q MATCH2 12345 DDDDDDDDDDD
0000000000000011111111111111411 00000000005Q MATCH2 12345 DDDDDDDDDDD Q
0000000000000011111111111111411 00000000005S MATCH3 12345 FFFFFFFFFFF S
0000000000000011111111111111411 00000000005S MATCH3 12345 FFFFFFFFFFF S

FILE
0000000000000011111111111111121000000000000004R MATCH1 12345 CCCCCCCCCCC R XXXXXX
0000000000000011111111111111411 00000000005Q MATCH2 12345 DDDDDDDDDDD
0000000000000011111111111111411 00000000005S MATCH3 12345 FFFFFFFFFFF XXXXXX
0000000000000011111111111111411 00000000005NO MATCH 12345 DDDDDDDDDDD
0000000000000011111111111111411 00000000005NO MATCH 12345 EEEEEEEEEEE
GRAMBO
 
Posts: 8
Joined: Fri Oct 15, 2010 10:22 pm
Has thanked: 0 time
Been thanked: 0 time

Re: 2 files -multiple match in each, seperate items on output

 

Re: 2 files -multiple match in each, seperate items on outpu

Postby Frank Yaeger » Sat Jul 02, 2011 2:09 am

Your example doesn't go far enough to show what you want to do for all situations.

For example, what would you want for output in these input cases:

MATCHA, MATCHA in file1 and MATCHA, MATCHA in file2

MATCHB, MATCHB, MATCHB in file1 and MATCHB, MATCHB in file2

MATCHC in file1 and MATCHC, MATCHC, MATCHC in file2?

MATCHD, MATCHD, MATCHD in file1 and MATCHD, MATCHD in file2?

Trailer totals on record count and 35,11 would be ideal as well for the output file.


Show an example of the expected output.

I suspect JOINKEYS would be a better choice than SPLICE for this kind of thing, but I can't say for sure since I don't know what you want to do.
If you want to take a look at what JOINKEYS can do, see:

http://www.ibm.com/support/docview.wss? ... g3T7000174
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: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: 2 files -multiple match in each, seperate items on outpu

Postby GRAMBO » Sat Jul 02, 2011 12:04 pm

I'll try to answer your question, thanks for any help provided.

There should be 2 files at the end -
- 1 file with all the records that are matching "pairs" from the 2 files
- 1 files with all the "unmatched" records from each file
- where a record is unmatched from file2, then "XXXXXX" should be added to column 110

ie.

MATCHA, MATCHA in file1 and MATCHA, MATCHA in file2
- 1st MATCHA file1 matches 1st MATCHA in file2, both written to matching file
- 2nd MATCHA file1 matches 2nd MATCHA in file2, both written to matching file

MATCHB, MATCHB, MATCHB in file1 and MATCHB, MATCHB in file2
- 1st MATCHB file1 matches 1st MATCHB in file2, both written to matching file
- 2nd MATCHB file1 matches 2nd MATCHB in file2, both written to matching file
- 3rd MATCHB file1, written to output file1

MATCHC in file1 and MATCHC, MATCHC, MATCHC in file2?
- 1st MATCHC file1 matches 1st MATCHC in file2, both written to matching file
- 2nd MATCHC file2, written to output file1, "XXXXXX" added to output record in position 110
- 3nd MATCHC file2, written to output file1, "XXXXXX" added to output record in position 110

MATCHD, MATCHD, MATCHD in file1 and MATCHD, MATCHD in file2?
- 1st MATCHD file1 matches 1st MATCHD in file2, both written to matching file
- 2nd MATCHD file1 matches 2nd MATCHD in file2, both written to matching file
- 3rd MATCHD file1, written to output file1
GRAMBO
 
Posts: 8
Joined: Fri Oct 15, 2010 10:22 pm
Has thanked: 0 time
Been thanked: 0 time

Re: 2 files -multiple match in each, seperate items on outpu

Postby dick scherrer » Sun Jul 03, 2011 4:22 am

Hello,

What have you learned about and tried with JOINKEYS?

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

Re: 2 files -multiple match in each, seperate items on outpu

Postby GRAMBO » Mon Jul 04, 2011 7:22 pm

I have managed to use joinkeys to get some way towards my desired output, attached below.

Am I able to place all the unmatched output in one file (i.e F1ONLY & F2ONLY) to save merging the output in a later step?

I also need 2 trailer counts on each file (a debit and credit total) with the number of items and value.
The value is taken from col 36,11 (format 00000000000) and the debit/credit indicator is in col 16,2.
(debit indicator = 99 or 86, credit indicator = 01 or 17) Can this be done or do I need another step?

Thanks,

//JOIN1    EXEC PGM=SORT,COND=(0,NE)                 
//SYSOUT   DD SYSOUT=*                               
//SORTJNF1 DD DSN=DEA.DDMGMA.SRQ,DISP=SHR           
//SORTJNF2 DD DSN=DEA.DDMGMA.OTHER,DISP=SHR         
//F1ONLY   DD DSN=DEA.DDMGMA.F1ONLY,                 
//            DISP=(,CATLG,CATLG),                   
//            SPACE=(CYL,(100,50),RLSE),             
//            DCB=(DSORG=PS,RECFM=FB,LRECL=130)     
//F2ONLY   DD DSN=DEA.DDMGMA.F2ONLY,                 
//            DISP=(,CATLG,CATLG),                   
//            SPACE=(CYL,(100,50),RLSE),             
//            DCB=(DSORG=PS,RECFM=FB,LRECL=130)     
//BOTH     DD DSN=DEA.DDMGMA.MATCH,                 
//            DISP=(,CATLG,CATLG),                   
//            SPACE=(CYL,(100,50),RLSE),             
//            DCB=(DSORG=PS,RECFM=FB,LRECL=130)     
//SYSIN DD *                                         
  JOINKEYS FILE=F1,FIELDS=(1,100,A,102,6,A)         
  JOINKEYS FILE=F2,FIELDS=(1,100,A,102,6,A)         
  JOIN UNPAIRED,F1,F2                               
  REFORMAT FIELDS=(F1:1,124,F2:1,124,?)             
  OPTION COPY                                       
  OUTFIL FNAMES=F1ONLY,INCLUDE=(249,1,CH,EQ,C'1'),   
  BUILD=(1,124,C'XXXXXX')                           
  OUTFIL FNAMES=F2ONLY,INCLUDE=(249,1,CH,EQ,C'2'),   
  BUILD=(125,124,6X)                           
  OUTFIL FNAMES=BOTH,INCLUDE=(249,1,CH,EQ,C'B'),
  BUILD=(1,124,6X/,125,124,6X)                 
/*                                             
GRAMBO
 
Posts: 8
Joined: Fri Oct 15, 2010 10:22 pm
Has thanked: 0 time
Been thanked: 0 time

Re: 2 files -multiple match in each, seperate items on outpu

Postby Frank Yaeger » Wed Jul 06, 2011 3:12 am

You seem to have changed the LRECL, positions, etc between your first post and your latest post. It's difficult to hit a moving target, so I went back to your first post for the attributes and positions. You can use a DFSORT JOINKEYS job like the following to create the REPORT and FILE output files you asked for:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB/150)
//IN2 DD DSN=... input file2 (FB/150)
//REPORT DD DSN=...  output file1 (FB/150)
//FILE DD DSN=...  output file2 (FB/150)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,100,A,151,10,A)
  JOINKEYS F2=IN2,FIELDS=(1,100,A,151,10,A)
  JOIN UNPAIRED,F1,F2
  REFORMAT FIELDS=(F1:1,160,F2:1,160,?)
  OPTION COPY
  OUTFIL FNAMES=REPORT,INCLUDE=(321,1,CH,EQ,C'B'),
    BUILD=(1,150,/,161,150)
  OUTFIL FNAMES=FILE,SAVE,
    IFOUTLEN=150,
    IFTHEN=(WHEN=(321,1,CH,EQ,C'1'),
      BUILD=(1,150)),
    IFTHEN=(WHEN=NONE,
      BUILD=(161,109,C'XXXXXX'))
/*
//JNF1CNTL DD *
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,100),PUSH=(151:SEQ=10))
/*
//JNF2CNTL DD *
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,100),PUSH=(151:SEQ=10))
/*


I still don't know what you want exactly for the trailers. Please show a better example of your input files and expected output files with trailers and explain what you want using the 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: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post