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

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