> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sync.cdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# DB2 for i (Native)

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 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 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:

```sql theme={null}
   -- 1. Create a journal receiver.
CRTJRNRCV <ReceiverLibrary>/<ReceiverName>

   -- 2. Create a journal.
CRTJRN <JournalLibrary>/<JournalName> <ReceiverLibrary>/<ReceiverName>

   -- 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 <FileLibrary>/<FileName> <JournalLibrary>/<JournalName> IMAGES(*BOTH)

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

   -- To stop journaling for a specific physical file (table), use the ENDJRNPF command.
ENDJRNPF FILE(<FileLibrary>/<FileName>) JRN(<JournalLibrary>/<JournalName>)
```

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

```sql theme={null}
SELECT * FROM QSYS2.JOURNAL_RECEIVER_INFO WHERE JOURNAL_RECEIVER_LIBRARY = <ReceiverLibrary>;
SELECT * FROM QSYS2.JOURNAL_INFO WHERE JOURNAL_LIBRARY = '<JournalLibrary>';
SELECT * FROM QSYS2.JOURNALED_OBJECTS WHERE JOURNAL_LIBRARY = '<JournalLibrary>';
```

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.

```sql theme={null}
   -- 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;
```

When you [create a CDC job](#create-a-change-data-capture-cdc-job-with-journal-and-schema-selections) in Sync, you select the journal and schema to determine which tables are available for capture.

### Set Required User Permissions

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

```sql theme={null}
SELECT * FROM QSYS2.OBJECT_PRIVILEGES WHERE AUTHORIZATION_NAME = '<CDCUser>'
```

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:

```sql theme={null}
GRTOBJAUT OBJ(<JournalLibrary>) OBJTYPE(*LIB) USER(<CDCUser>) AUT(*EXECUTE)
GRTOBJAUT OBJ(<JournalLibrary>/*ALL) OBJTYPE(*JRNRCV) USER(<CDCUser>) AUT(*USE)
GRTOBJAUT OBJ(<JournalLibrary>/<JournalName>) OBJTYPE(*JRN) USER(<CDCUser>) AUT(*USE *OBJEXIST)

GRTOBJAUT OBJ(<ReceiverLibrary>) OBJTYPE(*LIB) USER(<CDCUser>) AUT(*EXECUTE)
GRTOBJAUT OBJ(<ReceiverLibrary>/*ALL) OBJTYPE(*FILE) USER(<CDCUser>) AUT(*USE)
```

> [!NOTE]
>
> To execute the commands through a Java Database Connectivity (JDBC) tool, use this command:
> <pre>`CALL QSYS2.QCMDEXC('CL COMMAND');`</pre>

## DB2 for i コネクタを追加

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

<ol>
  <li>Sync のダッシュボードから**接続**ページを開きます。</li>
  <li>**接続を追加**をクリックして**コネクタを選択**ページを開きます。</li>
  <li>**データソース**タブをクリックして
  **DB2 for i (Native)** 行に移動します。</li>

  <li>
    行末にある**接続を設定**アイコンをクリックして、**新しい接続**ページを開きます。この操作により、
    **接続を追加**ダイアログボックスが開きます。

    **Note：** **接続を設定**アイコンが表示されない場合は、**コネクタをダウンロード**アイコンをクリックして DB2 for i (Native) コネクタをインストールしてください。
  </li>

  <li>**接続を追加**ダイアログボックスに接続名を入力します。</li>
  <li>**追加**をクリックして、コネクタの**設定**タブを開きます。</li>
</ol>

新しいコネクタのインストールについての詳細は、
[接続](https://docs.sync.cdata.com/26.4/ja/connections/connections.md)を参照してください。

## DB2 for i への認証

After you add the connector, you need to set the required properties.

* **Server:** Enter the address or host name of the DB2 for i 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 database.
* **Password:** Enter the password that you use to authenticate to the DB2 for i database.

## 接続を完了する

To complete your connection:

1. Define advanced connection settings on the **Advanced** tab. (In most cases, though, you should not need these settings.)
2. Click **Create & Test** to create your connection.

## Create a Change Data Capture (CDC) Job with Journal and Schema Selections

This section explains how to create a change data capture (CDC) job for DB2 for i and how to configure row identification for tables without primary keys.

### Create the CDC Job

Follow these steps to create a CDC job for DB2 for i:

1. Enter a name for your job.

2. Select your DB2 for i source.

3. Select your destination.

4. Specify values for the following fields:

   * **Journal Library**
   * **Journal Name**
   * **Source Schema**

   You can select the values for each of these properties from their drop-down lists. Sync validates these values against the DB2 for i system to ensure that the journal exists and is properly configured.

   ![DB2 for i CDC job creation form](https://mintcdn.com/cdata-sync/4njnJgdQs2eFHAEE/26.4/images/DB2-i-Native-Source_creating-a-cdc-job_1.png?fit=max\&auto=format\&n=4njnJgdQs2eFHAEE\&q=85\&s=e7f2544a64ded8ef4fe60df8ee4999ac)

5. Click **Add Job**.

   After the job is created, the journal settings are displayed in the CDC engine settings and cannot be modified.

6. Continue to the job's **Task** tab for table selection. Only tables that meet the following criteria are displayed:
   * The table is journaled.
   * The table is associated with the selected journal.
   * The table belongs to the selected schema.

If you change the journal or schema, previously selected incompatible tables are automatically deselected.

> [!NOTE]
>
> When you create DB2 for i CDC jobs through the API, you must provide the journal library and journal name.

### Configure Row Identification for Tables without Primary Keys

When you configure a change data capture (CDC) job for DB2 for i, you can use the relative record number (RRN) as the value for the `_cdatasync_id` column. This approach provides a stable identifier for tables that do not have a primary key.

By default, Sync uses the RRN as the identifier for DB2 for i records (the default value is `Db2Rrn`). To override this and use an MD5-based row hash instead, set the following [`SyncIdSourceColumn` job option](../../jobs/advanced-job-options#追加オプション) in the **Additional Options** field on the job's **Advanced** tab:

```
SyncIdSourceColumn=RowHash
```

When you use the RRN-based identifier:

* The `_cdatasync_id` column is represented as a BigInt data type.
* The identifier is derived directly from the physical row location in the source table.
* The `_cdatasync_id` column can be used as a partition key for destinations that support partitioning.

> [!WARNING]
>
> Reorganizing the source table resets the RRN values for all rows. This change invalidates previously generated `_cdatasync_id` values because they no longer correspond to the same physical rows. To correct this issue, you must perform a full re-sync of the affected tables. For details, see [Re-Syncing Job Task Execution](https://docs.sync.cdata.com/26.4/ja/getting-started/administration/jobs-page.md#ジョブのタスク実行を再同期).

### Replicate Members of Multi-Member Physical Files

Sync replicates each member of a multi-member physical file as its own table. To choose a member, create an SQL alias that references that member.

> [!WARNING]
>
> If your CDC jobs include multi-member physical files, Sync now replicates **only the default member** instead of all members. Add an alias for each of the other members to avoid losing replication coverage.

#### Configure Replication

To configure replication for multi-member physical files:

1. Journal the physical file, as shown below. Journaling applies to all members, including those added later. Aliases do not require separate journaling.

   ```sql theme={null}
   STRJRNPF FILE(MYLIB/MYFILE) JRN(MYLIB/MYJRN) IMAGES(*BOTH)
   ```

2. Create an alias for each member you want to replicate:

   ```sql theme={null}
   CREATE ALIAS MYLIB.MYFILE_MBR1 FOR MYLIB.MYFILE (MBR1);
   ```

3. Add the aliases as tasks. The aliases appear in the table list next to their associated physical file.

#### Replication Behavior

Replication depends on whether you select an alias or the physical file:

* **Alias:** Using an alias replicates only the member that is referenced by the alias, including the initial load and subsequent changes. The target table uses the alias name.
* **Physical file:** Using a physical file replicates only the default member (the member with the same name as the physical file).
* **Members without selected aliases:** These members are ignored.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.