When we upgrade applications from Mainframe/UNISYS to ORACLE/SUN environment we need to ensure a perfect synchronization between the new application and the migrated one. In order to achieve this goal, one solution would be to put the content of the Mainframe application into a dedicated table, let’s say mainframe_tab for example. The initial load starts by loading via a sqlloader a flat file into the mainframe_tab table and then it triggers the process of the initial load into the new application. Within the new application very often the migrated data are mapped into new typologies and are changed so that it reflects the new philosophy of a relational data base. Each record “initially loaded” must end by an insert of the same record (get again from the new application) into a clone table to the mainframe_tab; let’s call it the oraclesun_tab. At the end of the initial load we will have at our disposable two tables full of the same data (normally the same data, this why we want to synchronize them). Hence one important step in this synchronization is to find the differences that might exist into these two cloned tables. Here below is a demo on how to find those differences. Thanks a lot to Tom Kyte for his help in this case
</p> <pre>CREATE TABLE mainframe_tab (ide NUMBER, appl VARCHAR2(3), x NUMBER, y NUMBER, w VARCHAR2(15), z VARCHAR2(30) ); create table oraclesun_tab ( ide number, appl varchar2(3), x number, y number, w varchar2(15), z varchar2(30) ); insert into mainframe_tab ( ide, appl, x, y, w, z ) values ( 4, ‘SPS’, 23, 13, ‘Lieven’, ‘Ali’); insert into mainframe_tab ( ide, appl, x, y, w, z ) values ( 3, ‘SPS’, 17, 31, ‘Desire’, ‘Omar’); insert into mainframe_tab ( ide, appl, x, y, w, z ) values ( 2, ‘SPS’, 11, 155.2, ‘Tom’, ‘Asif’); insert into mainframe_tab ( ide, appl, x, y, w, z ) values ( 1, ‘SPS’, 125, 13.2, ‘Richard’, ‘Jean’); insert into oraclesun_tab ( ide, appl, x, y, w, z ) values ( 4, ‘CCO’, 23, 13, ‘Lieven’, ‘Ali’); insert into oraclesun_tab ( ide, appl, x, y, w, z ) values ( 3, ‘CCO’, 17, null, ‘Desire’, ‘Omar’); insert into oraclesun_tab ( ide, appl, x, y, w, z ) values ( 2, ‘CCO’, 11, 155.2, ‘Tom’, ‘Asif’); insert into oraclesun_tab ( ide, appl, x, y, w, z ) values ( 1, ‘CCO’, 125, 13.2, ‘Richard’, ‘Jean-Pol’); commit;
Now we would like to get the differences between these two tables for each ide. Of course the application name (SPS for SUN and CCO for mainframe) should not be considered as a difference. This is the select to issue
<pre>SELECT ide, x, y, z, w, count(src1), count(src2), decode (min (appl_1), NULL, min (appl_2), min (appl_1)) FROM (SELECT t1.*, 1 src1, to_number (NULL) src2, appl appl_1, NULL appl_2 FROM oraclesun_tab t1 UNION ALL SELECT t2.*, to_number (NULL) src1, 2 src2, NULL appl_1, appl appl_2 FROM mainframe_tab t2 ) GROUP BY ide, x, y, z, w HAVING count (src1) <> count (src2);
Ide |
Application |
x |
y |
z |
w |
1 |
SPS |
125,00 |
13,20 |
Jean |
Richard |
1 |
CCO |
125,00 |
13,20 |
Jean-Pol |
Richard |
3 |
SPS |
17,00 |
31,00 |
Omar |
Desire |
3 |
CCO |
17,00 |
|
Omar |
Desire |