Creating tables at the target Db2 database

When you add a table to a replication set, the replication process automatically creates an identical table at the target database if it does not already exist.

The tables are created with the same index that exists on the source table and in the same table space. However, the table space must already exist at the target database. Replication does not create it.

Replication does not create any views, stored procedures, triggers, materialized query tables, stored procedures, triggers, or other objects that exist on the source database, including table spaces and authorizations. These objects must be created prior to adding the table to replicate.

A common method for creating these objects when you deploy a new target Db2 site is to run the db2look command at the source system, which can a new target Db2 site is to run the db2look command at the source system, which can generate the SQL statements that are then used to recreate all database objects, not just the tables. This is the preferred method when creating a new target system from scratch when you want the target database to include all object definitions that exist in the source database, and rely on replication to load the data into the empty tables.

Using db2look, the following commands generate the DDL statements for recreating objects from the database SOURCEDB and send the SQL statements to the file db2look.sql:

su –db2inst1   --log in as Db2 administrator
db2look -e -d SOURCEDB -u db2inst1 -o db2look.sql
-- Creating DDL for table(s)
-- Output is sent to file: db2look.sql
-- Binding package automatically ...
-- Bind is successful
-- Binding package automatically ...
-- Bind is successful

You can then transfer the db2look.sql file that was produced on the source system and execute it on the target system TARGETDB:

sftp dallas
db2inst1db2inst1@dallas’s password:
sftp>put db2look.sql
Uploading db2look.sql to /home/db2inst1/db2look.sql
sftp> quit
ssh dallas db2inst1
db2inst1@dallas’s password:
db2 connect to TARGETDB
db2 -tvf db2look.sql
exit