Single-Server Deployment

SAP 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 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
SAP Adaptive Server Enterprise	venus	5000 – server
SAP Adaptive Server Enterprise	pluto	5000 – server

Start your DA server instance:
```
$SYBASE/DA-15_5/server/instance/RUN_instance_64.sh
```
Where $SYBASE is the directory in which you installed the Data Assurance Option, instance is the name of your DA server instance, and RUN_instance_64.sh is the start-up script.
Note: On Windows, the start-up script file is named RUN_instance_32.bat or RUN_instance_64.bat, where instance is your DA server instance name. On UNIX or Linux platforms, the file is named RUN_instance_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 password -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.
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 SAP Adaptive Server databases installed on venus and pluto. The user sa and null password are defaults for SAP 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 abort_diff_max to 1000 
      and set abort_diff_row_count to true 
      and set auto_reconcile to false 
      and set compare_mode to row_compare 
      and set compress_data_transfer to false
      and set create_col_log to false
      and set create_recon_script to false
      and set enable_row_count to true      
      and set external_sort to false
      and set hash_type to database_hash
      and set num_partitions to 2
      and set priority to normal
      and set retry_delay_sec to 10
      and set retry_diff to wait_and_retry
      and set retry_max to 3
      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
------------ ------ ------------- -------- ------------ --------------         
cmp_authors2 true   authors_demo2 NORMAL   ROW_COMPARE  WAIT_AND_RETRY
  
DESCRIPTION
-----------
(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   true
 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
 NUM_PARTITIONS         2
 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
------------ -------- ------------------- ------------------- --- --------
cmp_authors1 FINISHED 2011-11-15 21:26:20 2011-11-15 21:26:26 1   100% 

NEXT RETRY ERROR
---- ----- -----

monitor job authors_job2
go

COMPARISON   STATUS   SUBMIT TIME         END TIME            RUN PROGRESS
------------ -------- ------------------- ------------------- --- --------
cmp_authors1 FINISHED 2011-11-15 21:26:35 2011-11-15 21:26:36 1   100% 

NEXT RETRY ERROR
---- ----- -----

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
go

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)

To view report of an individual job history, specify the History_ID number for that job:

show report authors_job1 3
go
 FILE        SERVER PATH
 ----------- ------------------------------------------------
-----------------------------------
 Text report /Sybase/DA-15_5/server/instance/data/authors_job
/2011-11-15/21.33.28.795/report.txt
 XML report  /Sybase/DA-15_5/server/instance/data/authors_job
/2011-11-15/21.33.28.795/report.xml

This is an excerpt from the text report file:

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.