IBM Db2 Connection Requirements

When using Syniti Data Replication with IBM Db2, you need the following information to set up a connection.

General Requirements

  • User and password for the Db2 with appropriate privileges (see below)

  • Security Mechanism used on the Db2 server. The value specified in Syniti DR must match whatever setting is specified on the Db2 server. The options available are User Only, User and Password, Encrypted Password and Encrypted User and Password. Select the value from the drop-down list in the Security Mechanism field. If the value you choose does not match the value on the Db2 server, you will see the following error when attempting to connect.


    Note    : If you need to change this setting at any time, you may need to delete the connection and recreate it for the changed setting to take effect.

  • IP address and port to reach Db2  

  • Name of Db2 catalog (Database Server Name)

  • Package collection
    The account used to make a connection the first time requires permissions to create a package where some prepared SQL statements (DECLARE CURSOR) will be stored. The package can be created in a collection specified in the configuration parameter ”r;Package Collection.”  The account must have CREATE, BIND and GRANT privileges for objects in the collection. These privileges are needed only for the first connection.  Once the package has been created, for all users of that package the privileges are no longer required. (A package can later be dropped using the DROP PACKAGE SQL statement if needed.)

  • Host Code Page (CCSID) for the Db2 catalog: the code of the language used in Db2 (ex. 280 Italian, 037 US English, 1252 ANSI etc.) In most cases, the appropriate choice is 1208 - Unicode UTF-8. However, you should check with your Db2 administrator.

Requirements for Log-based Mirroring/Synchronization with Db2 LUW

  • Either the DBADM or the SYSADM login ID.
    To access the Db2 log, the user ID specified in the Syniti DR Connection dialog/wizard must have Database Administrator privileges.

  • Db2 Log settings as follows. See Setting Parameters for the Db2 Log for more information.
    LOGARCHMETH1 parameter should be set to LOGRETAIN or DISK
    NOTE: Be sure to complete a full offline backup of the database after making any changes to the log settings.

  • Copy file to Db2 Server.

  1. On your Windows desktop, open the Syniti DR install folder, and go to the ServerFiles/Db2UDB folder.

  2. Locate the appropriate file for your operating system and Db2 version.
    For example, the file aix53_9010_Db2udbreadlog03 is for the IBM AIX 5.3 operating system, Db2 version 9.1, whereas the file win_10010_Db2udbreadlog03.dll is for Microsoft Windows, Db2 version 10.1.
    NOTE    : If you do not find a file for your operating system or Db2 version, contact technical support.

  3. Copy the appropriate file from the ServerFiles/Db2UDB folder in your Syniti Data Replication install directory to:
    <Db2 Home Directory>/function
    on your Db2 server (the file contains stored procedures that Syniti DR calls to access the log).

  4. Rename the copied file to Db2udbreadlog03.dll (Db2udbreadlog03 for UNIX).

Setting Parameters for the Db2 Log

By default, the IBM Db2 logging type is Circular, which means the log files are written in a circular fashion. To make the log files accessible for reading, Db2 requires the logging type to be Archive.

If the database is not already in Archive mode, you need to:

  1. Change the logging type. To change the database logging type to Archive, change the parameter LOGARCHMETH1 value to either LOGRETAIN or  DISK.

  2. Perform a full offline backup (required by Db2 right after the logging type is changed.)

It is highly recommended that the above actions are carried out by the Db2 Database Administrator.

Setting Parameter LOGARCHMETH1 examples

Below are examples showing how to change the LOGARCHMETH1 parameter and to perform a backup. Again, it is highly recommend that the operations are carried out by the DBA. Refer to your database documentation for more information on the steps below.

  • Set the value of LOGARCHMETH1 to LOGRETAIN.
    CONNECT TO SAMPLE;
    UPDATE DATABASE CONFIGURATION USING LOGARCHMETH1 LOGRETAIN IMMEDIATE;
    CONNECT RESET;

  • Set the value of LOGARCHMETH1 to DISK
    CONNECT TO SAMPLE;
    UPDATE DATABASE CONFIGURATION USING LOGARCHMETH1 DISK:f:/logs/  IMMEDIATE;
    CONNECT RESET;

  • Back up the database
    Note that, depending on the size of your database, this step can take several hours.
    BACKUP DATABASE SAMPLE TO "/tmp" WITH 2 BUFFERS BUFFER 1034 PARALLELISM 1 COMPRESS  WITHOUT PROMPTING;

For more information, see IBM Db2 Version 9.7’s ‘Configuration parameters summary’ on the IBM website:

http://publib.boulder.ibm.com/infocenter/Db2luw/v9r7/index.jsp?topic=/com.ibm.Db2.luw.admin.config.doc/doc/r0005181.html.