DA can automatically reconcile differences between the source and target databases, or create a SQL script that enables a database administrator to manually reconcile the target database. You can configure DA server to do both tasks simultaneously.
This example uses a single DA server with the local embedded agent, and shows you how to generate a script to reconcile a target table that differs from the source table with one missing row, one inconsistent row, and one orphaned row.
Component Name | Machine Name | Port Numbers |
---|---|---|
DA server | mars |
|
Adaptive Server Enterprise | venus | 5000 – server |
Adaptive Server Enterprise | pluto | 5000 – server |
create compareset authors_demo3 with source conn_venus dbo authors s target conn_pluto dbo authors t map s.au_id = t.au_id set key=true and s.au_lname = t.au_lname and s.au_fname = t.au_fname and s.phone = t.phone and s.address = t.address and s.city = t.city and s.state = t.state and s.country = t.country and s.postalcode = t.postalcode go
create job authors_job3 add comparison cmp_authors3 set compareset = authors_demo3 and set create_col_log = true and set create_recon_script = true go
run job authors_job3 go (1 row affected)
monitor job authors_job3 cmp_authors3 go
COMPARISON SUBMIT TIME END TIME ------------ ------------------- ------------------- cmp_authors3 2012-03-30 10:31:36 2012-03-30 10:31:42 (0 rows affected) RUN PHASE TYPE SUMMARY START TIME END TIME COUNT READ M O I R --- ----------------- ---- ---------------------- ------------------- ------------------- ---- ---- ------- PROGRESS ESTIMATE END ERROR -------- ------------------ 1 COMPARE_ALL S conn_venus/dbo.authors 2012-03-30 10:31:39 2012-03-30 10:31:39 23 23 100% T conn_pluto/dbo.authors 2012-03-30 10:31:39 2012-03-30 10:31:39 23 23 1 1 1 100% 2 VERIFY_DIFFERENCES S 2012-03-30 10:31:40 2012-03-30 10:31:41 2 100% T 2012-03-30 10:31:40 2012-03-30 10:31:41 2 1 1 1 100%
show history authors_job3 go HISTORY ID SUBMIT TIME FINISH TIME ---------- ------------------- ------------------- 1 2012-03-30 10:31:36 2012-03-30 10:31:42 (0 rows affected)
show history authors_job3 1 go
COMPARISON RUN PHASE TYPE SUMMARY START TIME END TIME ----------- --- -------------------------- ---- --------------------------- ----------------- ------------------ COUNT READ M O I R ERROR ----- ---- ------- ----- cmp_authors3 1 COMPARE_ALL S venus:5000/pubs2.dbo.authors 2012-03-30 10:31:39 2012-03-30 10:31:39 23 23 T pluto:5000/pubs2.dbo.authors 2012-03-30 10:31:39 2012-03-30 10:31:39 23 23 1 1 1 2 VERIFY_DIFFERENCES S 2012-03-30 10:31:40 2012-03-30 10:31:41 2 T 2012-03-30 10:31:40 2012-03-30 10:31:41 2 1 1 1 3 CREATE_RECONCILIATION_SCRIPT T 2012-03-30 10:31:41 2012-03-30 10:31:42 3 (0 rows affected) COMPARISON TARGET RECONCILIATION SCRIPT ---------- ------ ---------------------------------------------------------------------------------------------------------- cmp 0 C:\Sybase\DA-15_5\server\instance\data\authors_job3\2012-10-05\09.11.28.585\cmp_authors3_T_recon_ins.sql C:\Sybase\DA-15_5\server\instance\data\authors_job3\2012-10-05\09.11.28.585\cmp_authors3_T_recon_upd.sql C:\Sybase\DA-15_5\server\instance\data\authors_job3\2012-10-05\09.11.28.585\cmp_authors3_T_recon_del.sql (0 rows affected)
show report authors_job3 1 goThe return result is:
FILE SERVER PATH ------------ -------------------------------------------------------------------------------------- Text report C:\Sybase\DA-15_5\server\instance\data\authors_job3\2012-03-30\10.31.36.099\report.txt XML report C:\Sybase\DA-15_5\server\instance\data\authors_job3\2012-03-30\10.31.36.099\report.xml (0 rows affected)This is an excerpt from the text report file:
source venus:5000/pubs2.dbo.authors starttime 2012-03-30 10:31:39 endtime 2012-03-30 10:31:39 target pluto:5000/pubs2.dbo.authors starttime 2012-03-30 10:31:40 endtime 2012-03-30 10:31:41 missing 1 orphaned 1 inconsistent 1
diff |au_id |au_lname |au_fname |phone |address |city ---- -------- ---------- --------- ------------- ---------------- ----------- | state |country |postalcode ------- -------- ----------- |172-32-1176 |Roberts |Alex |408 496-7223 |10932 Bigge Rd. |Menlo Park |CA |USA |94025 |172-32-1176 |White |Johnson |408 496-7223 |10932 Bigge Rd. |Menlo Park |CA |USA |94025 | |^^^^^^^ |^^^^^^^ | | | | | O |213-46-8915 |Green |Marjorie |415 986-7020 |309 63rd St. #411 |Oakland |CA |USA |94618 M |321-78-9087 |Jones |Steve |412 555-6434 |48 Barnaby Close |Walnut Creek |CA |USA |94592 reconciliation script starttime 2012-03-30 10:31:41 endtime 2012-03-30 10:31:42 reconciled 3 (0 rows affected)
C:\>isql -S pluto:5000 -U sa -i "C:\Sybase\DA-15_5\server\myserver\data\ authors_job3\2012-03-30\10.31.36.099\cmp_authors3_T_recon_ins.sql" Password: (1 row affected)
-- -- Replication Server Data Assurance Option/15.7.2/DA Server/P/generic/ generic/da157x/121/VM: Sun Microsystems Inc. 1.6.0_24/OPT/Tue 24 Apr 2012 09:24:31 GMT -- Reconciliation Script (Auto-generated); fixes 1 difference(s). -- Missing: 1 (insert) -- -- Date Created: 2012-03-30 10:31:42 -- File encoding: UTF-8 -- -- Source: dbo.authors on venus:5000/pubs2 -- Target: dbo.authors on pluto:5000/pubs2 -- use pubs2 go -- -- Missing: 1 rows -- begin tran insert into dbo.authors au_id,au_lname,au_fname,phone,address,city,state,country,postalcode) values ('321-78-9087','Jones','Steve','412 555-6434','48 Barnaby Close','Walnut Creek','CA','USA','94592 ') commit tran go