Stelo Technical Documents

SQDR Plus Db2 LUW Resource Utilization

Last Update: 9 November 2022
Product: SQDR Plus
Version: 4.0 and later
Article ID: SQV00PL028

Abstract

This technical document describes the interactions of SQDR Plus and SQDR with a Db2 LUW (IBM Db2 for Linux, UNIX & Windows) source database.

Solution

SQDR technology is designed to have a minimal impact on the Db2 LUW operations. To accomplish real time replication, the data layout (schema) and image of the data (copy) must first be acquired. Subsequently, the changes to the database are obtained by monitoring the Db2 database logs for changes.

Because no application software is installed on the source system and all access is performed using industry-standard protocols, SQDR Plus and SQDR can work with Db2 LUW running on any supported platform (Windows, Linux on Intel hardware, AIX, Solaris, z/Linux, POWER Linux, etc). However, for best performance we recommend installing the Stelo-supplied Db2 LUW Log Reader Stored Procedure on the source system. It is currently available for Windows, Linux on Intel hardware, and AIX.

Schemas and baseline images are acquired by the SQDR client using an ODBC driver, either Stelo’s StarSQL driver (bundled in SQDR) or the IBM Db2 ODBC driver, both of which communicate using the built-in open-standards based DRDA protocol. The default port is 50000 (exception: databases created with Db2 LUW 11.5.6 & later default to port 25000). The source user specified in the Data Replicator Manager does not require any special privileges; it simply needs read access to the source tables to be replicated and to the SQDR Plus-created table SQDR.SQ_PROPERTIES.

SQDR Plus accesses the source system using the IBM Data Server Driver for JDBC  (JCC4 driver), which is included in the local copy of Db2 for LUW installed for use as staging database. It also uses the DRDA protocol. Change data is obtained by reading the database logs on the source system using either

• the Stelo-supplied Db2 LUW Log Reader Stored Procedure (recommended)
• the Db2 CLI driver (also part of the local copy of Db2 for LUW; also uses DRDA)

Both methods use the db2ReadLog API, which requires SYSADM or DBADM authority, so the user that is specified during creation of the SQDR Plus agent must have this level of authority.

The source database must be configured for archive (rather than circular) logging. Enter the command db2 get db cfg for MYDB on the host system and examine the value of LOGARCHMETH1. A value of OFF indicates that circular logging is in use; you must change the database configuration before using SQDR Plus - see Configuring Archive Logging. Many customers already have a log management process in place; if not, a Stelo-supplied exit program to manage logs is available upon request from Stelo support. The job of the exit program is to prevent premature deletion of logs (before SQDR Plus has had an opportunity to examine them for changes), and to clean up the logs when they are no longer needed.

Users

When working with a Db2 for LUW source, SQDR Plus does not create any new users on the source system. Instead, it communicates with the source system using an existing userID supplied during agent creation. As mentioned above, this user must have SYSADM or DBADM authority in order to use the db2ReadLog API. You can use either the instance owner (e.g. db2admin, db2inst1) or create a new user for this purpose.

The user specified when configuring a source in Data Replicator Manager (for use by SQDR) does not require any special privileges; it simply needs read access to the source tables to be replicated and to the SQDR Plus-created table SQDR.SQ_PROPERTIES.

Schema and Objects

During creation of an SQDR Plus Staging Agent for the Db2 LUW host, you can specify the name of the control schema (default SQDR). When the agent is started for the first time, the schema is created on the host and populated with the following control tables and stored procedures:

Control Tables:
SQ_BASELINES SQ_CATALOG SQ_CQUEUE SQ_PROPERTIES SQ_READERS SQ_WORKERS SQ4711TEMP
Stored Procedures:
TABLEINFO	used by SQDR to list tables available for subscriptions