Testing Replication

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.

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

Add rows to the ptab1 table:

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');
commit;

In Replication Agent, create a replication definition against the primary IBM DB2 UDB database:

rs_create_repdef ptab1
go
Table/Procedure Name     RepDef Name    Status
------------------------ -------------- --------
<DB2UDBTableOwner>.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));

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 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

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

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 <repdefname> replication definition. Replication Server logs in to Replication Agent for IBM DB2 UDB, and Replication Agent issues a SELECT command to select data from the IBM DB2 UDB database, enter:

create subscription ptab1_sub
for <repdefname>
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 connects to Replication Agent for IBM DB2 UDB.
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 Replication Agent for IBM DB2 UDB that is used to select data from the IBM DB2 UDB database.

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 <repdefname>
with replicate at rds.rdb
go

If there are errors, see Troubleshooting Subscription Problems.

Connect to the IBM DB2 UDB 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');
commit;

On the Replication Server, 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 <repdefname>
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 replicate database, verify that the data is replicated to the replicate PTAB1 table:

SELECT * FROM <tableowner>.PTAB1