Compare files in AS400

45 pts.
Tags:
AS/400 command functions
AS/400 DB2
AS/400 File Sharing
CMPPFM
When i compare two files using CMPPFM command, i am getting message as request failed. This is because files were having huge volume of data. It was showing file exceeds 112498 records. Is there any other way to compare files of large volume of data in AS400. Please let me know.

Answer Wiki

Thanks. We'll let you know when a new response is added.

The size limitation is a known issue with the CMPPFM command.
I have split the large files into smaller files when I needed to do that in the past.
If you not worried about the sequence of the data, just what records are in one file but not the other, you can do that with SQL.
I keep all my source in QSQLSLC. Here is an example.
LMK if you need more information.
<pre>
INSERT INTO WRKENCRPT/ADDPF
(SYS, PRN, AGT, ACCTNO, CLIENT,
STRTDAT, ENDDAT, CHGDAT, USRPRF)
SELECT SYS, PRN, AGT, ACCTNO, CLIENT, STRTDAT,
CURDATE(), CHGDAT, USRPRF FROM WRKENCRPT/ACCTMSTR A
WHERE CLIENT <> ’1379′ AND
NOT EXISTS (SELECT * FROM
ICULIB/ACCTMSTR B WHERE A.ACCTNO = B.ACCTNO AND
A.SYS = B.SYS AND A.PRN = B.PRN AND A.AGT = B.AGT);</pre>

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Although the CMPPFM command can run against PF-DTA file members, the help text should make it clear that it is intended as a source comparison tool. The first line of help text:
    • This command allows you to compare source physical file members.
    Source file members with more than 112498 records are indeed excessive, though not impossible nor unheard of. First, are these actually source files or are they database files? If these are source files, you might be better off using the cmp - Compare two files Qshell utility. Actually, that might also be better for database file comparison, though I'd always be wary of any comparison tool for those. Such comparisons are generally made on a line-by-line basis with no field-level interpretation. One file might have a 2-byte character field beginning in, say, position 10 of the buffer. The second file might have a 16-bit integer field in the same buffer position. Both files might have x'4040' at that position, but the data is definitely different -- blanks in the first file and numeric 16448 in the second file. The record formats are not considered in the comparison. What exactly are you trying to accomplish? What business problem are you needing to solve? Those are usually easier questions to answer than trying to say how to use a particular tool for what might not be appropriate. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    I might be thinking about writing an RPG program for this... Phil
    49,850 pointsBadges:
    report
  • TomLiotta
    writing an RPG program for this… I probably agree (with possible exceptions depending on exactly what is being done.) The two files could be read into data structures record by record. As long as the DSes matched, the next records from the files would be read. Upon mismatch, some kind of logic would need to decide which was read next. It'd almost be a 'matching records' function, but sort sequencing could be a big problem. If this is merely some kind of textual data stored in source members, sorting might make no sense. Then again, sorting might be manipulated in views that might simplify some logic. Without knowing what needs to be accomplished, it's hard to guess. Tom
    125,585 pointsBadges:
    report
  • Yorkshireman
    writing an RPG program for this… lots to consider when trying to do these functions once you move outside a simple binary compare. Unless there's some kind of spec it can turn into a GGM (*) Is it text? is it numeric, is it field by field or merely inserted / de;eted records - what if they are moved in sequence? what if text changes case? etc etc (*) - Great Grimpen Mire - one step off the path that no-one knows is there and you sink)
    5,580 pointsBadges:
    report
  • Thotts
    [...] 4. CharlieBrowne, TomLiotta, Philpl1jb, and Yorkshireman pitched in to help a member compare files in AS/400. [...]
    0 pointsBadges:
    report
  • RamvishakRamesh
    We can achieve this by using SQL query.  Something like this will work Select * from file1 where field1||field2||field3 not in (select a.field1||a.field2||a.field3 from file2 a) . Write the query in reverse way also to find those records in file2 but not in file1
    2,505 pointsBadges:
    report
  • BigKat
    SELECT * FROM T1 A1 B1 A A A B ******** End of data ******** SELECT * FROM T2 A2 B2 A B B B ******** End of data ******** SELECT * FROM T1 FULL OUTER JOIN T2 ON A1 = A2 AND B1 = B2 WHERE A1 IS NULL A1 B1 A2 B2 - - B B ******** End of data ******** SELECT * FROM T1 FULL OUTER JOIN T2 ON A1 = A2 AND B1 = B2 WHERE A2 IS NULL A1 B1 A2 B2 A A - - ******** End of data ******** SELECT * FROM T1 FULL OUTER JOIN T2 ON A1 = A2 AND B1 = B2 WHERE A1 IS NULL OR A2 IS NULL A1 B1 A2 B2 A A - - - - B B ******** End of data ********
    8,100 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following