Stelo Technical Documents

SQDR Plus: Db2 LUW Log Reader Stored Procedure

Last Update: 17 November 2022
Product: SQDR & SQDR Plus
Version: 5.22 and later
Article ID: SQV00PL064

Abstract

StarQuest Data Replicator (SQDR) is a replication software product that allows you to copy data incrementally from IBM Db2, Oracle, MySQL, Informix, and Microsoft SQL Server database environments.

To provide incremental replication, the original implementation of the SQDR Plus agent (running on a Windows, Linux or AIX "tier 2" system) used remote API's to accesses the database logs of the source database. This method has the advantage that no StarQuest software needs to be installed on the host system. Change data is obtained by reading the database logs on the source system using the Db2 CLI driver and the db2ReadLog API, communicating using the DRDA protocol.

We now recommend (or in some cases require) the use of the stored procedure described below instead for the following reasons:

• Recommended for improved performance
• Required when running SQDR Plus ion Windows 2016 and later and the Db2 LUW source system is configured for AUTHENTICATION = SERVER ENCRYPT.
• Required when running SQDR Plus on AIX.
• Required when connecting to Db2 LUW source configured for AUTHENTICATION=CLIENT

Solution

• Procedure
• User Privileges
• Uninstallation
• Host Runtime Environments
• Troubleshooting

Procedure

1. On the source system, copy the appropriate binary to the function subdirectory of your Db2 instance home e.g.

Windows (in an elevated command prompt):

C> cd C:\Program Files\StarQuest\sqdrplus\StarQuestSP
C> copy Windows\StarQuestSP.dll C:\Program Files\IBM\SQLLIB\function

Note that this is a different location than where Java stored procedures typically reside on Windows (ProgramData\IBM\DB2\DB2Copy1\function\).

Also verify that a recent version of the Microsoft Visual C++ Redistributables (for Visual Studio 2015-2022) is installed on the source system. The latest version can be downloaded from Microsoft Visual C++ Redistributable latest supported downloads or from StarQuest.

UNIX (as user db2inst1):

$ cd /opt/StarQuest/sqdrplus/StarQuestSP
$ cp Linux/StarQuestSP /home/db2inst1/sqllib/function
or
$ cp AIX/StarQuestSP /home/db2inst1/sqllib/function
$ chmod 755 /home/db2inst1/sqllib/function/StarQuestSP

Do not confuse this directory with the program directory /opt/ibm/db2/V11.5/function/.

2. Using SQDR Manager, edit the configuration of the Agent and add the property udbReadLogUsingSP, with the value true.
3. Save the configuration. On restart of the agent, the stored procedure will be automatically registered on the source system.
   

User Privileges

The SQDR Plus Agent must be configured to use a user with SYSADM or DBADM authority, whether you use the standard method of invoking the db2ReadLog API from the agent or when using the stored procedure.

Uninstallation

To uninstall the stored procedure support:

* Using SQDR Manager, edit the configuration of the Agent and remove the property udbReadLogUsingSP, or set it to false. The agent will restart when you save the configuration.

* On the source system, delete the binary from the function directory and drop the stored procedures:
e.g.
DROP PROCEDURE "SQDR"."SQ_GET_INITIAL_LRI"
DROP PROCEDURE "SQDR"."SQ_READLOG"

Host Runtime Environments

The stored procedure was built in the following environments, and should function in similar or later versions. However, it is possible that future operating system environments may require the installation of C runtime compatibility libraries (e.g. for gcc or xlc runtimes). Contact StarQuest support for advice on runtime environments.

• Windows - built with Visual Studio 2019
• Linux - built with gcc 4.7.2 on CentOS 6.10
• AIX - built with XL C for AIX, V12.1 on AIX 6.1

All build environments used headers and libraries from Db2 LUW 10.5. The stored procedure should function in Db2 LUW 10.5 & later.

Troubleshooting

SYMPTOM: a 444/42724 error appears in the agent diagnostic log:

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-444, SQLSTATE=42724, SQLERRMC=*_READLOG;SQL200609132350924;.../sqllib/function/StarQuestSP;*log_sp;4,

or

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-444, SQLSTATE=42724, SQLERRMC=*TIAL_LRI;SQL220419090908422;\StarQuestSP;sq_get_initial_lri;4,

or

com.ibm.db2.jcc.am.SqlSyntaxErrorException: Routine "*TIAL_LRI" (specific name "SQL221116183644746") is implemented with code in library or path "\StarQuestSP", function "sq_get_initial_lri" which cannot be accessed. Reason code: "4".. SQLCODE=-444, SQLSTATE=42724, DRIVER=4.31.10

• Verify that StarQuestSP is present in the proper function directory.
• Check that the file is readable by the Db2 instance owner and is executable (mode 755 on UNIX).
• On Windows, examine the Programs & Features control panel and verify that a recent version of the Microsoft Visual C++ Redistributables for Visual Studio 2015-2022 is installed. You can use the open source application dependencies to look for any missing dependencies.
• On UNIX, use the ldd command (LInux & Solaris) or dump -H (AIX) to check for missing dependencies.
  

 

SYMPTOM: the following error appears in the agent diagnostic log:
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=SQDRC7F.SQ_READLOG;PROCEDURE, DRIVER=4.28.11

This indicates an authority issue. Verify that the user that SQDR Plus is connecting with (e.g. db2admin) has dbadm authority in the database. Otherwise, grant it:

db2 connect to <database> user <privileged-user>
db2 grant dbadm on database to db2admin