Database Table Reconciliation

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.

Deployment Summary
Component Name Machine Name Port Numbers
DA server mars
  • 4500 – RMI
  • 4501 – TDS
  • 4503 – DASD
SAP Adaptive Server Enterprise venus 5000 – server
SAP Adaptive Server Enterprise pluto 5000 – server
  1. Follow step 1 to step 5 of the single-server deployment example to start your DA server instance and connect to your databases.
  2. Create a new compareset to map the entire source table:
    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
    Warning!  DA server reconciles only columns that are mapped in the compareset. Using a compareset that partially maps a table for reconciliation may lead to automatic reconciliation errors and defective reconciliation scripts.
  3. Create a new job:
    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
    Note: You can set the job comparison auto_reconcile option to true to automatically reconcile data differences.
  4. Execute the new job:
    run job authors_job3
go
(1 row affected)
  5. Monitor the job:
    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%
  6. Obtain the job history ID:
    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)
  7. Use the history ID to view the job history:
    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)
  8. Execute the show report with the job history ID.
    show report authors_job3 1  
go
    The 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)
  9. Execute the cmp_authors3_T_recon_ins insert reconciliation script against the target database:
    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)
    An example of the reconciliation script:
    --
-- 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
Parent topic: Getting Started