具有不同表结构的两个DB2表中的数据值之间的差异 [英] Difference between data values in two DB2 tables with different table structures

问题描述

我在大型机旧"和新"上有两个db2表.截至目前，这些表具有10亿条记录，每个记录有70-80列.这些表是从两个不同的系统填充的，因此表的结构并不相同，即列之间存在一些差异，两个表共享一些公共列，必须针对不同的数据值进行比较.例如:

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

因此，在上面的示例中，旧"和新"具有必须比较的列B和C，并且在公共列中具有不同值的行必须写入到具有所有公共列的另一个表中.

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.

我确实读过有关except&可以使用并集函数，但是这些额外的约束(表结构的差异和并非所有ID都按顺序出现)使查询过于复杂.

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.

谢谢.

推荐答案

这是我比较表的通用方法.

This is my generic how to compare tables.

修改为仅使用两列.

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 

如果需要保存结果，请将以上内容包装在create table语句中.

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

这篇关于具有不同表结构的两个DB2表中的数据值之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！