To check replication system components, use Sybase Central or isql to log in to the servers identified for the primary and replicate sites.
In the TOKYO_DS Adaptive Server, log in to the pubs2 database and ensure that the publishers table exists:
isql -Usa -P -STOKYO_DS use pubs2 go sp_help publishers go
You will create the subscription using the “pubs2_user” login name. This user must exist in both Replication Servers.
In the TOKYO_DS Adaptive Server, create this login name:
isql -Usa -P -STOKYO_DS sp_addlogin pubs2_user, pubs2_pw, pubs2 go
In the TOKYO_DS Adaptive Server, add the “pubs2_user” login name to the pubs2 database, and grant the user select permission on the publishers table:
use pubs2 go sp_adduser pubs2_user go grant select on publishers to pubs2_user go
In the TOKYO_RS Replication Server, create the “pubs2_user” login name and grant primary subscribe permission to this login name:
isql -Usa -P -STOKYO_RS create user pubs2_user set password pubs2_pw go grant primary subscribe to pubs2_user go
In the SYDNEY_RS Replication Server, create the “pubs2_user” login name and grant create object permission to this login name:
isql -Usa -P -SSYDNEY_RS create user pubs2_user set password pubs2_pw go grant create object to pubs2_user go
In the TOKYO_RS Replication Server, create the replication definition publishers_rep for the publishers table:
isql -Ujohn -P -STOKYO_RS create replication definition publishers_rep with primary at TOKYO_DS.pubs2 with all tables named 'publishers' (pub_id char(4), pub_name varchar(40), city varchar(20), state char(2)) primary key (pub_id) searchable columns (pub_id, pub_name) replicate minimal columns go
In this example, the user “john” creates the replication definition. This user requires create object permission in TOKYO_RS.
In the TOKYO_DS Adaptive Server, mark the publishers table for replication. To mark the table for replication with the sp_setreptable system procedure, you must be the Database Owner or System Administrator for the data server. Enter the following command:
sp_setreptable publishers, 'true' go
In the SYDNEY_DS Adaptive Server, log in to the pubs2 database, and verify that the publishers table exists:
isql -Usa -P -SSYDNEY_DS use pubs2 go sp_help publishers go
When you add the replicate pubs2 database using Sybase Central or rs_init, the maintenance user is created and given replication_role. The maintenance user must have replication_role, sa_role, or alias the Database Owner to replicate truncate table.
In SYDNEY_DS, make sure the maintenance user has select, insert, delete, and update permissions on the publishers table:
grant all on publishers to SYDNEY_DS_maint go
Log in to the SYDNEY_RS Replication Server using the “pubs2_user” login name and create the subscription publishers_sub for the replication definition publishers_rep:
isql -Upubs2_user -Ppubs2_pw -SSYDNEY_RS create subscription publishers_sub for publishers_rep with replicate at SYDNEY_DS.pubs2 subscribe to truncate table go
This subscription uses atomic materialization, the default. No where clause is included, so all rows will be replicated. Execution of the truncate table command will be reproduced at the destination database.
While still logged into SYDNEY_RS, use the check subscription command to monitor the status of the subscription:
check subscription publishers_sub for publishers_rep with replicate at SYDNEY_DS.pubs2 go
You can also check if replication is occurring as expected by verifying that a row you insert is copied to the replicate table.
In the TOKYO_DS Adaptive Server, insert a row into the publishers table:
isql -Usa -P -STOKYO_DS
use pubs2
go
insert publishers
values ('9950', 'Who Donut', 'Butler', 'CA')
go
In the SYDNEY_DS Adaptive Server, verify that the row you inserted was replicated into the replicate copy of the publishers table:
isql -Usa -P -SSYDNEY_DS use pubs2 go select * from publishers go
