ISeries AS/400 AS400 Command: PCMPDBF Compare Database Files

PCMPDBF Command for IBM ISeries AS/400 AS400

Introduction

PCMPDBF compares records in two versions of same database (PF, LF) or DDM file. This is one of the commands in our ISeries utilities library (click this URL for full contact and pricing details). This command is useful to compare before/after file contents e.g. following a system conversion or during a parallel run.

The compare is at field level. One record for every field difference is written to a results file PCMPDBRS - or your own copy of PCMPDBRS. PCMPDBRS has records with unmatched keys (on either version of the file) or - if keys match - has one record per different field value. If the file is not keyed, then the relative record number appears in the key field. The PCMPDBRS records include an audit date/time the record was created and the field type - A for Alphanumeric, P for Packed Decimal etc. You can develop your own query/report program to run over the PCMPDBRS member to select and format the results. For a sample of such a report, run the program PSPUTIL/PCMPDBFR - with a suitable OVRDBF to the member(s) in PCMPDBRS.

Fields can be ignored in the comparison e.g. a timestamp field that you know will be different in the two versions of a file you are comparing. This is achieved via the IGNFLD parameter on the PCMPDBF command - this allows up to 20 field names that will be ignored. You can also ignore field comparisons by selectively filtering the output records in the PCMPDBRS member.

Examples

PCMPDBF FILE1(GLDATA/GLMAST) MBR1(*FIRST) FILE2(GLDATAOLD/*FILE1) MBR2(*MBR1) MBRRS(GLDATA)

Command above compares first members in each of two versions of file GLMAST - one in GLDATA library, the other in GLDATAOLD library. Differences are written into the member GLDATA in the file PSPUTIL/PCMPDBRS - replacing any records already in that member.

PCMPDBF FILE1(GLDATA) MBR1(M1997) MBR2(M1998) RSFILE(MYLIB/PCMPGL) MBRRS(COMP9798) MBROPT(*ADD) IGNFLD(DTEAUD TIMAUD)

Command above compares members M1997 and M1998 in file GLDATA in *LIBL. Differences are appended to the member COMP9798 in the file MYLIB/PCMPGL (which is a clone of PCMPDBRS). PCMPDBF automatically creates MYLIB/PCMPGL if it does not already exist. The values of the DTEAUD and TIMAUD fields are ignored in the comparison.

Restrictions

1) External definition: FILE1 must be externally defined. It is analysed for record layout (key and non-key fields) and record length. FILE2 is assumed to have the same record layout and record length. So FILE2 does not have to be externally defined, as long as it has the same implicit record layout/length as FILE1.

2) The field names reported in PCMPDBRS are those in FILE1. If FILE2 has different field names, these do not appear in PCMPDBRS.

3) Single record format in FILE1 (FILE2 assumed to have same format).

4) Non-keyed files: Files do not have to be keyed. In this case, processing is simply by relative record number (RRN), which is the value that appears for the key in PCMPDBRS. If arrival sequence is a concern, you may need to sort the underlying records prior to running the command. To keep life simple, there is a restriction that MBR1 and MBR2 must have same number of records if not keyed.

5) Keyed files: Files do not have to be keyed unique, as long as you accept that records with the same key may not be compared exactly - arrival sequence is not predictable.

6) FILE1 and FILE2 do not have to be physical files or logical files. Either/both may also be a DDM file, in which case the MBR1 and MBR2 values on the PCMPDBF command are ignored. A DDM file itself has no members, although you may create it to point to selected member(s) in the file on the remote system.

7) Maximum 99 keys and 999 fields with maximum record length of 4K (4096 bytes): These limitations can be bumped up quite easily if needs be.

8) PCMPDBRS results: For every field-level difference, a record is written to the PCMPDBRS member you select. This includes fields (e.g. RSAV1, RSAV2) that show the values that are different. For large field values it is difficult to show exactly where the difference occurs. So if you look at a PCMPDBRS record and do not see an obvious field difference, always examine the original files that are being compared to detect the actual difference. The difference does exist - PCMPDBRS may just not show it exactly.

9) Field types: The following DDS types are fully supported:

A (Alphanumeric)

P (Packed Decimal)

S (Zoned Decimal)

F (Floating Point)

L (Date)

T (Time)

Z (Timestamp)

DDS types B (Binary) and H (Hexadecimal) are supported, but with two restrictions:

A binary or hexadecimal field can not be a key field.

In the record in the PCMPDBRS results file, a different value for a a Binary field is reported in the RSAV1 and RSAV2 fields with the word *BIN and a different value for a hexadecimal field is reported with the word *HEX. This is done because there is no absolutely safe way to capture the binary/hexadecimal values (they could contain unprintable characters). You still see which record/field has the difference, but have to look in the original database files to find out the actual values that differ.

10) Field lengths: Alphanumeric fields (DDS type A) are limited to maximum 4096 characters. Numeric fields (DDS types P/S/F) are limited to maximum 15 decimal places and 29 digits in total.

11) Floating point numbers: If you use floating point fields (DDS type F) then you need to have a good understanding of how their numeric values are actually stored in your database files. Otherwise PCMPDBF may report a difference that you struggle to reconcile with your actual data.

IBM rebranded the AS/400 - some users called it AS400 - to ISeries several years ago. All 3 names - ISeries, AS/400, AS400 - refer to exactly the same system.