ICETOOL to mathching records from 3 files



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

ICETOOL to mathching records from 3 files

Postby savitha_y » Mon May 11, 2009 10:50 pm

Hi
I have a requirement, where i use three files and create a single file with matching records across all the 3 files using a key field. pls find the layouts given for input files below.

----+----1
**********
0003 K3 A
0008 A5 D
0005 N1 X
1023 C2 L

----+----1----+----2--
**********************
0003 Vicky 01
0005 Carrie 03
0006 Carrie 04
0007 Vicky 02
0008 Holly 01
0015 Frank 01
0103 David 02

----+----1
**********
0003 ABCD
0005 MNQR
0006 PBAC
0008 MPQR
0007 XYZP
0015 STYU
0103 DEBM

i need to create an output file matching key value at pos 1 of length 4 across all files. my outut should look like
----+----1----+----2----+-
**************************
0003 ABCD K3 Vicky A 01
0005 MNQR N1 Carrie X 03
0008 MPQR A5 Holly D 01

i tried with KEEPNODUPS and WITHANY options in splice function of icetool. It is not giving me the expected output. could you pls suggest me if any option available in icetool.
Thanks.
savitha_y
 
Posts: 18
Joined: Mon May 11, 2009 3:05 pm
Has thanked: 0 time
Been thanked: 0 time

Re: ICETOOL to mathching records from 3 files

Postby Frank Yaeger » Mon May 11, 2009 11:49 pm

Here's a DFSORT/ICETOOL job that will do what you asked for. I'm not sure if I got all of your data positions right, so adjust as necessary.

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/10)
//IN2 DD DSN=...  input file2 (FB/22)
//IN3 DD DSN=...  input file3 (FB/10)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/26)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
COPY FROM(IN3) TO(T1) USING(CTL3)
SPLICE FROM(T1) TO(OUT) ON(1,4,CH) WITHANY -
  WITH(6,4) WITH(14,8) WITH(25,2) USING(CTL4)
/*
//CTL1CNTL DD *
  INREC BUILD=(1,4,11:6,2,23:9,1,26:X)
/*
//CTL2CNTL DD *
  INREC BUILD=(1,4,14:6,8,25:21,2)
/*
//CTL3CNTL DD *
  INREC OVERLAY=(26:X)
/*
//CTL4CNTL DD *
  OUTFIL FNAMES=OUT,
    OMIT=(6,4,CH,EQ,C' ',OR,11,2,CH,EQ,C' ',OR,25,2,CH,EQ,C' ')
/*


OUT would have:

0003 ABCD K3 Vicky    A 01   
0005 MNQR N1 Carrie   X 03   
0008 MPQR A5 Holly    D 01   
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: ICETOOL to mathching records from 3 files

Postby savitha_y » Tue May 12, 2009 1:13 pm

Hi Frank,

Thanks a lot. It is working as expected. But the problem with the input files is that there is no gaurentee that all fields will have always data except key field. If the fields which we are using in OMIT clause does not have data, it is not written onto output in the above example. But in my case i need to write that record too as it has match across 3 files. Could you please advice me if it is possible to get the required output using ICETOOL.

Also I need to have all the key values which are not common across all files. In the above example I should get output as

----+
*****
0006 - from file2 and file3
0007 - from file2 and file3
0015 - from file2 and file3
0103 - from file2 and file3
1023 - from file1

Please advice me if it is possible using ICETOOL..

Thanks in advance.
savitha_y
 
Posts: 18
Joined: Mon May 11, 2009 3:05 pm
Has thanked: 0 time
Been thanked: 0 time

Re: ICETOOL to mathching records from 3 files

Postby Frank Yaeger » Tue May 12, 2009 8:46 pm

I'm sure it is possible, but first you have to give a better description and example.

I gave you a job that worked for the example you showed. In that example, your output only had records 3, 5 and 8. Now you appear to be saying you want other records as well. Why didn't you show those in the output for your original example?

Please show a better example of the records in your input files and what you expect for output covering all possible cases.

Give the RECFM and LRECL of each input file. Give the starting position, length and format of all fields in the input files and the output file.
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: ICETOOL to mathching records from 3 files

Postby savitha_y » Tue May 12, 2009 10:51 pm

Hi Frank,

pls find my input file details. At present i don't have exact data with me but have file layouts are as follows.

----+----1
**********
0003
0008A5  D
0005N1  X
0011    M
1023C2  L


this file is of length 10 and FB. file does not have any data for the record with key 0003

layout of file1
----------------
cus-num 4 bytes starting from 1st position and of length 4bytes
acc-id of 2 bytes starting from 5th position and of length 2bytes
cd-ind of one byte at position 9.

----+----1----+----2--
**********************
0003Vicky           01
0005                03
0006Carrie          04
0007Vicky           02
0008Holly           01
0011Mary
0015Frank           01
0103David           02


this is of length 22 and FB. file does not have any value in name field for record with key 0005

layout of file2
----------------
cus-num of 4 bytes starting from 1st position
cus-name of 8 bytes starting from 5th position
grp-cd of bytes starting from 21st position

----+----1
**********
0003ABCD
0005MNQR
0006PBAC
0008
0007XYZP
0011STUD
0015STYU
0103DEBM


this file is of length 10 and FB. file does not have data for record 0008.

layout of file3
----------------
cus-num of 4 bytes starting from 1st position
soc-name of 4 bytes starting from 5th position

As the records with key values 0003,0005 and 0008 are common across all files, i need to get the output as below. Length of output file is 26

----+----1----+----2----+-
**************************
0003 ABCD    Vicky      01
0005 MNQR N1          X 03
0008      A5 Holly    D 01
0011 STUD    Mary     M
savitha_y
 
Posts: 18
Joined: Mon May 11, 2009 3:05 pm
Has thanked: 0 time
Been thanked: 0 time

Re: ICETOOL to mathching records from 3 files

Postby Frank Yaeger » Tue May 12, 2009 10:57 pm

Again, you are only showing the output records that have a common key in all three files (3, 5, 8, 11).

This does not seem to fit with your statement that
Also I need to have all the key values which are not common across all files.

0006 - from file2 and file3
...


Do you need those other records in the output or not? If you do, then show the complete output you need. If you don't need those records in the output, then why did you say you do?
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: ICETOOL to mathching records from 3 files

Postby savitha_y » Wed May 13, 2009 9:42 pm

Hi Frank, Sorry for the confusion made from my end. I need to produce two output files using above input files.

First output file should have matching records across 3 files as given below.

0003 ABCD    Vicky      01
0005 MNQR N1          X 03
0008      A5 Holly    D 01
0011 STUD    Mary     M


Second output file should have only customer number which are not common across the 3 files as below.

0006
0007
0015
0103
1023

I need to produce above two output files from the given three given input files.
savitha_y
 
Posts: 18
Joined: Mon May 11, 2009 3:05 pm
Has thanked: 0 time
Been thanked: 0 time

Re: ICETOOL to mathching records from 3 files

Postby Frank Yaeger » Wed May 13, 2009 10:55 pm

Here's a DFSORT/ICETOOL job that will do what you asked for:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/10)
//IN2 DD DSN=...  input file2 (FB/22)
//IN3 DD DSN=...  input file3 (FB/10)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT1 DD DSN=...  output file1 (FB/26)
//OUT2 DD DSN=...  output file2 (FB/4)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
COPY FROM(IN3) TO(T1) USING(CTL3)
SPLICE FROM(T1) TO(OUT1) ON(1,4,CH) WITHANY KEEPNODUPS -
   WITH(6,4) WITH(14,8) WITH(25,2) WITH(27,1) WITH(28,1) WITH(29,1)-
   USING(CTL4)
/*
//CTL1CNTL DD *
  INREC BUILD=(1,4,11:5,2,23:9,1,27:C'1',2X)
/*
//CTL2CNTL DD *
  INREC BUILD=(1,4,14:5,8,25:21,2,28:C'2',X)
/*
//CTL3CNTL DD *
  INREC OVERLAY=(1,4,6:5,4,29:C'3')
/*
//CTL4CNTL DD *
  OUTFIL FNAMES=OUT1,INCLUDE=(27,3,CH,EQ,C'123'),
    BUILD=(1,26)
  OUTFIL FNAMES=OUT2,SAVE,BUILD=(1,4)
/*
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: ICETOOL to mathching records from 3 files

Postby savitha_y » Mon May 18, 2009 2:55 pm

Hi Frank,

It is working as expected.Thanks a lot.
savitha_y
 
Posts: 18
Joined: Mon May 11, 2009 3:05 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post