StarQuest Technical Documents

SQDR Plus: Tips for DB2 LUW Replication

Last Update: 01 February 2019
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

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:

SEVERE: [sqv][RMI TCP Connection(7)-127.0.0.1][Sep 18, 2012 2:51:50 PM]
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.

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.

Log reader initialization failed with RC=-1

Symptom: The following error appears when starting an agent for a DB2 LUW system:

SEVERE Failed in call to log library (RC=-1)

Solution: This error may occur if the AUTHENTICATION parameter of the Database Manager (DBM) of the DB2 LUW source system is configured as a value other than SERVER (most commonly SERVER_ENCRYPT) and the corresponding agent property (udbAuthentication) has not be configured to match this value.

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):.

  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.


"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.

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

After the change, OVERFLOWLOGPATH will look something like

db2 get db cfg for MYDB | grep OVERFLOWLOGPATH/home/db2inst1/archlog/db2inst1/MYDB/NODE0000/LOGSTREAM0000/

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

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%

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.

 

 


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.