Cmppfm

[kartikchowdary]

Hi, I need to compare the data of a physical file in two libraries. The file is having a million records. But CMPPFM has a limitation on the count of records. Please advice how to overcome this limitation?

[Andrew K]

Re: Cmppfm

Hi,

I have never used (or even heard of using) CMPPFM for physical files; in the help it states that it is for source files and that it what I use it for.

But a quick search reveals that there are those who have used it for PFs: see http://search400.techtarget.com/tip/...892243,00.html

Don't think it works if you have packed fields though.

If I was you I would look at using SQL to analyse the differences.

[kartikchowdary]

Re: Cmppfm

Hi Andrew, thank you for replying. Yes I can use SQL, but the scenario is...... I have more than 1000 files to compare the data. So I would like to input all the files through a program to CMPPFM for comparison.

I have already used it successfully for many files, but for a few files... I got the error message that ..the number of records exceeds the limit. So I need a solution for this scenario.

Please help me if you know any other way to achieve this.

[Chipper]

Cmppfm

I have a few thoughts on how to proceed, but want to make sure I understand your needs:
* Are you trying to find records that exist in one file but not the other?
* Or... find records where values in some fields are different?

At any rate, I've done this on several occasions but some of the techniques I've used involve matching records

[Andrew K]

Re: Cmppfm

Chipper,

You said those words. You know THOSE words....M******* R******

I still have nightmares.

[bryce4president]

Re: Cmppfm

Is this something you want to do a lot? Or just one time?

[kartikchowdary]

Re: Cmppfm

Chipper,

Yes I need to do both the things. I.e. new records and differences in fields.

[Chipper]

Re: Cmppfm

Chipper,

Yes I need to do both the things. I.e. new records and differences in fields.

I wrote a code generator a few years ago which creates a matching records program. The only thing is that you need the Craig Rutledge utility jcrffd in order to be able to run the code generator program. In addition, you need to do some tweaking after the code generator does its thing in order to get it to fully work. Would be happy to share that with you.

Some other options:

Use wrkqry:
1) Select the two files
* Join by key fields
* Type of join = use option #3 (unmatched records with primary file)
2) Then reverse the two files
This would give you the two files and records that exist in one but not the other.

To try to find the records with diff values: If there are only a few fields use wrkqry again
* Select both files with option #2 (Matched rcds with primary file).
* On the "Select Records" screen do a select to find each field that is different.

Note: This post assumes some previous experience with wrkqry. Somebody on the forum may be able to walk you through a solution using SQL.

[kartikchowdary]

Re: Cmppfm

Hi, please...... I dont need to find out which records differ, I just need to check whether there are any differences or not. I can do this by using CMPPFM, for less number of records but if the record count is more then I am getting error message SPC5010. Please provide any solution to avoid the error (I mean is there any solution interms OVRDBF).

The command I am using is like,
CMPPFM NEWFILE(NEWLIB/FILE1) OLDFILE(OLDLIB/FILE1) CMPTYPE(*LINE) RPTTYPE(*SUMMARY)

[kitvb1]

Re: Cmppfm

Yes I can use SQL, but the scenario is...... I have more than 1000 files to compare the data. So I would like to input all the files through a program to CMPPFM for comparison.

You could automate this by using a small sql statement, a small rpgle and maybe also a small clle.

The rpg would read your file list
then open, read, update, and close the file/member containing the sql statement.
run the sql statement.
loop till no more files in the list.

[tomholden]

Re: Cmppfm

here's a quick and dirty i pulled from another program. you can modify this to do your compare.

[kausix777]

Re: Cmppfm

There can be several methods - depends on the kind of data.
1. If your data can be grouped into large parts, you can copy parts of your data into members of a file and use CMPPFM as you please.
2. You can compare the number of records with a simple count(*) SQL query.
3. For differences in records - depends on your key fields and up to what level you want the differences.
Say, your file has fields A B C D E of which A B and C are keys.

Now, do you want two records to be considered different if they have differences only in A B C values or even if they have differences in D E values?

You can use the GROUP BY clause with summary functions and check your results on similar files.

I presume that your team has been updating wrong copies of files, so you need to know which file is current. Am I right?