IBM Mainframe Forum

by **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.

by **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.

Select all

//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:

Select all

0003 ABCD K3 Vicky A 01
0005 MNQR N1 Carrie X 03
0008 MPQR A5 Holly D 01

by **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.

by **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.

by **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.

Select all

----+----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.

Select all

----+----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

Select all

----+----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

Select all

----+----1----+----2----+-
**************************
0003 ABCD Vicky 01
0005 MNQR N1 X 03
0008 A5 Holly D 01
0011 STUD Mary M

by **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?

by **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.

Select all

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.

Select all

0006
0007
0015
0103
1023

I need to produce above two output files from the given three given input files.

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

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

Select all

//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)
/*

by **savitha_y** » Mon May 18, 2009 2:55 pm

Hi Frank,

It is working as expected.Thanks a lot.

IBM Mainframe Forum

ICETOOL to mathching records from 3 files

ICETOOL to mathching records from 3 files

Re: ICETOOL to mathching records from 3 files

Re: ICETOOL to mathching records from 3 files

Re: ICETOOL to mathching records from 3 files

Re: ICETOOL to mathching records from 3 files

Re: ICETOOL to mathching records from 3 files

Re: ICETOOL to mathching records from 3 files

Re: ICETOOL to mathching records from 3 files

Re: ICETOOL to mathching records from 3 files