Stelo Technical Documents

Replicating from Db2 LUW configured as AUTHENTICATION=CLIENT

Last Update:14 November 2022
Product: SQDR & SQDR Plus
Version: 5.22 and later
Article ID: SQV00PL075

Abstract

To use SQDR with a Db2 LUW source that has been configured for AUTHENTICATION=CLIENT (rather than SERVER or SERVER_ENCRYPT), 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 use the Db2 LUW Log Reader Stored Procedure on Tier 1 (source system) and the IBM DB2 ODBC driver on Tier 3 (SQDR).

Solution

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. 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:
2. 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.
3. 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=4;
   4. Change sourceDbHost e.g.
      t2system
      ->
      t1host
   5. Change sourceDbName e.g.
      TEMPDB
      ->
      SRCDB
4. Save the configuration.
5. Start the agent & verify functionality.
6. Drop the temporary local database
   db2 drop db TEMPDB

 

Tier 3 (SQDR)

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

Use ODBC Administrator to create an ODBC DSN based on a database alias. When creating the alias, choose the Security options, select the checkbox Specify the security options (Optional) and choose Client authentication (CLIENT)