Re: Easy way to determine if two tables contain the same data

[Charles Wilt <charles.wilt@xxxxxxxxx>]

A correction...

Apparently
select * from table1
EXCEPT
select * from table2

only checks that all rows in table1 are in table2.  It won't tell you if
table2 has extra rows.  So you need to run it both ways...

(select * from table1
  EXCEPT
 select * from table2)
UNION ALL
(select * from table2
  EXCEPT
 select * from table1)

if you end up with no rows, then the tables are an exact match.

And for the record, this technique works when CMPPFM fails because the
files are to large.  For instance, I just used it on a 250K row table
totaling just over 67MB.  CMPPFM failed with  "exceeds 83368 records" as it
can only compare 16MB.

HTH,
Charles




On Thu, Mar 28, 2013 at 7:28 PM, Charles Wilt <charles.wilt@xxxxxxxxx>wrote:

> D
> on't know if this technique has ever been posted here or not, but I ran
> across the technique last night on a DB2 LUW forum and it works just fine
> on DB2 for IBM i...
>
> If you have a need to compare the contents of two tables to determine if
> they match, here's the easy way.
> select * from table1
> EXCEPT
> select * from table2
>
> In my case, I was refactoring some code and wanted to make sure that the
> output was still the same.
>
> You can even use it on two different version of a table...you just have to
> do a bit more typing.  Example:  Original table has fld1, fld2 and new
> table has fld1,fld2, fld3.
>
> select * from origtable
> EXCEPT
> select fld1,fld2 from newtable
>
> I was doing it on 7.1, but according to the manual, except is supported
> starting at v5r4.
>
> Cavats: the tables must have a primary key; or be a uniquely keyed
> physical.
>
> HTH,
> Charles