Version 25.3.9414

• Prerequisites
  • Set Up DB2 for i for Enhanced CDC
    • Create the Journal Receiver and Journal and Enable Journaling on Tables
    • Set Required User Permissions
• DB2 for i コネクタを追加
• DB2 for i への認証
• Complete Your Connection

CData Sync アプリケーションからDB2 for i コネクタを使用して、DB2 for i からデータを取得してサポートされている任意の同期先に移動できます。これを行うには、コネクタを追加し、コネクタへの認証を行い、接続を完了する必要があります。

Prerequisites

Before you add and set up the DB2 for i source connector, you need to set up the DB2 for i database for enhanced change data capture (CDC), as explained in the following sections.

Limitations:

The following capabilities are not supported in Sync:

• remote journals or failover functionality

• large objects such as CLOB, XML, TEXT, BLOB

Set Up DB2 for i for Enhanced CDC

Journaling records database changes in journal receivers, which the change data capture (CDC) process uses to capture and replicate those changes to other systems in real-time or near real-time synchronization.

A journal is a database object that serves as a tracking mechanism, capturing metadata about changes made to specific database objects. Each journal is linked to one or more journal receivers, which physically store the detailed change records, including before-and-after images of the data as well as metadata and transaction details.

Because CDC depends on journaling to supply detailed change records, creating a journal and journal receiver and setting user permissions are the first steps in enabling CDC for DB2 for i, as explained in the next sections.

Create the Journal Receiver and Journal and Enable Journaling on Tables

Run the following command-line (CL) commands to create the journal receiver, journal, and enable journaling on tables:

   -- 1. Create a journal receiver.
CRTJRNRCV <RECEIVER_LIBRARY>/<RECEIVER_NAME>

   -- 2. Create a journal.
CRTJRN <JOURNAL_LIBRARY>/<JOURNAL_NAME> <RECEIVER_LIBRARY>/<RECEIVER_NAME>

   -- 3. Enable journaling on a file (table) by using the STRJRNPF command. As a best practice, capture both before-and-after images of the records to support features like auditing and recovery.
STRJRNPF <FILE_LIBRARY>/<FILE_NAME> <JOURNAL_LIBRARY>/<JOURNAL_NAME> IMAGES(*BOTH)

   -- If you already started journaling and need to change the image settings, submit the CHGJRNOBJ command.
CHGJRNOBJ OBJ((<FILE_LIBRARY>/<FILE_NAME> *FILE)) ATR(*IMAGES) IMAGES(*BOTH)

   -- To stop journaling for a specific physical file (table), use the ENDJRNPF command.
ENDJRNPF FILE(<FILE_LIBRARY>/<FILE_NAME>) JRN(<JOURNAL_LIBRARY>/<JOURNAL_NAME>)

To verify that journaling is enabled for a table, or for details about the journals and their journal receivers, execute the following queries:

SELECT * FROM QSYS2.JOURNAL_RECEIVER_INFO WHERE JOURNAL_RECEIVER_LIBRARY = <RECEIVER_LIBRARY>;
SELECT * FROM QSYS2.JOURNAL_INFO WHERE JOURNAL_LIBRARY = '<JOURNAL_LIBRARY>';
SELECT * FROM QSYS2.JOURNALED_OBJECTS WHERE JOURNAL_LIBRARY = '<JOURNAL_LIBRARY>';

The following example creates the RCV01 journal receiver and the JRN1 journal. Then, it adds the PRODUCTS table for journaling by using QSYS2.QCMDEXC to call any CL command through the JDBC tool. All of the objects are created in the TEST2 schema/library.

   -- 1.Create a NEW journal receiver.
CALL QSYS2.QCMDEXC('CRTJRNRCV TEST2/RCV01');

   -- 2. Create a journal.
CALL QSYS2.QCMDEXC('CRTJRN TEST2/JRN1 TEST2/RCV01');

   -- 3. Start journaling tables/files.
CALL QSYS2.QCMDEXC('STRJRNPF FILE(TEST2/PRODUCTS) JRN(TEST2/JRN1) IMAGES(*BOTH)');

   -- 4. Stop journaling tables/files.
CALL QSYS2.QCMDEXC('ENDJRNPF FILE(TEST2/PRODUCTS) JRN(TEST2/JRN1)'); 

VALUES CURRENT_SCHEMA; -- TEST2
SELECT * FROM QSYS2.JOURNAL_RECEIVER_INFO WHERE JOURNAL_RECEIVER_LIBRARY = CURRENT_SCHEMA;
SELECT * FROM QSYS2.JOURNAL_INFO WHERE JOURNAL_LIBRARY = CURRENT_SCHEMA;
SELECT * FROM QSYS2.JOURNALED_OBJECTS WHERE JOURNAL_LIBRARY = CURRENT_SCHEMA;

Set Required User Permissions

To verify all permissions for the user to which you are connected, submit the following query:

SELECT * FROM QSYS2.OBJECT_PRIVILEGES WHERE AUTHORIZATION_NAME = '<CDC_USER>'

If the user lacks privileges to the journal, journal receiver, or the schema (library) from which you want to capture changes, run the following CL commands:

GRTOBJAUT OBJ(<JOURNAL_LIBRARY>) OBJTYPE(*LIB) USER(<CDC_USER>) AUT(*EXECUTE)
GRTOBJAUT OBJ(<JOURNAL_LIBRARY>/*ALL) OBJTYPE(*JRNRCV) USER(<CDC_USER>) AUT(*USE)
GRTOBJAUT OBJ(<JOURNAL_LIBRARY>/<JOURNAL_NAME>) OBJTYPE(*JRN) USER(<CDC_USER>) AUT(*USE *OBJEXIST)

GRTOBJAUT OBJ(<RECEIVER_LIBRARY>) OBJTYPE(*LIB) USER(<CDC_USER>) AUT(*EXECUTE)
GRTOBJAUT OBJ(<RECEIVER_LIBRARY>/*ALL) OBJTYPE(*FILE) USER(<CDC_USER>) AUT(*USE)

Note: To execute the commands through a Java Database Connectivity (JDBC) tool, use this command:

CALL QSYS2.QCMDEXC('CL COMMAND');

After you download the drivers, restart Sync.

DB2 for i コネクタを追加

Sync でDB2 for i のデータを使用できるようにするには、まず以下の手順でコネクタを追加する必要があります。

1. Sync のダッシュボードから接続ページを開きます。

2. 接続を追加をクリックしてコネクタを選択ページを開きます。

3. データソースタブをクリックしてDB2 for i (Native) 行に移動します。

4. 行末にある接続を設定アイコンをクリックして、新しい接続ページを開きます。接続を設定アイコンが利用できない場合は、コネクタをダウンロードアイコンをクリックしてDB2 for i (Native) コネクタをインストールします。新規コネクタのインストールについて詳しくは、接続を参照してください。

DB2 for i への認証

コネクタを追加したら、必須プロパティを設定する必要があります。

• Connection Name: Enter a connection name of your choice.

• Server: Enter the address or host name of the DB2 for i (Native) server. The default server is localhost.

• Database: Enter the name of your DB2 for i database.

• Port: Enter the port number for your server. The default port is 446.

• User: Enter the username that you use to authenticate to the DB2 for i (Native) database.

• Password: Enter the password that you use to authenticate to the DB2 for i (Native) database.

• Schema: Enter the schema that you want to use for DB2 for i.

Complete Your Connection

To complete your connection:

1. 高度な設定タブで接続の高度な設定を定義します。（ただし、ほとんどの場合これらの設定は必要ありません。）

2. 作成およびテストをクリックして接続を作成します。