Testing Replication

When you finish setting up the replication system, test that replication works as intended.

Connect to the primary DB2 UDB instance as a regular user, rather than as the maintenance user. Make sure the regular user also exists in the replicate database.
1. To connect to the database with CLP, use:
```
CONNECT TO dbalias USER db2_user USING db2_user_ps
```
  where dbalias is the cataloged alias of the primary database, db2_user is the primary database user, and db2_user_ps is the password.
At the primary DB2 UDB database, create a table named PTAB1 to replicate:
```
CREATE TABLE PTAB1
(IDNO INTEGER NOT NULL,
NAME VARCHAR(20),
PRIMARY KEY(IDNO))
```
Grant permissions to any new or existing object to be replicated in the primary database:
```
grant all on PTAB1 to public
```
Connect to Replication Agent through isql, and mark the PTAB1 table for replication:
```
pdb_setreptable PTAB1, mark
go
```
In Replication Agent, create a replication definition against the primary DB2 UDB database:
```
1> rs_create_repdef ptab1
2> go
Table/Procedure Name  RepDef Name    Status
--------------------- -------------- --------
<DB2TableOwner>.PTAB1 "<repdefname>" Created
(1 row affected)
```
Record the name of the replication definition for use later.
At the replicate SAP HANA database instance, create a table named PTAB1:
```
CREATE TABLE <tableowner>.PTAB1 
(IDNO INT PRIMARY KEY, 
NAME VARCHAR(20));
```
case sensitive
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 tablename, the owner of the table—<tableowner>—is the user that is signed on at the time the table is created.
Grant permissions to any new or existing object to be replicated in the replicate database so that the Replication Server maintenance user can update this table:
```
grant all privileges on <tableowner>.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.
Alter the replication definition that you created earlier to point to the correct replicate table:
```
alter replication definition <repdefname>
with replicate table named <tableowner>.ptab1
go
```
Create a subscription against the replicate connection.
To create the ptab1_sub subscription for the <repdefname> replication definition:
```
create subscription ptab1_sub 
for <repdefname>
with replicate at rds.rdb
without materialization
go
```
where <repdefname> is the replication definition that you altered in the previous step, and rds.rdb is the name of the replicate SAP HANA database connection created earlier.
For information on using the create subscription command in direct load materialization, see Replication Server Reference Manual.
On the DB2 UDB database, insert data into the primary PTAB1 table and commit:
```
INSERT INTO PTAB1
(IDNO, NAME) VALUES
(6, 'Dr. Forrester');
COMMIT;
```
On the SAP HANA database, verify that the data replicated to the replicate PTAB1 table:
```
SELECT * FROM <tableowner>.PTAB1;
```