Remote replication between DB2 and non-DB2 databases

I. BACKGROUND

DB2 (DB2 certified DB2 Training) Joint database (Database training database certification) is a special form of distributed database management. In a federated database system, you can issue command requests to multiple data sources through a single SQL command. Before replicating between DB2 and non-DB2 databases, it is first necessary to ensure that non-DB2 data sources can be accessed by DB2 ESE Version 8 federated database. The federated database functionality required for DB2 Replication version 8 can be provided in the existing published DB2 ESE version 8 and DB2 Connect Enterprise Edition version 8.

SQL replication, also known as DB2 replication, is one of two types of data replication developed for DB2, which is replicated through SQL. As a simple mention here, another "Q copy" in DB2 replication is done through the Websphere MQ message queue. During SQL replication, the Capture program reads the DB2 recovery log to get changes to the specified source table. The program saves changes to the transfer table, also known as the Change data table (changed), and the Apply program reads the changes in parallel and applies to the target transaction, as shown in Figure 1.

Figure 1:SQL The structure of the replication

WebSphere II Global Information Integration replication, through the replication between different databases, the effective use of data resources, to improve efficiency provides a good platform.

WebSphere II is required for replication between DB2 and non-DB2 databases. This article strives to make the reader have a whole concept of replication between different databases through the replication instance.

Second, motive

The use of replication for a number of reasons in business can be summarized as:

Dispersion: Spread the data 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 above mentioned methods.

The birth of the Federated (Federated) database system utilizes the existing data resources, integrates the data of different commercial database software, and greatly improves the data utilization. Federated databases can use an SQL statement to make requests for multiple data sources that are distributed across different locations. Federated database systems can join local tables and remote data sources as if the data were local, and can improve the processing power of the data source by distributing requests to the data source, as well as supplementing the SQL limits of the data source by processing partial distribution requests on the federation server.

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

A federation server can be configured to receive all or receive a partial request for a data source. The federation server disperses these requests to the data source.

As with other application servers, a federation server communicates with DB2 family instances with DRDA communication protocols, such as SNA and TCP/IP. However, unlike other application servers, other protocols are used when communicating with non-DB2 family instances.

WebSphere II includes two types of wrappers (Wrapper), a relational wrapper responsible for replicating data such as DB2 UDB, Informix, Oracle, Microsoft SQL Server, Sybase, ODBC, OLE DB, and more. The other is a non-relational wrapper, responsible for Flatfile, Excel, XML and other non-relational data replication.

The wrapper defines a library that is responsible for communication between the local database and the remote database. The wrapper performs many tasks, such as: it can connect to a data source, and the wrapper applies the standard connection API for the data source. It can also submit requests to the data source. A federated database system can operate a table of remote federated systems. Remote tables are virtual in the local federated database, and client applications can manipulate these virtual tables, but they really exist in the remote database. Each remote virtual database, which treats the federated database as a database client, responds only to requests from the database client. So the federated database needs to download clients for various remote databases.

The construction of a federated system requires a DB2 instance as a federation server, a database as a federated database, one or more data sources, and customers (users and applications) who can access databases and data sources. If you want to complete replication between remote databases, you also need to apply the DB2 data replication feature.

IBM DB2 Replication (known as data propagation on some platforms) is a powerful, flexible tool for replicating DB2 and/or other database vendor data from one location to another. IBM replication supports data transformations, data connections, and filtering data. Data can be moved between different platforms, and data can be dispersed to different locations or aggregated into one place from a decentralized location. Data can be exchanged between different systems.

IBM replication consists of four main components: Management (Administrator), capture,apply, and alert monitor.

The part of management is implemented primarily through the graphical interface of the replication center. The replication center allows you to define a replication source and define a map from the data source to the target data. It is also used to manage and monitor both local and remote Capture and Apply processes. You can see from Figure 3 that the replication Center graphical interface supports several other parts.

Figure 3: Application of the Replication center

Capture programs running on the source data server can obtain changes in the DB2 source data table. The source data server for DB2 can be DB2 on z/OS, os/390 versions 6,7 and 8, or it can be iseries in os/400 v5r2, or DB2 in Windows, Unix system version 8. The corresponding trigger (Triggers) is automatically generated when the data source is defined, and can be used to capture changes in the data source. The data to be copied can be filtered by selecting columns in the Capture process. The changed information that is captured is first stored in the table in the database where the source data resides and is automatically deleted when the changes are applied to the target data.

When a change is made to the source table, DB2 writes the related records to the log. These log services are found and replicated in the database. The capture program automatically connects and gets log records through the database. Each source table has a corresponding CD (change data) table to get the data changed. When you define a replication data source, the copy center automatically generates a CD table.

For the Apply section, the captured changes are applied to the target table through the Apply program. The Apply program can run on any server and must have connectivity to both the source and destination servers used. Data can be filtered by columns, rows, and can be merged (for example, through a view) or through an SQL expression during the Apply process. When copying between DB2 and other related data, the nickname must be created through the federated database System. The relationship wrapper and the non-relational wrapper need to be installed on the local machine. For replication between db2<->oracle in this example, you need to install a relational wrapper.

The alarm monitor is used for error monitoring of the capture and apply sections.

Remote replication between DB2 and non-DB2 databases