Shulou(Shulou.com)06/01 Report--

Replication Server products include SQL replication and Q replication. Compared with SQL replication, the data transmission technology of Q replication improves the efficiency and reliability of data transmission with the help of IBM queue mechanism, which can replicate thousands of tables and hundreds of thousands of rows of data per second, and the end-to-end delay is less than 1 second (from source DB2 commit to destination DB2 commit) at a distance of thousands of miles. Change Data Capture (CDC) mainly uses log-based change data capture technology to achieve real-time data replication of key business systems without affecting the performance of business systems. The biggest advantage of CDC lies in its support for heterogeneous databases and integration with IBM products, which provides customers with excellent solutions in data migration, integration, synchronization, dynamic data warehouse and other applications. This time the sharing is about SQL replication. The following is my own test through the configuration, if you are interested, you can follow the steps to do it.

[test environment]

DB2 9.7.0.11

LINUX 6.5

Source Table: EMPLOYEE (ID, FAMILYNAME, GIVENNAME)

Target Table: TRGEMPLOYEE, which only copies the first and third columns of the source table; it does not need to be pre-defined and will be generated automatically

Database name: TESTDB. In this example, the source database and target database are the same database.

(if the source and destination are not on the same physical host, remote catalog operations are required on each physical host)

[specific process]

Step1: make sure that the TESTDB database uses archive logs, create employee tables, and inserts two rows of data

1. Create a database

$db2 "create database testdb on / home/db2inst1/testdb using codeset gbk territory CN pagesize 32k"

two。 Change the database to archive log mode

$db2 update db cfg for testdb using LOGARCHMETH1 LOGRETAIN

3. Connect to the database and check whether it is updated to LOGRETAIN (restart the database to make the parameters take effect after modification)

$db2 connect to testdb

$db2 get db cfg | grep-I LOGARCHMETH1

First log archive method (LOGARCHMETH1) = LOGRETAIN

4. Back up the database (you need to back up the database once after modifying the archive mode)

$db2 backup db testdb

5. Create a table space, specify the table space name, page size, and table space size

$db2 "CREATE LARGE TABLESPACE tbs_test

PAGESIZE 32768 MANAGED BY DATABASE

USING (FILE'/ home/db2inst1/testdb/tbs_test' 500m) "

6. Create the source table (note that the source table must have primary key or unique index, otherwise the later operation will fail)

$db2 "create table employee (id int NOT NULL, FAMILYNAME char (20), GIVENNAME char (20), PRIMARY KEY (ID))"

7. Insert data into the source table

$db2 "insert into employee values (1), (2)), (2)"

Step2: connect to the database and run the following script:

1. Create a script (including setting up server, creating control tables, creating REGISTRATION, creating predetermined sets, creating scheduled set members)

$cat > sqlrep.asnclp

The following is the content of the script: (the blue font is a variable and can be set according to the actual situation)

# identify databases involved.

# ('Control' and' Target' are usually the same)

SET SERVER CAPTURE TO DB TESTDB ID db2inst1 PASSWORD "pwd"

SET SERVER CONTROL TO DB TESTDB ID db2inst1 PASSWORD "pwd"

SET SERVER TARGET TO DB TESTDB ID db2inst1 PASSWORD "pwd"

SET RUN SCRIPT NOW STOP ON SQL ERROR ON

# Add meta data tables to hold information about

# your source and target tables.

CREATE CONTROL TABLES FOR CAPTURE SERVER

CREATE CONTROL TABLES FOR APPLY CONTROL SERVER

# Identify source table (s).

# Changed data will be 'staged' (stored) in a' CD' table

CREATE REGISTRATION (DB2INST1.EMPLOYEE) DIFFERENTIAL REFRESH STAGE CDEMPLOYEE COLS (id, GIVENNAME)

# A subscription maps a source table to a target table.

# Subscriptions are grouped in sets.

# Every subscription must be in a set, so we make a set here:

CREATE SUBSCRIPTION SET SETNAME SET00 APPLYQUAL MYQUAL1 ACTIVATE YES TIMING INTERVAL 1 START DATE "2008-01-01" TIME "01purl 00purl 00.000000"

# Each subscription is a member of a set.

# If needed, you can create the target table as we do here:

SET PROFILE TBSPROFILE FOR OBJECT TARGET TABLESPACE OPTIONS UW USING FILE "/ home/db2inst1/sqllib/TSTRG.TS" SIZE 700 PAGES

CREATE MEMBER IN SETNAME SET00 APPLYQUAL MYQUAL1 ACTIVATE YES

SOURCE EMPLOYEE

TARGET NAME TRGEMPLOYEE

DEFINITION IN TSTRG00 CREATE USING PROFILE TBSPROFILE

TYPE USERCOPY COLS ALL REGISTERED

# Now, run the file through the asnclp command

# and you are ready to replicate data.

QUIT

two。 Execute the DB2 copy command to execute the above script

$asnclp-f sqlrep.asnclp

Step3: start capture and apply

1. Start capture (it is recommended to put it in the background so that you don't have to occupy a window)

$asncap capture_server=testdb

(run in the background: $nohup asncap capture_server=testdb &)

The following is the normal output, which can be viewed through nohup.out, if you are running in the background

2017-09-08-01.48.04.703588 ASN0600I "Capture": "Initial": Program "capture 9.7.11 (Build 9.7.0.11 Level s150922, PTF IP23944)" is starting.

2017-09-08-01.48.06.817804 ASN0100I CAPTURE "ASN": "WorkerThread": The Capture version "capture 9.7.11 (Build 9.7.0.11 Level s150922, PTF IP23944), DB2 v9.7.11" program initialized successfully.

2017-09-08-01.48.06.817850 ASN0109I CAPTURE "ASN": "WorkerThread". The Capture program has successfully initialized and is capturing data changes for "1" registrations. "0" registrations are in a stopped state. "0" registrations are in an inactive state.

two。 Start apply

$asnapply control_server=testdb apply_qual=MYQUAL1

(run in the background: $nohup asnapply control_server=testdb apply_qual=MYQUAL1 &)

The following is the normal output, which can be viewed through nohup.out

2017-09-08-01.50.33.673457 ASN0600I "Apply": "Initial": Program "apply 9.7.11 (Build 9.7.0.11 Level s150922, PTF IP23944)" is starting.

2017-09-08-01.50.35.733818 ASN1045I APPLY "MYQUAL1": "Initial": The Apply version "9.7.11" program was started using database "TESTDB"

Step4: observation results (you can open a new window to view):

1. Connect to the database

$db2 connect to testdb

two。 Show all tables under this schema (because we created these tables under db2inst1 earlier)

$db2 list tables for schema db2inst1

Table/View Schema Type Creation time

CDEMPLOYEE DB2INST1 T 2017-09-08-00.50.36.243736

EMPLOYEE DB2INST1 T 2017-09-08-00.41.29.577792

TRGEMPLOYEE DB2INST1 T 2017-09-08-00.50.37.243767

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.