In week 35/2007 I wrote about how easy it is to compare two datasets or members. If you want to compare the content of two DB2 tables you are faced with a much more complicated task. There are no obvious SQL functionality which performs comparisons on a table level. I only know of the tool called Compare for DB2 from Princeton Softech which is able to do the task, but I think BMC and CA has similar products. But what do you do if you do not have any of these products available?

My first suggestion is to use SPUFI. Make a SELECT * form the first table in SPUFI and then make a SELECT * from the second table, but to another output dataset. Afterwards it is possible to use ISPF EDIT COMPARE to perform the comparison. This approach requires a reasonable ORDER BY on the two SELECT * you issue initially.

If you are only interested in knowing whether the two tables has exactly the same content or not and do not care about which differences there are you may use the following approach. Start by executing a SELECT COUNT(*) from one of the tables and then make a SELECT COUNT(*) from the other table These two counts must of course give the same result. Lastly you execute a UNION of the two tables:

SELECT * FROM new.mytable
UNION
SELECT * FROM old.mytable

If this SQL statement returns the same number of rows as the number of rows returned by the previous SELECT COUNT(*) then the two tables has the same content. The trick about UNION is that two or more identical rows will be returned as one row. You may of course risk that two rows in the same table are identical and if this is the case UNION will remove these duplicates as well and make the approach worthless. If a unique index exist on both tables the approach is 100% reliable.

If you are running DB2 version 7 or later you may perform the whole opeartion in one SQL statement:

SELECT COUNT(*) FROM new.mytable
UNION
SELECT COUNT(*) FROM old.mytable
UNION
SELECT COUNT(*) FROM
( SELECT * FROM new.mytable
  UNION
  SELECT * FROM old.mytable
) temptable

If this SQL statement returns one row the content of the two tables are guaranteed to be identical with the same limitations as mentioned above. In version 7 and later versions it is possible to use full selects (SELECTs with UNIONS or/and ORDER BY) anywhere you may use subselects.