Testing Replication

Connect to the primary Adaptive Server instance as a non-maintenance user with the ability to create tables.

At the primary Adaptive Server database, create a table named ptab1 to replicate:

use pubs2
go
create table ptab1
(idno int not null,
name varchar(20) null)
go
sp_primarykey ptab1, idno
go

Note: By default, this DDL creates a table with the owner dbo.

Grant permissions to any new or existing object to be replicated in the primary database:

grant all on ptab1 to public
go

Mark the ptab1 table for replication:

sp_setreptable ptab1, 'true'
go
The replication status for 'ptab1' is set to true, owner_off.

Add rows to the table, ptab1:

insert into ptab1 values (1, "Burt Gringlesby")
insert into ptab1 values (2, "Douglas Wong")
insert into ptab1 values (3, "Tom Servo")
insert into ptab1 values (4, "Innes del Castillo")
insert into ptab1 values (5, "Akiko Yokomoto")
go

At the replicate SAP HANA database instance, create a table named PTAB1:

CREATE TABLE PTAB1 
(IDNO INT PRIMARY KEY, 
NAME VARCHAR(20))

Note: SAP HANA database is not case-sensitive and uses uppercase characters if lowercase characters are provided.

If an owner is not specified with the table name, the owner of the table—<tableowner>—is the user who is logged in when the table is created.

Grant permissions to any new or existing object to be replicated in the replicate SAP HANA database so that the Replication Server maintenance user can update this table:

grant all privileges on PTAB1 to public

Log in to Replication Server:

isql –Usa –Psa_pass –SSAMPLE_RS

If you have not set up the sample Replication Server instance, enter your Replication Server instance name in place of SAMPLE_RS.

Create a replication definition against the primary Adaptive Server database.

To create the ptab1_repdef replication definition for the ptab1 table on the primary database, enter:

create replication definition ptab1_repdef
with primary at pds.pdb
with primary table named ptab1
with replicate table named <tableowner>.ptab1
(idno integer,
"name" varchar(20))
primary key (idno)
go

where the replicate table on SAP HANA database—ptab1— is owned by <tableowner>, and sunak1502i.pubs2 is the name of the primary connection created earlier.

Create a subscription against the replicate connection to materialize the PTAB1 table on the SAP HANA database.

Create the ptab1_sub subscription with direct_load option for the ptab1_repdef replication definition:

create subscription ptab1_sub 
for ptab1_repdef
with replicate at rds.rdb
without holdlock
direct_load
user puser password ppwd
go

where:

rds.rdb is the replicate SAP HANA database connection created earlier.
puser is the user who selects the data from the table in the primary Adaptive Server database.
ppwd is the password of the puser. You must enter a password.

Note: The interfaces file used by the replicate Replication Server must contain an entry for the primary Adaptive Server where the initial data resides.

To create the subscriptions at the replicate Replication Server using the no-materilization method, use the create subscription command with the without materialization clause.

For information about using the create subscription command in direct_load materialization, see Replication Server Reference Manual.

Check the subscription status to ensure there are no errors:

check subscription ptab1_sub
for ptab1_repdef
with replicate at rds.rdb
go

If there are errors, see Troubleshooting Subscription Problems.

Connect to the primary Adaptive Server database as a non-maintenance user with the ability to insert rows into the table:

insert into ptab1 values (10, "Michel DeFrance")
insert into ptab1 values (11, "Dirk Stringer")
go

Check the status of the subscription created with the direct_load option by using the check subscription command. When the subscription is in the VALID state, materialization is complete:

check subscription ptab1_sub
for ptab1_repdef
with replicate at rds.rdb
go
Subscription ptab1_sub is VALID at the replicate.

After the status is VALID at the primary and replicate database, on the SAP HANA database, verify that the data is replicated to the replicate PTAB1 table:

SELECT * FROM PTAB1