Remote replication between the DB2 database and the Oracle database

I. background

DB2 joint database is a special form of distributed database management. In the federated database system, you can use an SQL command to send command requests to multiple data sources. Before copying data between DB2 and non-DB2 databases, ensure that non-DB2 data sources can be accessed by the DB2 ESE Version 8 federated database. The federated database functions required for DB2 Replication Version 8 can be provided in the currently released DB2 ESE Version 8 and DB2 Connect Enterprise Edition Version 8.

"SQL replication", also known as "DB2 replication", is one of the two types of data replication developed for DB2, which is replicated through SQL. Here, another "Q replication" in DB2 replication is implemented through the Websphere MQ message queue. During SQL replication, the Capture program reads the DB2 recovery log to obtain changes to the specified source table. This program saves the changes to the transfer table, also known as the changed data table). The Apply program reads the changes in parallel and applies them to the target transaction. See.

: Structure of SQL Replication

Global Information Integration and replication of WebSphere II provides a good platform for improving efficiency by effectively utilizing data resources through replication between different databases.

The replication between DB2 and non-DB2 databases requires WebSphere II. This article strives to provide readers with an overall concept of replication between different databases through replication instances.

Ii. Motivation

For commercial reasons, the use of replication can be summarized:

• Scattered: data is distributed to various locations;
• Integration: combine data from other locations;
• Exchange: two-way data exchange with other locations;
• Flexible application: make some changes or combinations of the methods mentioned above.

The birth of Federated database systems has used existing data resources to integrate data from different commercial database software, greatly improving data utilization. A federated database can use an SQL statement to send requests to multiple data sources distributed in different locations. The federated database system can connect local tables and remote data sources, just as data is stored locally. In addition, it can improve the data source processing capability by distributing requests to the data source, you can also supplement the SQL restrictions of the data source by processing partial distributed requests on the federated server.

The federated database has two different characteristics from other application servers:

• The federated server can be configured to receive all or some requests for the data source. The federated server distributes these requests to the data source.
• Like other application servers, a federated server uses DRDA communication protocols such as SNA and TCP/IP to communicate with the DB2 family instances. However, unlike other application servers, other protocols are used to communicate with non-DB2 family instances.

: Set the apsaradb System

WebSphere II includes two Wrapper packages. One is a relational Wrapper that replicates data such as DB2 UDB, Informix, Oracle, Microsoft SQL Server, Sybase, ODBC, and OLE DB. Another non-relational wrapper is responsible for copying non-relational data such as Flatfile, Excel, and XML.

The package defines a database responsible for communication between the local database and the remote database. The package executes many tasks, such as connecting to the data source. The package applies the standard connection API of the data source. It can also submit requests to the data source. The federated database system can operate the tables of the remote federated system. Remote tables exist in the local federated database. Client Applications can operate on these virtual tables, but they actually exist in the remote database. Each remote virtual database treats a database as a database client and only responds to requests from the database client. Therefore, you need to download clients of various remote databases for the associated database.

A consortium system requires a DB2 instance as the consortium Server, a database as the consortium database, and one or more data sources, and customer users and applications that can access databases and data sources ). To replicate data between different databases remotely, you also need to apply the data replication function of DB2.

IBM DB2 replication is known as data transmission on some platforms. It is a powerful and flexible tool for copying data from one location to another. IBM replication supports data conversion, data connection, and data filtering. Data can be moved between different platforms, or scattered to different locations or aggregated from scattered places to one place. Data can be exchanged between different systems.

IBM replication consists of four main components: Administrator, Capture, Apply, and alarm Monitor ).

The management part is mainly implemented through the graphical interface of the Replication Center. You can use the Replication Center to define a copy source and a map from the data source to the target data. It is also used to manage and monitor local and remote Capture and Apply processes. It can be seen that the graphic interface of the Replication Center supports the other parts.

: Application of the Replication Center

The Capture program running on the source data server can obtain changes in the DB2 source data table. The source data server of DB2 can be DB2 versions 6, 7, and 8 on z/OS, OS/390, iseries on OS/400 V5R2, or DB2 on Windows, version 8 in Unix. When defining a data source, Triggers is automatically generated to capture data source changes. Data to be copied can be filtered by selecting columns in the Capture process. The captured changes are first stored in the table of the database where the local source data is located and automatically deleted after the changes are applied to the target data.

When the source table is modified, DB2 writes related records to the log. These logs serve database discovery and replication. The Capture program automatically connects to the database and obtains log records. Each source table has a corresponding CD (change data) table to obtain data changes. When defining a replication data source, the Replication Center automatically generates a CD table.

For the Apply part, the captured changes are applied to the target table through the Apply program. The Apply program can run on any server and must be connected to both the source and target servers. Data can be filtered through columns and rows, and merged, for example, through views), or transmitted through SQL expressions during the Apply process. When copying data between DB2 and other related data, the nickname must be created through the joint database system. The relational package and non-relational package need to be installed on the local machine. In this example, the relational wrapper must be installed for data replication between db2 <-> ORACLE. See.

: Remote replication Relationship Diagram

The alarm monitor is used to monitor errors in the Capture and Apply sections.

(