StarQuest Technical Documents

SQDR Plus Informix Resource Utilization & Log Management

Last Update: 18 June 2019
Product: SQDR Plus
Version: 4.0 and later
Article ID: SQV00PL045

Abstract

This technical document describes the interactions of SQDR Plus and SQDR with an IBM Informix source database.

Solution

SQDR technology is designed to have a minimal impact on the Informix 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 logical 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 Informix running on any supported platform (Windows, Linux on Intel hardware, AIX, Solaris, z/Linux, POWER Linux, HP-UX, etc)..

Schemas and baseline images are acquired by the SQDR client using an ODBC driver, either the IBM Informix ODBC driver installed as part of the IBM Informix CSDK (Client Software Development Kit), or the bundled "SQDR Informix" driver installed with SQDR. Both of these drivers communicate using the Informix protocol (default port 9088). You can also use the IBM DB2 ODBC driver, which communicates using the DRDA protocol (default port 9089), but some data type limitations apply.

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 Informix JDBC driver. Change data is obtained by reading the database logical logs on the source system using the Change Data Capture API. This API uses the syscdc system database, which contains the Change Data Capture functions and system tables. The source database must be enabled for logging. The Quick Start Guide to Using SQDR Plus v4 on Windows with Informix contains instructions on verifying/enabling logging and creating the syscdc database and granting DBA authority for syscdc to the SQDR Agent User.

Users

When working with an Informix 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. This user must have DBA authority to the syscdc database in order to use the CDC API. You can use either the Informix user owner or create a new user for this purpose. This user must also have CONNECT and RESOURCE to the source database and SELECT authority for the tables to be replicated.

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 Informix 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

 

Logical Log Management

Care must be taken to prevent premature deletion of transaction logs (logical log) by configuring a sufficient amount of logical logs. If a log file is deleted prematurely (before it has been examined for changes by SQDR Plus), all incremental subscriptions will be flagged as requiring new baselines and you will see the following errors:

12:14:02 CDC: Log Reader starting to snoop at the REQUESTED position, LSN(178,6184018), page 0.
12:14:02 CDC: Log Reader: ERROR: could not get logid 178
12:14:14 CDC: Log Reader starting to snoop at the REQUESTED position, LSN(178,6184018), page 0.
12:14:14 CDC: Log Reader: ERROR: could not get logid 178
12:14:16 CDC: A capture session ended abnormally. See also D:\PROGRA~1\IBMINF~1\tmp/cdcras_1456258456. 88080423_0x0000000085085030
12:14:25 CDC: Log Reader starting to snoop at the CURRENT position, LSN (192,1e40000), page 7744.
12:14:25 CDC: Log Reader started. CDC session id 90177575. SQL session id 172.
12:14:26 CDC: A capture session ended abnormally. See also D:\PROGRA~1\IBMINF~1\tmp/cdcras_1456258466. 89653287_0x0000000085D95030
12:17:01 Checkpoint Completed: duration was 0 seconds. 12:17:01 Tue Feb 23 - loguniq 192, logpos 0x1e68018, timestamp: 0x5f3dbb7 Interval: 8068

You can use the LOGSIZE and LOGFILES configuration parameters to increase logical log size and the number of logical log files

onmode -wf LOGSIZE=1048576

Or following this procedure:

1. Add a directory to act as a storage pool:
   EXECUTE FUNCTION ADMIN ('storagepool add', 'D: \storage','0K','0','50000K','2')
   Alternative - use OAT (Open Admin Tool) to mark the existing file as expandable.
2. Expand the llogsbs using OpenAdmin Tool (adding 2gb to the existing 1gb). This creates a new file ol_informix1210_1_logdbs_p_1 in the directory D:\storage.
3. Increase the number of logs
   onparams -a -d logdbs
   (this updates the onconfig file)

If you do not specify a size with the -s option, the size of the log file is taken from the value of the LOGSIZE parameter in the ONCONFIG file when the database server disk space was initialized.

Or using OAT:

1. Space Administration > Recovery Logs.
2. Click the Admin tab.
3. In the Add Logical Log section, complete the fields:
   Dbspace Name: Select the dbspace to add the logs to.
   Size: Enter the size of the logs in KB (kilobytes).
   Number: Enter the number of logs to add.

To show the status of the new log file, enter the command onstat -l

Cleanup: After the logs have been read by SQDR, space can be reclaimed by using the ontape -a command to perform an automatic backup of logical logs. If the backup is being performed solely to reuse logical logs, you can define the parameter LTAPEDEV in the onconfig configuration file as a null device (/dev/null for UNIX or NUL for Windows).