StarQuest: keeping data in motion

 

Stelo Technical Documents

SQDR Plus: Tips for Db2 LUW Replication

Last Update:28 February 2024
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL031

Abstract

This technical document contains tips and troubleshooting hints when using SQDR Plus for incremental data replication from a Db2 for Linux, UNIX & Windows (Db2 LUW) database.

The following technical documents provide additional information:

Contents:

Matching the AUTHENTICATION property (Log reader initialization failed with RC=-1)

Symptom: The following error will occur If the AUTHENTICATION parameter of the Database Manager (DBM) of the Db2 LUW source system is configured as a value other than SERVER:

CaptureAgentLog: Failed in call to log library (RC=-1):
SEVERE: [sqv][RMI TCP Connection(7)-127.0.0.1][Sep 18, 2012 2:51:50 PM]
CaptureAgentLog: ReplicationWorker.ReplicationWorker:
com.starquest.sqdr.capture.ReplicationException: Unexpected native error:
Failure Reading Log Records
at
com.starquest.sqdr.capture.udb.UDBLogReader.<init>(UDBLogReader.java:307)

Solution: Enter the command db2 get dbm cfg on the host system and examine the value of AUTHENTICATION. If the value is something other than SERVER (e.g. SERVER_ENCRYPT), edit the agent configuration and set the property udbAuthentication to the same value.

  1. Stop the agent.
  2. Open the Configuration Settings panel.
  3. Select the Plus icon at the top.
  4. Create a new property udbAuthentication with the same value as the source system.
  5. Save the configuration by selecting the Save icon at the top.
  6. Start the agent.

Setting the property udbAuthentication may be insufficient in some cases. If SQDR Plus is running on Windows 2016 and later, also confirm that you are using the Db2 LUW Log Reader Stored Procedure - i.e. the property udbReadLogUsingSP is set to true and the stored procedure has been copied to the source system.

Resolving the RC=-1 error when using Log Reader DLL

Symptom:

The agent will fail with the RC=-1 error if all of the following conditions are true

  • You are not using the recommended Log Reader Stored Procedure.
  • SQDR Plus is running on Windows 2016 and later.
  • The host is configured for SERVER_ENCRYPT or DATA_ENCRYPT.

Tthe following error will appear in db2diag.log on the SQDR Plus system:

2022-05-17-16.01.05.715000-420 I78057F510 LEVEL: Error
PID : 9228 TID : 6224 PROC : java.exe
INSTANCE: DB2 NODE : 000
HOSTNAME: w2019h
EDUID : 6224
FUNCTION: DB2 Common, Cryptography, cryptDynamicLoadGSKitCrypto, probe:998
MESSAGE : ECF=0x90000076=-1879048074=ECF_LIB_CANNOT_LOAD
Cannot load the specified library
DATA #1 : unsigned integer, 4 bytes
170
DATA #2 : String, 47 bytes
C:\PROGRA~1\IBM\SQLLIB\bin\icc64\gsk8ssl_64.dll

Solution:

The error indicates that the Log Reader DLL cannot locate the IBM GSK (Global Security Kit) library (installed as part of Db2).

Do the following to add the GSK library to the PATH:

  1. Confirm the install location of IBM GSK. The default location is C:\Program Files\IBM\gsk8.
  2. Examine
    C:\Program Files\StarQuest\sqdrplus\capagent\wrapper\conf\wrapper.conf and copy the line that defines PATH.
  3. Create a text file named wrapper-local.conf in C:\ProgramData\StarQuest\sqdrplus\conf\wrapper-local.conf, adding C:\Program Files\IBM\gsk8\lib64 to the PATH definition e.g.

set.PATH=C:\Program Files\IBM\SQLLIB/bin%WRAPPER_PATH_SEPARATOR%C:\Program Files\IBM\gsk8\lib64%WRAPPER_PATH_SEPARATOR%C:\Program Files\StarQuest\sqdrplus\/OracleIC%WRAPPER_PATH_SEPARATOR%C:\Program Files\StarQuest\sqdrplus\/staradmin%WRAPPER_PATH_SEPARATOR%%PATH%

Note that the contents of the PATH statement may change in future versions of SQDR Plus.

  1. Use the Services control panel to restart the SQDR Plus Launch Agent service.

Logreader problem after updating source from Db2 LUW 9.7 to 10.5

Symptom: After updating a Db2 for LUW source from 9.7 to 10.5, the Staging Agent failed with the error

Failed in call to log library (RC=-2032):Log reader initialization failed with sqlcode=-2032.

The failure is due a change of size for LSN (Log Sequence Number) between Db2 9.7 and 10.5.

Solution:

  1. Stop the SQDRSVC service. It important that there are no StarSQL connections to the source system.
  2. Use StarAdmin to rebind StarSQL packages in the SQDR collection on the source system. It may be necessary to use a tool such the Query tool included in SQDR Control Center or the Db2 command window to DROP PACKAGE SQDR.SYSCAT.
  3. Use the SQDR Control Center Query tool, Db2 command window, or other tool to clear the SQ_READERS table in the local Db2 for LUW staging database:

DB2> CONNECT to SQDRP0 user SQDR
DB2> DELETE FROM SQDR.SQ_READERS

Log reader initialization failed with sqlcode=-2018

Symptom: When connecting to a Db2 LUW source system, the following error was encountered:

Failed in call to log library (RC=-2018):Log reader initialization failed with sqlcode=-2018.
ReplicationWorker.ReplicationWorker:
com.starquest.sqdr.capture.ReplicationException: Unexpected native error: Failure Reading Log Records

Solution: This error can occur if the Agent user (i.e. the user that SQDR Plus is using to connect to the source system) does not have sufficient authority to use the DB2readLog API. This user needs either SYSADM or DBADM authority on the source system.

  1. Examine the configuration and locate the sourceUserId property.
  2. On the source system, determine the name of the Windows or UNIX group that Db2 uses for SYSADM_GROUP:

C> db2 get dbm cfg | findstr SYSADM_GROUP
SYSADM group name (SYSADM_GROUP) = DB2ADM

  1. If the value of SYSADM_GROUP is blank, create the OS group, set the SYSADM_GROUP value and restart Db2:

    Create an OS group db2adm
    db2 update dbm cfg using SYSADM_GROUP db2adm
    db2stop
    db2start
  2. Add the sourceUserId to the OS group.
  3. Restart SQDR Plus.

Log reader initialization failed with sqlcode=-2652

Symptom: When connecting to a Db2 LUW source system, the following error was encountered:

SEVERE Failed in call to log library (RC=-2652):Log reader failed with sqlcode=-2652.
WARNING ReplicationWorker.run:
com.starquest.sqdr.capture.ReplicationException: Unexpected native error: Failure Reading Log Records

In addition, the following error appeared in db2diag.log on the source system:

"No memory available in 'Database Heap'"
DIA8302C No memory available in the database heap.

Solution: Error -2652 indicates Insufficient memory to run the db2ReadLog or db2ReadLogNoConn API. This condition was encountered when the DBHEAP database configuration parameter (on the source system) was configured for a static value; changing it to AUTOMATIC resolved the problem.

"Truncated before image" warning

Symptom: A customer was receiving an intermittent but persistent warning from a Db2 LUW agent:

CaptureAgentLog: Truncated before image record for : MYSCHEMA.MYTABLE

In addition, the replication process was failing to update the data in some columns of the affected table.

Solution: The affected table had been altered prior to creating the SQDR subscription to add additional columns. Performing a REORG of the table resolved the problem.

Note: If the subscription included Criteria, then the agent might terminate instead of issuing the warning. If you suspect that you are encountering this situation, remove the criteria to see if the warning starts to appear.

Configuring Archive Logging

SQDR Plus requires that a source Db2 LUW be configured for archive logging rather than circular logging.

Enter a command like the following to examine the current the current logging method; a value of OFF indicates circular logging.

Windows: db2 get db cfg for MYDB | findstr LOGARCHMETH1
UNIX: db2 get db cfg for MYDB | grep LOGARCHMETH1

To enable archive logging to DISK:

  1. Make sure there are no active connections to the database.
  2. Create a directory for archive logs (typically on a different disk than the location of Db2 active logs) e.g.
    D:> mkdir \DB2LOGS
  3. Change the value of LOGARCHMETH1 and perform a backup of the database

db2 update db cfg for mydb using LOGARCHMETH1 DISK:D:\DB2LOGS
db2 backup db mydb to NUL:
or
db2 backup db mydb to C:\TEMP

For performance reasons, we recommend using OVERFLOWLOGPATH

To prevent archive logs from accumulating over time, establish some method of log management. A simple cleanup script is described below.

Other options for LOGARCHMETH1 include TAPE:, DB2REMOTE (remote storage), VENDOR (a vendor-supplied routine) or USEREXIT (user exit program).

Performance tip: Use OVERFLOWLOGPATH

Symptom: On a busy system, where the logs are being updated frequently and are written to the archive log location before the SQDR Plus Agent has finished scanning them, you may experience a lag in replication, and the message ADM1843I Started retrieve for log file S0nnnnnn.LOG appears frequently in db2diag.log.

Solution: If the source database is configured to write archived logs to a disk location (LOGARCHMETH1 of the database configuration is set to a value like DISK:<path to archive logs>, we recommend using OVERFLOWLOGPATH to avoid the retrieval of archived log files. Note that LOGARCHCOMPR1 must be OFF.

  • Examine the values of LOGARCHMETH1 and LOGARCHCOMPR1:

db2 get db cfg for MYDB | grep LOGARCH
Windows: db2 get db cfg for MYDB | findstr "LOGARCH"
First log archive method (LOGARCHMETH1) = /home/db2inst1/archlog
Archive compression for logarchmeth1 (LOGARCHCOMPR1) = OFF

  • Run the following command for instance db2inst1 and database MYDB:

db2 update database config for MYDB using OVERFLOWLOGPATH /home/db2inst1/archlog/db2inst1/MYDB

  • Restart Db2 for the change to take effect.

After the change, OVERFLOWLOGPATH will look something like

db2 get db cfg for MYDB | grep OVERFLOWLOGPATH
on Windows: db2 get db cfg for MYDB | findstr "OVERFLOWLOGPATH"

/home/db2inst1/archlog/db2inst1/MYDB/NODE0000/LOGSTREAM0000/

You can also examine and modify the OVERFLOWLOGPATH property using Data Studio.

The above syntax and typical values are from a UNIX system; they will be slightly different for Windows - e.g. use findstr "LOGARCH" instead of grep LOGARCH.

Detailed Explanation: If the archive log paths (LOGARCHMETH1, LOGARCHMETH2, or both) are configured to use DISK method, then the overflow log path can be configured to point to the exact location of the archive log path. In this configuration, ROLLFORWARD avoids copying log files from the archive log path to the retrieve location. When the overflow log path is configured in this way, Db2 reads the log files directly from the overflow log path (which is the archive log path), and saves the cost of unnecessary I/O copy operations. When you configure the overflow log path in this way (to point to the archive log path), it is configured to point to the chain subdir subpath of the archive log path.

For more information, see Using an overfow log path in the Db2 documentation.

Performance affected by large number of archived logs

Symptom: A customer was experiencing intermittent poor performance for incremental replication from a Db2 for LUW source system. An investigation revealed that Db2 was configured for archive logging:

db2 update db cfg for DBNAME using LOGARCHMETH1 DISK:/somedisk/db2archlogs/DBNAME

and though backups were performed on a regular basis, no maintenance was being performed on the archive directory. The archive directory contained approximately 40,000 log files, dating back several years.

When the Db2 system was not busy, only the active log files were being examined by SQDR Plus, and performance was satisfactory. However, when the Db2 system was very active and the logs needed by the db2readlog API used by SQDR Plus had already been archived, performance dropped and lagtimes of over 30 minutes were experienced.

Solution: Use the following command to prune the archived log files; this should be run after a backup, as only logs that are no longer needed for recovery will be deleted:

db2 connect to DBNAME
db2 prune history <timestamp> and delete

For example, this command will delete all unneeded archive logs older than March 1, 2017:

db2 prune history 20170301000000 and delete

Here is an example of a batch file that does not need to be run from a Db2 command window; it will delete archive logs that are older than today's date:

set DB2DIR=C:\Program Files\IBM\SQLLIB
set DB2BIN=%DB2DIR%\bin
set DB2=%DB2BIN%\db2
set DB2CLP=DB20FADE
set DB2INSTANCE=DB2

for /f "tokens=1-5 delims=/ " %%d in ("%date%") do set CURRENTDATE=%%g%%e%%f

set DB=DBNAME
"%DB2%" connect to %DB% user db2admin
"%DB2%" prune history %CURRENTDATE% with force option and delete
"%DB2%" disconnect %DB%

Managing Db2 LUW Logs on the source database

SQDR Plus extracts incremental changes from the Db2 logs on the source database. If a log file is deleted prematurely (before it has been examined by SQDR Plus), all incremental subscriptions will be flagged as requiring new baselines.

To determine which log files can be removed without affecting SQDR Plus incremental replications, use the db2flsn (Find Log Sequence Number) command on the source Db2 LUW system to display the LSN range for each log file. You may delete logs that contain LSN ranges that are less than the current position of SQDR Plus.

For example

Issue the following command in a Db2 Admin command window on the source Db2 LUW system:

C>db2flsn -db MYDBMS -lsnrange -startlog 0 -endlog 128

produces this output:

S0000000.LOG: has LSN range 0000000027FBC8BA to 0000000027FC775E
S0000001.LOG: has LSN range 0000000027FC775F to 0000000027FD25BE
S0000002.LOG: has LSN range 0000000027FD25BF to 0000000027FDD407
etc.

Then compare the list to the current LSN being processed by the SQDR Capture Agent. In the SQDR Control Center select the agent of interest and display the Summary panel. The last 16 digits identify the current LSN.

 

An alternative is to delete archived logs that are older than a specified amount of time, assuming that SQDR is running and has processed these logs. For example, the following UNIX shell script can be run shortly before midnight to delete logs up to midnight from the previous night.

  1. Create a shell script called cleanup in the bin directory of the instance owner containing the following contents:

DB=MYDB
CURRENTDATE=$(date +%Y%m%d)
# echo $CURRENTDATE
. /home/db2inst1/sqllib/db2profile
db2 connect to $DB
db2 prune history $CURRENTDATE with force option and delete
db2 disconnect $DB

  1. To create a crontab entry to invoke cleanup as user db2inst1 nightly at 11:45pm, as user db2inst1, run crontab -e and input:

45 23 * * * /home/db2inst1/bin/cleanup

This creates a file /var/spool/cron/crontabs/db2inst1. See the crontab log in /var/adm/cron/log for results; results are also mailed to the db2inst1 user.

Here is a Windows batch file that can be invoked from the Windows scheduler:

set DB2DIR=C:\Program Files\IBM\SQLLIB
set DB2=%DB2DIR%\bin\db2
set DB2CLP=DB20FADE
set DB2INSTANCE=DB2

set DB=MYDB
for /f "tokens=1-5 delims=/ " %%d in ("%date%") do set CURRENTDATE=%%g%%e%%f
"%DB2%" connect to %DB%
"%DB2%" prune history %CURRENTDATE% with force option and delete
"%DB2%" disconnect %DB%

Db2 11.1fp3 - accumulation of old log files

Issue: In Db2 11.1fp3 (11.1.3.3 and 11.1.3.3.ifix001) old log files might not be removed from the active log path after they have been archived. This may eventually cause exhaustion of the active log path filesystem.

Solution: Update to Db2 11.1fp4 or later (APAR IT25556) (recommended) or set the DB2_USE_BUFFERED_READ_FOR_ACTIVE_LOG registry variable to NO as documented here:

Db2 active log path may become exhausted/full due to archived log files not being removed in v11.1.3.3, when db2ReadLog in use

Upgrading to a major new release - avoiding new baselines (unable to read old archive logs error)

Issue: After upgrading Db2 LUW to a major new release (e.g. from 10.5 to 11.1), incremental replication is failing, and the following message appears in db2diag.log:

FUNCTION: DB2 UDB, data protection services, sqlpgReadLogAPI, probe:6677
MESSAGE : ZRC=0x071000D4=118489300=SQLP_EXT_INCOMPAT_VERS
"Incompatible log version in extent header."

and the following message appears in Diagnostics:

Failed in call to log library (RC=-1263):Log reader failed with sqlcode=-1263.
com.starquest.sqdr.capture.ReplicationException: Unexpected native error: Failure Reading Log Records.

(SQLCODE 1263 indicates The archive log file logfile is not a valid log file)

Db2 is trying to access older log records from archive logs, but log records are not compatible between 10.5 and 11.1 (or any other major releases). Unfortunately there is no way to upgrade log files. The first time the database is activated after the upgrade, Db2 archives any of the log files that were active at the time of shutdown, and then starts new logs.

Solution: Ideally you should ensure that SQDR Plus is all caught up before stopping Db2 and upgrading the database. Failing that, if SQDR Plus is trying to access the older logs, start the agent with Startup Mode set to Warm Start to reset the agent's log position. Unless you are confident that no changes were made to source tables after stopping the agent prior to upgrade; inew baselines are recommended.

Increasing internal memory buffer to avoid db2shred thread waits

Note: StarQuest has not verified this suggestion from IBM. IBM recommends consulting with Db2 Support before implementing the suggested registry variable change, and also suggests that proper tuning of the Log File Size, Log Buffer Size, and log archive methods should be completed first.

Symptom: Slow performance, burst-like log read behavior, and db2diag.log messages related to db2shred threads as described in the IBM technical document.

Solution: Increase the memory buffer with the registry variable DB2_DPS_RLOG_SHR_MEMBYTES.

See the IBM technical document DB2 readlog API Slow Performance.

Replicating LOB columns using theIBM DB2 ODBC driver

Symptom:

When replicating tables with LOB columns from Db2 LUW to Db2 LUW using the IBM DB2 ODBC driver, the following error occurred

Error applying change data for subscription MYSUB: SQL attempted SQLBindParameter...

The following error was also encountered:

ODBC message: SQLSTATE HY090, native error 0, [Microsoft][ODBC Driver Manager] Invalid string or buffer length

Solution:

LongDataCompat=1 is required for correct replication of LOB data types. When replicating larger LOB columns, set LOBMaxColumnSize and the SQDR service property Inderterminant Column Size to the maximum value to be used.

After making the change restart the service and recreate the problematic subscriptions that are using BLOB and CLOB data types.

LongDataCompat informs the IBM DB2 ODBC driver to report CLOB and BLOB as ODBC types -1 & -4, respectively (instead of 99, 98) and LOBMaxColumnSize sets the maximum value to be used.

These parameters can be set in the connection string or in the ODBC data source:

 

Publish or Subscribe fails with SQLCODE=-911, SQLSTATE=40001

Publishing or subscribing to certain tables fail with the error

Failed to add subscription: DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=68, DRIVER=3.69.24 SQLSTATE=40001

This error indicates the current transaction has been rolled back due to a lock timeout. These tables are typically very busy.

When a publication or subscription is created, the agent will attempt to alter the table to capture changes e.g.
ALTER TABLE schema.table DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS

This SQL will fail if the table is locked. For a publication, the agent will retry every 10 minutes until success or the table is removed from the Publications panel.

It may be necessary to perform this operation on the affected tables during a quiet period.

 

DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.