Configure your Db2 for IBM i database

The Db2 for IBM i streaming connector can monitor and consume changes as they occur within your Db2 for IBM i database. To achieve this, the connector reads journal entries for the tracked tables. This means that the tracked tables must be journaled, and the Db2 for IBM i user for streaming requires permissions to query the journals.

The following sections describe how to set up user permissions, and configure journaling correctly in Db2 for IBM i.

Set user permissions

Within Db2 for IBM i, you require a user with the following permissions as a minimum:

SQL USAGE privileges on the schemas containing the tables to be tracked:
```
GRANT USAGE ON <schema> TO <streaming_user>;
```
Where <schema> is the schema name and <streaming_user> is the user account name.
SQL SELECT privileges on the tables to be tracked. This is to allow the snapshot phase to use SELECT * FROM ... with those tables:
```
GRANT SELECT ON <schema>.<table> TO <streaming_user>;
```
Where <schema> is the schema name, <table> is the table name, and <streaming_user> is the user account name.
Read-only access to the journals that are being used to track streaming changes. You set this within the operating system terminal with the command:
```
GRTOBJAUT OBJ(<schema>/<jrn>) OBJTYPE(*JRN) USER(<streaming_user>) AUT(*USE)
```
Where <schema> is the schema name, <jrn> is the journal name, and <streaming_user> is the user account name.
Read-only access to the journal receivers that are being used to track streaming changes. The operating system command to grant access to all journal receivers associated with the journal is:
```
GRTOBJAUT OBJ(<schema>/*ALL) OBJTYPE(*JRNRCV) USER(<streaming_user>) AUT(*USE)
```
Where <schema> is the schema name, and <streaming_user> is the user account name.

Configure journaling

The Db2 for IBM i connector initially queries the database to determine which journals capture the selected tables. When streaming, the connector will read from each of the relevant journal receivers.

Use the following commands to set up journaling on tables for use with streaming where the table isn't already being journaled.

Create a journal receiver:
```
CRTJRNRCV JRNRCV(<library>/<journal-receiver-name>)
```
Where <journal-receiver-name> is the name of the journal receiver, and <library> is the name of the library where it will be created. Read Create Journal Receiver (CRTJRNRCV) to learn more.
Create the journal:
```
CRTJRN JRN(<library>/<journal-name>) JRNRCV(<library>/<journal-receiver-name>)
```
Where <journal-name> is the name of the new journal, and <library> and <journal-receiver-name> are the values already used when creating the journal receiver. Read Create Journal (CRTJRN) to learn more.
Start journaling for the desired files:
```
STRJRNPF FILE(<library>/<file>) JRN(<library>/<journal-name>) IMAGES(*BOTH)
```
Where <file> is the name of the file to be journaled, and <library>, and <journal-name> are the values already used when creating the journal.

To capture a complete change record for the updates, with the complete table values both before and after the update, journals must be configured with IMAGES(*BOTH) when starting the journaling of a file, as shown in the above command.

Configure signal tables

If you are using on-demand snapshots, a signal table must be configured in the source database. This table is tracked by the connector and automatically triggers snapshots when it receives a signal.

Note that:

A signal table should be pipeline-specific to avoid accidental overlap of snapshot executions.
The connection details provided for the source must have write permissions to the signal table.
The pipeline will insert a row into this table to trigger a snapshot.
The table should be created with the correct structure.
Supplemental logging must be enabled for the signal table.

Create the table

Create a signal table with the following SQL statement:

CREATE TABLE IF NOT EXISTS <schema>.<table> (
id VARCHAR(42) PRIMARY KEY,
type VARCHAR(32) NOT NULL,
data VARCHAR(2048) NULL
);

The signal table should have three columns:

id: An identifier for each signal execution. This is the table's primary key, of type VARCHAR(42).
type: Indicates the type of signal being sent. This column is of type VARCHAR(32).
data: Stores additional data related to the signal. This column is of type VARCHAR(2048).

Commence signal table journaling

Start journaling for the signal table with the following command:

STRJRNPF FILE(<schema>/<table>) JRN(<schema>/<journal-name>) IMAGES(*BOTH)

Where <schema> is the schema, <table> is the signal table name, and <journal-name> is name of the journal that will record changes to the signal table.

Grant database role privileges

The database role that is configured for running a pipeline will need to have the expected privileges on the signal table. The on-demand snapshot requires USAGE on the schema containing the signal table and INSERT on the signal table.

Grant SQL USAGE privileges on the schema containing the signal table:
```
GRANT USAGE ON <schema> TO <streaming_user>;
```
Where <schema> is the schema name and <streaming_user> is the user account name.
Grant SQL INSERT privileges on the signal table:
```
GRANT INSERT ON <schema>.<table> TO <streaming_user>;
```
Where <schema> is the schema name, <table> is the signal table name, and <streaming_user> is the user account name.

Once you set up the signal table, on-demand snapshots can be enabled, and new tables that are added to the pipeline after the initial startup will be snapshotted. If on-demand snapshots are disabled, any new tables added to the pipeline will capture changes from that point onwards but will not sync their historical data with the target, and the pipeline will immediately have a STREAMING status.

Configure new tables

When adding tables to a pipeline, they must be correctly configured to allow the on-demand snapshot to take place and to ensure future changes on those tables will be streamed.

If on-demand snapshots are disabled, any new tables added to a pipeline will have their changes captured from that point onwards, but will not synchronize their historical data with the target and the pipeline will immediately have a STREAMING status.

Migrating tables to a single journal

You may already have tables configured to be tracked by different journals—even though on-demand snapshots currently only support a single journal. You can change the journal associated with a table by running the following commands:

End journaling for a table:
```
ENDJRNPF FILE(<schema>/<table>)
```
Where <schema> is the schema name and <table> is the table name to end journal tracking for.
Restart journal tracking for a table under a predefined journal:
```
STRJRNPF FILE(<schema>/<table>) JRN(<schema>/<journal-name>) IMAGES(*BOTH)
```
Where <schema> is the schema, <table> is the table name for the table we are moving to the journal, and <journal-name> is name of the journal that will record changes for the table going forwards.

Limitations

Schema changes

The Db2 for IBM i connector might fail to process changes to journal entries if you alter the table's structure when the connector isn't active. To prevent such issues:

Only use the connector when the table's schema is fixed.
Only make schema modifications while the connector is running.

Additionally, rapidly making multiple schema changes to a single table while the connector is running can potentially lead to connector failures. Ensure a schema change has propagated to the output files or target table before initiating another schema change.

Multi-member files

Multi-member files aren't supported.

Table names

Tables that require double quotes around the name when referencing them within SQL statements—such as for tables with lower/mixed case or with special characters in their names—aren't supported.