SEARCH and MERGE USING ICETOOL



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

SEARCH and MERGE USING ICETOOL

Postby Samreshv » Sat Mar 13, 2010 3:14 am

Hi,
I am new to ICETOOL and tried looking into the forum for a specific requirement. I have to search and create an output file using search and merging the data from 2 Fixed Block files having different LRECL. Second File is a lookup file and I need to pull data from a specific field and merge it with the first file in the end.

First file: SORTED based on Contract# and Mem# field.
<--Contract#--><--Mem#--><--GRP#--><--OtherInfo-->
1111111111111110000000001abc1234567...............
1111111111111110000000002abc1234567...............
2222222222222220000000001def2345678...............
2222222222222220000000002def2345678...............
2222222222222220000000003def2345678...............
3333333333333330000000001ghi3456789...............
4444444444444440000000001abc1234567...............
5555555555555550000000001def2345678...............

Second File: Sorted on Grp# and BenefitCode.
<--Grp#--><--BenefitCode-->
abc1234567Benefit0000000001
def2345678Benefit0000000002
ghi3456789Benefit0000000003


Output file:
<--Contract#--><--Mem#--><--GRP#--><--OtherInfo--><--BenefitCode-->
1111111111111110000000001abc1234567...............Benefit0000000001
1111111111111110000000002abc1234567...............Benefit0000000001
2222222222222220000000001def2345678...............Benefit0000000002
2222222222222220000000002def2345678...............Benefit0000000002
2222222222222220000000003def2345678...............Benefit0000000002
3333333333333330000000001ghi3456789...............Benefit0000000003
4444444444444440000000001abc1234567...............Benefit0000000001
5555555555555550000000001def2345678...............Benefit0000000002

Any help in creating the control statement for ICETOOL is highly appreciated.
Samreshv
 
Posts: 3
Joined: Sat Mar 13, 2010 2:42 am
Has thanked: 0 time
Been thanked: 0 time

Re: SEARCH and MERGE USING ICETOOL

Postby Frank Yaeger » Sat Mar 13, 2010 4:36 am

Here's a DFSORT JOINKEYS job that will do what you asked for. You'll need the Nov, 2009 DFSORT PTF to use JOINKEYS. For details, see:

dfsort-icetool-icegener/topic2894.html

If you don't have the Nov, 2009 PTF installed, ask your System Programmer to install it (it's free).


//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//FILE1 DD DSN=...  input file1 (FB/50)
//FILE2 DD DSN=...  input file2 (FB/27)
//SORTOUT DD DSN=...  output file (FB/67)
//SYSIN DD *
  JOINKEYS F1=FILE1,FIELDS=(26,10,A)
  JOINKEYS F2=FILE2,FIELDS=(1,10,A),SORTED
  REFORMAT FIELDS=(F1:1,50,F2:11,17,F1:68,8)
  SORT FIELDS=(68,8,ZD,A)
  OUTREC BUILD=(1,67)
/*
//JNF1CNTL DD *
  INREC OVERLAY=(68:SEQNUM,8,ZD)
/*
 
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: SEARCH and MERGE USING ICETOOL

Postby Samreshv » Tue Mar 30, 2010 3:31 am

Frank,
Thanks for your reply. It works.
Is there a way in the above sysin to stop dropping the unmatched records from the File1? I am trying to have all the records from File1 on the output file and if there is a match on File2 with the Joinkeys then have the required data values appended in the end of the File1 record.

Explanation:
File1 is 1000 byte file with about 450 bytes of filler at the end. File2 is 250 bytes and these go to the (first 250 bytes of) filler area of File1 and output length remains same as File1 for a matching record. But I don't want to drop File1 records which don't have a matching record on File2 and these all go to output file as 1000 byte record as well.

Appreciate your help.
Samreshv
 
Posts: 3
Joined: Sat Mar 13, 2010 2:42 am
Has thanked: 0 time
Been thanked: 0 time

Re: SEARCH and MERGE USING ICETOOL

Postby Samreshv » Tue Mar 30, 2010 9:02 pm

I got it. I used Joined Unpaired option.
Thanks any way.

Samresh
Samreshv
 
Posts: 3
Joined: Sat Mar 13, 2010 2:42 am
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post