IBM Mainframe Forum

Posted: **Fri Feb 27, 2009 3:05 am**

I have requirement to match merge two files on the same key, save matching and non-matching records in two separate files, save record counts in separate files and save the SUM of amount field in a separate file.

I have used the ICETOOL to match merge two files and get matching and non matching records using the sample from the book, but I am not sure if I can get the counts in each file and get the SUM in the same step. I was planning to create a separate steps to get record COUNTs and SUM.

Since my input datasets are huge, I would like to get the COUNT and SUM in the same step rather then adding additional SORT steps. Can some one tell me how OCCURS or COUNT can be used in SPLICE datasets and if I can save the information in a dataset?

Select all

//S5 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=... input File1 (FB/10)
//IN2 DD DSN=... input File2 (FB/10)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),
//** USE MOD FOR T1
// DISP=(MOD,PASS)
//OUT12 DD SYSOUT=* names in File1 and File2
//OUT1 DD SYSOUT=* names in File1 only
//OUT2 DD SYSOUT=* names in File2 only
//TOOLIN DD *
* Add 'BB' identifier for File1 records.
COPY FROM(IN1) TO(T1) USING(CTL1)
* Add 'VV' identifier for File2 records.
COPY FROM(IN2) TO(T1) USING(CTL2)
* SPLICE to match up records and write them to their
* appropriate output files.
SPLICE FROM(T1) TO(OUT12) ON(1,10,CH) WITH(11,1) -
USING(CTL3) KEEPNODUPS
/*
//CTL1CNTL DD *
* Add 'BB' (base) identifier to File1 records.
INREC OVERLAY=(11:C'BB')
/*
//CTL2CNTL DD *
* Add 'VV' (overlay) identifier to File2 records.
INREC OVERLAY=(11:C'VV')
/*
//CTL3CNTL DD *
* Write matching records to OUT12 file. Remove id.
OUTFIL FNAMES=OUT12,INCLUDE=(11,2,CH,EQ,C'VB'),BUILD=(1,10)
* Write File1 only records to OUT1 file. Remove id.
OUTFIL FNAMES=OUT1,INCLUDE=(11,2,CH,EQ,C'BB'),BUILD=(1,10)
* Write File2 only records to OUT2 file. Remove id.
OUTFIL FNAMES=OUT2,INCLUDE=(11,2,CH,EQ,C'VV'),BUILD=(1,10)
/*

Posted: **Fri Feb 27, 2009 3:53 am**

It isn't clear what you want the output to look like, so it's difficult to tell you how to do it. But I can give you some general guidance.

The OUTFIL statement can be used to get COUNTs and TOTALs (SUMs). So if you want a count for each output data set in a separate file and the sum of an amount in each output data set in a separate file, you could use additional OUTFIL statements something like this:

Select all

OUTFIL FNAMES=CT12,INCLUDE=(11,2,CH,EQ,C'VB'),
REMOVECC,NODETAIL,
TRAILER1=(COUNT=(M10,LENGTH=10))
OUTFIL FNAMES=TOT12,INCLUDE=(11,2,CH,EQ,C'VB'),
REMOVECC,NODETAIL,
TRAILER1=(TOT=(1,10,ZD,M10,LENGTH=10))
...

and add similar OUTFIL statements for the other output files.

Of course, exactly what you'd need to code depends on exactly what you want to do. If you want more specific help, give more details on what you want the output files to look like.

Posted: **Sat Feb 28, 2009 12:01 am**

This is exactly what I was looking for. In addition to the COUNT in each file I want to get the SUM of all amount fields in the combined file (OUT1+OUT2+OUT12). I believe I can use SUM FIELDS=(5,1,ZD)

Thank you for your help.

Posted: **Sat Feb 28, 2009 12:28 am**

You can use TOT instead of SUM.

Posted: **Mon Mar 02, 2009 7:47 pm**

Do I have to sort the input files before giving them to ICETOOL step? If not then how do I allocate space for sort work space?

Both of my input files are 32K VB and have over 40 Million records in each file. Do I have to allocate space for T1 equal to the space in IN1 and IN2?

Posted: **Mon Mar 02, 2009 9:27 pm**

Do I have to sort the input files before giving them to ICETOOL step? If not then how do I allocate space for sort work space?

No. The SPLICE operator will sort the copied files using dynamically allocated sort work space. You don't have to allocate the work space yourself.

Both of my input files are 32K VB and have over 40 Million records in each file. Do I have to allocate space for T1 equal to the space in IN1 and IN2

Maybe. Maybe not.

Why did you show your input files as FB/10 in your example if they're really VB/32K? 32K is the maximum LRECL - it doesn't mean that every record is 32K. Also, it may be that you can make the records smaller when you copy them, depending on how much of the record you really need for your expected output. Since you've never really given a good description of what you're doing, it's difficult to answer your questions.

Posted: **Tue Mar 03, 2009 12:18 am**

I started with the example in the book and wanted to get a working script with FB files. I will add the RDW for the VB after coming up with a working version of the script.

Both of my input have LRECL=32007 VB. One has current week data and the other has previous week data. I will produce three outputs:
1. Matching records in both files based on a key (17,11,BI)
2. Records present only in input file 1
3. Records present only in input file 2

This means output files will have the same layout as the inputs. These files will be fed into other JCLs for further processing.

In addition to the above files I will also produce some intermediate output files with:
- SUM of amounts in each file based on certain condition.
- Record count in each file

I will read the intermediate files using REXX to update the DB2 database.

Posted: **Tue Mar 03, 2009 12:53 am**

Hello,

I will read the intermediate files using REXX to update the DB2 database.

If the intermediate files will be high volume (i can't tell for sure from the description), rexx will not be a good choice - rexx is very poor at high processing volume files.

Posted: **Tue Mar 03, 2009 1:17 am**

Each intermediate file contains only one record(either COUNT or SUM of amount etc). So REXX will not have a problem in reading these single record files. High volume files are give to external systems for further processing.

Posted: **Tue Mar 03, 2009 2:25 am**

Thanks for the followup

d

IBM Mainframe Forum

How to store Matching and Non-Matching record counts?

How to store Matching and Non-Matching record counts?

Re: How to store Matching and Non-Matching record counts?

Re: How to store Matching and Non-Matching record counts?

Re: How to store Matching and Non-Matching record counts?

Re: How to store Matching and Non-Matching record counts?

Re: How to store Matching and Non-Matching record counts?

Re: How to store Matching and Non-Matching record counts?

Re: How to store Matching and Non-Matching record counts?

Re: How to store Matching and Non-Matching record counts?

Re: How to store Matching and Non-Matching record counts?