Difference between data values in two DB2 tables w

问题:

I have two db2 tables on the mainframe 'old' and 'new'. These tables have 1 billion records each as of now with 70-80 columns. These tables are getting populated from two different systems, so the table structure is not identical, i.e. there are some differences in the columns with both tables sharing some common columns which have to be compared for different data values. for example :

OLD
id A B C  
1  x y z
1  x y z
3  m n o
4  e f g

NEW
id B C D E 
1  y a a b
1  y a a b
2  n o c d
4  g g l m

So in the above example 'old' and 'new' have columns B and C which have to be compared for differences and the rows which have different values in common columns have to be written to another table which have all common columns.

I did read about the except& union function which can be used , but these additional constraints(difference in table structure and not all Ids are present in both in order) makes the query too complicated.

Is there any easier way to do this? I cannot install any software/third party tools on the mainframe. Please help.

Thank you.

回答1:

This is my generic how to compare tables.

How to retrieve only those rows of a table (db1) which are not in another table (db2)

Modify to use only two columns.

SELECT 'AFTER', A.* FROM      
(SELECT b,c FROM &AFTER         
EXCEPT                        
 SELECT b,c FROM &BEFORE) AS A  
UNION                         
SELECT 'BEFORE', B.* FROM     
(SELECT b,c FROM &BEFORE        
EXCEPT                        
 SELECT b,c FROM &AFTER) AS B 

Wrap the above in a create table statement if you need to save the results.