Single-Server Deployment

Sybase recommends a single-server deployment when there is low network latency between the DA server and the database servers and when few concurrent comparisons are required. A single-server deployment is also easier to deploy and maintain than a distributed deployment.

Before You Begin

This example uses a single Data Assurance (DA) server with the local embedded agent. No remote DA agents are used.

Deployment Summary
Component Name	Machine Name	Port Numbers
DA server	mars	4500 – RMI 4501 – TDS 4503 – DASD 4504 – HTTP
Adaptive Server Enterprise	venus	5000 – server
Adaptive Server Enterprise	pluto	5000 – server

Start your DA server instance:
```
$SYBASE/DA-15_5/server/myserver/RUN_myserver
```
Where $SYBASE is the directory in which you installed the Data Assurance Option, myserver is the name of your DA server instance, and RUN_myserver is the startup script.
Note: On Windows, the startup script file is named RUN_myserver_32.bat or RUN_myserver_64.bat, where myserver is your DA server instance name. On UNIX or Linux platforms, the file is named RUN_myserver_64.sh.
From isql, log in to DA server as an administrator:
```
$SYBASE/OCS-15_0/bin/isql -S mars:4501 -U da_admin -P sybase -w 250
```
Note: 4501 is the default TDS port number for DA server. The TDS port is required when the command line tool connects to the DA server using isql.
Verify the product version:
```
version
go
```

Create the database connections for the local DA agent:

create connection conn_venus
	set agent=localagent 
	and set host=venus
	and set port=5000 
	and set database=pubs2
	and set user=sa	  
	and set password='' 
	go

create connection conn_pluto
	set agent=localagent 
	and set host=pluto
	and set port=5000 
	and set database=pubs2 
	and set user=sa	 
	and set password='' 
	go

Note: In this example, the embedded DA agent localagent connects to the Adaptive Server databases installed on venus and pluto. The user sa and null password are defaults for Adaptive Server Enterprise. For a null password, you can also omit the password parameter.

View the database connections defined within DA:

show connection
	go
 NAME       TYPE AGENT      HOST  PORT DATABASE USER DESCRIPTION
 ---------- ---- ---------- ----- ---- -------- ---- -----------
 conn_venus ASE  localagent venus 5000 pubs2    sa
 conn_pluto ASE  localagent pluto 5000 pubs2    sa
(0 rows affected)

Test the database connections:

test connection conn_venus
go
RESULT
------
Succeeded
(0 rows affected)

test connection conn_pluto
go
RESULT
------
Succeeded
(0 rows affected)

Create a simple compareset for the authors table, comparing only the au_id, au_lname, and au_fname columns:

create compareset authors_demo1
with 
source conn_venus dbo authors s1
target conn_pluto dbo authors t1
map
s1.au_id = t1.au_id set key=true
and s1.au_lname = t1.au_lname
and s1.au_fname = t1.au_fname
go

Create a more complex compareset using the where clause and comparing all columns in the authors table. Exclude from comparison all rows with a state column value of CA:

create compareset authors_demo2
with
source conn_venus dbo authors s1
where "state != 'CA'"
target conn_pluto dbo authors t2
where "state != 'CA'"
map
s1.au_id = t2.au_id set key=true
and s1.au_lname = t2.au_lname
and s1.au_fname = t2.au_fname
and s1.phone = t2.phone
and s1.address = t2.address
and s1.city = t2.city
and s1.state = t2.state
and s1.country = t2.country
and s1.postalcode = t2.postalcode
go

View the comparesets:

show compareset authors_demo1
	go
 TYPE CONNECTION  OWNER TABLE   WHERE CONSTRAINT
 ---- ----------  ----- ------- ----------------
 S    conn_venus  dbo   authors
 T    conn_pluto  dbo   authors
(0 rows affected)

show compareset authors_demo2
	go
 TYPE CONNECTION  OWNER TABLE   WHERE CONSTRAINT
 ---- ----------  ----- ------- ----------------
 S    conn_venus  dbo   authors state != 'CA'
 T    conn_pluto  dbo   authors state != 'CA'
(0 rows affected)

Note: To see compareset column mappings, use the columns option with the show compareset command. For example:

show compareset authors_demo1 columns

Create a row comparison job with default options using the authors_demo1 compareset:

create job authors_job1
 add comparison cmp_authors1
 set compareset=authors_demo1
go

Create another job using the authors_demo2 compareset, and set comparison options explicitly:

create job authors_job2 
   set max_concurrent_comparisons = 10 
   add comparison cmp_authors2
      set compareset=authors_demo2 
      and set compare_mode to row_compare 
      and set abort_diff_row_count to true 
      and set abort_diff_max to 1000
      and set retry_diff to wait_and_retry
      and set retry_max to 3
      and set retry_delay_sec to 10
      and set create_col_log to true
      and set create_recon_script to true
      and set auto_reconcile to false 
      and set hash_type to database_hash 
      and set compress_data_transfer to false
      and set external_sort to false
      with column option 
         set city = literal 
         and set postalcode to column_hash
go

Note: To change the job or comparison options, use alter job.

View the newly created job authors_job2:

show job authors_job2
	go
 OPTION                     VALUE
 -------------------------- -----
 MAX_CONCURRENT_COMPARISONS 10
(0 rows affected)
 COMPARISON   ACTIVE COMPARESET    PRIORITY COMPARE MODE RETRY
         DESCRIPTION
 ------------ ------ ------------- -------- ------------ --------------
         -----------
 cmp_authors2 true   authors_demo2 NORMAL   ROW_COMPARE  WAIT_AND_RETRY
(0 rows affected)
 SCHEDULE ACTIVE TYPE EVERY START DATE TIME KEEP KEEP UNIT CRON DESCRIPTION
 -------- ------ ---- ----- ---------- ---- ---- --------- ---- -----------
(0 rows affected)

View the comparison cmp_authors1 for the newly created job authors_job1:

show job authors_job1 cmp_authors1
go
 OPTION                 VALUE
 ---------------------- -------------
 ABORT_DIFF_MAX         1000
 ABORT_DIFF_ROW_COUNT   false
 AUTO_RECONCILE         false
 COMPARE_MODE           ROW_COMPARE
 COMPRESS_DATA_TRANSFER false
 CREATE_COL_LOG         false
 CREATE_RECON_SCRIPT    false
 ENABLE_ROW_COUNT       true
 EXTERNAL_SORT          false
 HASH_TYPE              DATABASE_HASH
 RETRY_DELAY_SEC        10
 RETRY_DIFF             NEVER
 RETRY_MAX              3
(0 rows affected)
 COLUMN   COMPARE MODE
 -------- ------------
 au_fname ROW_HASH
 au_id    LITERAL
 au_lname ROW_HASH
(0 rows affected)

Execute the jobs:

run job authors_job1 
	go
(1 row affected)

run job authors_job2 
	go
(1 row affected)

Monitor the progress of the jobs:

monitor job authors_job1
	go
 COMPARISON   STATUS   SUBMIT TIME         END TIME            RUN PROGRESS
         NEXT RETRY ERROR
 ------------ -------- ------------------- ------------------- --- --------
         ---------- -----
 cmp_authors1 FINISHED 2011-11-15 21:26:20 2011-11-15 21:26:26 1   100%
(0 rows affected)

monitor job authors_job2
	go
 COMPARISON   STATUS   SUBMIT TIME         END TIME            RUN PROGRESS
         NEXT RETRY ERROR
 ------------ -------- ------------------- ------------------- --- --------
         ---------- -----
 cmp_authors2 FINISHED 2011-11-15 21:26:35 2011-11-15 21:26:36 1   100%
(0 rows affected)

Monitor the individual comparisons within each job:

monitor job authors_job1 cmp_authors1
go
 COMPARISON   SUBMIT TIME         END TIME
 ------------ ------------------- -------------------
 cmp_authors1 2011-11-15 21:33:28 2011-11-15 21:33:29
(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 2011-11-15 21:33:28
         2011-11-15 21:33:29 23    23           100%
                 T    conn_pluto/dbo.authors 2011-11-15 21:33:28
         2011-11-15 21:33:29 23    23   0 0 0   100%
(0 rows affected)

monitor job authors_job2 cmp_authors2
 COMPARISON   SUBMIT TIME         END TIME
 ------------ ------------------- -------------------
 cmp_authors2 2011-11-15 21:35:46 2011-11-15 21:35:50
(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 2011-11-15 21:35:46
         2011-11-15 21:35:46 8     8            100%
                 T    conn_pluto/dbo.authors 2011-11-15 21:35:46
         2011-11-15 21:35:47 8     8    0 0 0   100%
(0 rows affected)

View a job history list:

show history authors_job1
go
 HISTORY ID SUBMIT TIME         FINISH TIME
 ---------- ------------------- -------------------
 3          2011-11-15 21:33:28 2011-11-15 21:33:29
 1          2011-11-15 21:26:19 2011-11-15 21:26:23
(0 rows affected)

To view an individual job history, specify the HISTORY_ID number for a job:

show history authors_job1 3
	go
  COMPARISON   RUN PHASE       TYPE SUMMARY
         START TIME          END TIME            COUNT READ M O I R ERROR
 ------------ --- ----------- ---- ---------------------------------
         ------------------- ------------------- ----- ---- - - - - -----
 cmp_authors1 1   COMPARE_ALL S    venus:5000/pubs2.dbo.authors
         2011-11-15 21:33:28 2011-11-15 21:33:29 23    23
                              T    pluto:5000/pubs2.dbo.authors
         2011-11-15 21:33:28 2011-11-15 21:33:29 23    23   0 0 0
(0 rows affected)
 FILE
         SERVER PATH

 -----------
         ----------------------------------------------------------------------- ---------------
 Text report
         C:\Sybase\DA-15_5\server\myserver\data\authors_job1\2011-11-15\21.33.28.795\report.txt
 XML report
         C:\Sybase\DA-15_5\server\myserver\data\authors_job1\2011-11-15\21.33.28.795\report.xml
(0 rows affected)

Note the path to the report files specified at the end of the output here.

View the job output contained in the report files:

source venus:5000/pubs2.dbo.authors
starttime 2011-11-15 21:33:00
endtime 2011-11-15 21:33:00

target pluto:5000/pubs2.dbo.authors
starttime 2011-11-15 21:33:00
endtime 2011-11-15 21:33:00
missing 0 orphaned 0 inconsistent 0

Note: A number of server configuration parameters may impact job performance. Use config to modify the default values for the configuration parameters.