Introduction to DB2 Table Replication method

DB2 Introduction to Table Replication Methods

version:v1.0.0

Author:xiedd

mail:xiedd@icss.com.cn

createdate:2006-08-29

Lasteditdate:2006-08-30

Description

This article describes how to copy some tables from a DB2 database to another database, as it applies to a large amount of data in a table (such as 10,000 Records). When the amount of data in the table is small, and the number of tables is not long, it can be implemented directly using the table copy function of the DB2 control center.

Applicable conditions

n DB2 replication of tables in the table, two databases can be on a single machine or on a different machine. (This assumes that two databases are located on different machines.) ）

n a single table with a large amount of data, usually above 10,000 records;

n the number of tables is more;

Terminology Definition

N Source database: Database that needs to be replicated in the table;

N Target database: a database that exists after a replicated table replication;

N DDL: A data definition language used to create and delete commands for databases and database entities.

General Description

Table replication typically uses the following several commands for DB2:

1. Export: Exports the data from the table to the data file.

2. Import: Imports data from a data file into a table.

3. LOAD: With import. The difference is that import needs to write log, and load does not need, so can greatly improve the load speed of data.

The idea of data replication in this article is: first copy the table structure from the source database to the target database through the DDL language, and then export the table in the source database to the data file (IXF Integrated Interchange Format) by Batch command, and then copy the data file to the machine where the target database resides. Loads the data file into the target database through the load command.

This process mainly uses the load command and avoids network transmission, which can greatly improve the data replication speed. The scripting approach also provides better scalability to replicate any number of tables. Also, for some complex scenarios, you can use programs to support automatic generation of these scripts.

Specific Steps

The following steps assume:

L Source database is YNDC

L Target database is HTDC

Create a batch file that generates DLL files

This step creates a file Dll.bat.

An example of its content syntax is as follows:

db2look-d <dbname> t <table1> <table2> <tableN>-e-nofed-o dll.sql

Replace <dbname> Replace the name of the source database,<tablen> with a specific table name, separated by a space between the tables. The following example exports the library table structure of the 2 tables Dim_group and Dim_pj_mach in the YNDC database.

db2look-d yndc-t dim_group dim_pj_mach-e-nofed-o dll.sql

Second, execute DLL batch file, build DLL file

This step executes Dll.bat

Copy the Dll.bat to the machine on which the source database resides, execute the Dll.bat in the DB2CMD environment, and generate Dll.sql.

Iii. Executing DLL files

This step executes Dll.sql

Before executing, change the name of the target database in Dll.sql and enter a username and password. Also, if necessary, change the table space in which the table resides.

--This CLP file was created using the Db2look version of 8.2

-Time stamp: 2006-7-10 11:28:02

--Database name: YNDC

--Database Manager version: DB2/NT versions 8.2.4

--Database code page: 1386

--Database Collation order: UNIQUE

CONNECT to htdc user db2admin using db2password;

------------------------------------------------

--The table's DDL statement "Db2admin". Bc_faccigtemplate "

------------------------------------------------

CREATE TABLE "Db2admin". Bc_faccigtemplate "(

"Cityid" VARCHAR not NULL,

"Cigid" VARCHAR not NULL,

"Cigname" VARCHAR not NULL,

"Isupload" SMALLINT)

In "Htdc" ;

--The DDL statement "Db2admin" of the index on the table. Bc_faccigtemplate "

CREATE INDEX "Db2admin". Pk__bc_faccigtemp1 "on" Db2admin "." Bc_faccigtemplate "

("Cityid" ASC,

"Cigid" ASC) CLUSTER;

--The DDL statement "Db2admin" of the primary key on the table. Bc_faccigtemplate "

ALTER TABLE "Db2admin". Bc_faccigtemplate "

ADD CONSTRAINT "SQL051107160756210" PRIMARY KEY

("Cityid",

"Cigid");