Stelo Technical Documents

Replicating from Db2 LUW configured as AUTHENTICATION=DATA_ENCRYPT

Last Update:1 March 2024
Product: SQDR & SQDR Plus
Version: 6.x and later
Article ID: SQV00PL084

Abstract

To use SQDR with a Db2 LUW source that has been configured for AUTHENTICATION=DATA_ENCRYPT (rather than SERVER, SERVER_ENCRYPT, or CLIENT), create an agent to a placeholder database (e.g. create a local temporary database on Tier 2). After creating the agent, modify its configuration to connect to the actual source system

This avoids the connectivity error
Security mechanism not supported
ERRORCODE=-4214, SQLSTATE 28000

You must also

• Configure the Launch Agent service to use JRE 8, as supplied with Db2 LUW.
• Configure the Launch Agent service to use an older version of the Derby JDBC driver that is compatible with JRE 8.
• If Db2 11.5.9 or later is installed on Tier 2, configure the Launch Agent service to use an older version of the IBM JCC JDBC driver.
• Recommended: use the Db2 LUW Log Reader Stored Procedure on Tier 1 (source system).
• Use the IBM DB2 ODBC driver on Tier 3 (SQDR).

Prerequisites and Caveats

• You should have an understanding of how a wrapper-local.conf file can be used to override or add to the contents of wrapper.conf.
• You will need the JDBC driver (derbyclient.jar & derbytools.jar) from Derby 10.14.2.0, the last version of Derby that supported JRE 8. In our example, we have placed these files in C:\ProgramData\StarQuest\sqdrplus\db-derby-10.14.2.0-lib\lib.
• If you are using Db2 11.5.9 or later, you will need an older version of the IBM JCC JDBC driver (db2jcc4.jar & db2jcc_license_cu.jar). In our example, we have placed these files in C:\ProgramData\StarQuest\sqdrplus\jcc11.5.8.
  
• To obtain these back-level JDBC drivers, you can download the zip file downrev_jdbc_drivers.jar and extract the zip file to C:\ProgramData\StarQuest\sqdrplus.
• If you have agents to other DBMS types whose JDBC driver require JRE 11 or later, you will need to locate older versions of those JDBC drivers, or (recommended) use another Tier 2 system with a standard configuration. At the time of this writing, we are not aware of any such drivers.
• If you have streaming destinations such as Google BigQuery or destinations using Kafka, confirm that the JAR files used to access those destinations work with JRE 8. At the time of this writing, we are not aware of any issues. Kafka 4.0 (planned for sometime in 2024) will remove support for JRE 8, though Stelo will continue to use Kafka 3.x libraries for some period of time after that.
• You will not be able to create Publications to the Db2 LUW source database using the SQDR Control Center; a workaround using SQL is available.
• The Query Tool will not be able to connect to the Db2 LUW source database.

Background Information

This IBM technical document explains that

• securityMechanism=13 (DATA_ENCRYPT) uses only the DES encryption algorithm.
  
• DES is considered insecure and is not supported by recent JRE versions.
• Support for DES has been removed in JCC 11.5.9 & later.
  

So it is necessary to use both an older JRE (JRE8) and an older JCC JDBC driver (11.5.8 or earlier)

DATA_ENCRYPT has been deprecated and may be removed from future versions of Db2.

IBM recommends using SSL/TLS instead of DATA_ENCRYPT, as does Stelo. This information is provided for users who are not yet able to migrate to SSL/TLS.

Solution

Note that the default directory names for Windows are shown below. For Linux, replace C:\ProgramData\StarQuest\sqdrplus with /var/sqdrplus, C:\Program Files\StarQuest\sqdrplus with /opt/stelo/sqdrplus, etc.

Tier 1 (source system)

On the source system, copy the stored procedure to the appropriate directory (e.g. C:\Program Files\IBM\SQLLIB\function on Windows).

Tier 2 (SQDR Plus)

1. Create a text file named wrapper-local.conf in C:\ProgramData\StarQuest\sqdrplus\conf containing the following to specify alternate versions of the java command, the Derby JDBC driver, and (if needed) the IBM JCC JDBC driver.
   
   wrapper.java.command=C:\Program Files\IBM\SQLLIB/java/jdk/jre/bin/java
   
   wrapper.java.classpath.15=C:\ProgramData\StarQuest\sqdrplus\db-derby-10.14.2.0-lib\lib/derbyclient.jar
   
   wrapper.java.classpath.16=C:\ProgramData\StarQuest\sqdrplus\db-derby-10.14.2.0-lib\lib/derbytools.jar
   
   wrapper.java.classpath.3=C:\ProgramData\StarQuest\sqdrplus\jcc11.5.8/*
   
   
   
   The above labels refer to the contents of C:\Program Files\StarQuest\sqdrplus\capagent\wrapper\conf\wrapper.conf as shipped with SQDR Plus 6.34; these numbers may change in future versions.
   
   
2. Restart the SQDR Launch Agent service.
3. Examine the Launch Agent diagnostics and confirm that JRE 8 and Derby 10.14.2.0 JDBC are being used:
   
   Launch Agent Starting (Version=6.34.20240208)
   Java Version: 1.8.0_301 from IBM Corporation
   ...
   ControlDataConnection.ControlDataConnection:JDBC Driver - Apache Derby Network Client JDBC Driver 10.14.2.0 - (1828579)
   
   
4. On the Tier 2 system, create a temporary database, configured for archive logging:
   mkdir D:\DB2LOGS
   db2 create db tempdb on D: dbpath on L:
   db2 update db cfg for tempdb using LOGARCHMETH1 DISK:D:\DB2LOGS
   db2 backup db tempdb to NUL:
5. Create an agent to that database (but no need to start it).
   Use the same userID (e.g. db2admin) that you plan to use with the actual database.
6. Edit Configuration Settings:
   1. Click the Plus symbol to add property name and supply a more descriptive name e.g.
      TEMPDB@t2system
      ->
      SRCDB@t1host
      
   2. Click the Plus symbol to add the property udbReadLogUsingSP, set it to true
   3. Change sourceDBUrl e.g.
      jdbc:db2://t2system:50000/TEMPDB:driverType=4;deferPrepares=false;
      ->
      jdbc:db2://t1host:50000/SRCDB:retrieveMessagesFromServerOnGetMessage=true;deferPrepares=false;securityMechanism=13;
   4. Change sourceDbHost e.g.
      t2system
      ->
      t1host
   5. Change sourceDbName e.g.
      TEMPDB
      ->
      SRCDB
7. Save the configuration.
8. Start the agent & verify functionality.
9. Drop the temporary local database
   db2 drop db TEMPDB

 

Tier 3 (SQDR)

Use the IBM DB2 ODBC driver to connect to the source.

To use a connection string, append Authentication=DATA_ENCRYPT e.g.

Hostname=t1host;port=50000;Database=SRCDB; Protocol=TCPIP;LongDataCompat=1;Authentication=DATA_ENCRYPT

This setting can also be specified in an ODBC data source using ODBC Administrator by adding the property Authentication and setting it to Data Encrypt.

Using Log Reader DLL

If you are unable to use the recommended Db2 LUW Log Reader Stored Procedure on Tier 1 (source system), you can use the Log Reader DLL:

• Set the agent property udbReadLogUsingSP to false (or do not include it).
• Set the agent propert udbAuthentication to DATA_ENCRYPT.
• Edit wrapper-local.conf and add the location of the IBM GSK (Global Security Kit) library to the PATH as described in Tips for Db2 LUW Replication: Resolving the RC=-1 error when using Log Reader DLL.