Configuring Db2 for IBM i

Overview

Matillion CDC's Db2 for IBM i connector can monitor and consume changes as they occur within your Db2 for 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 i user for CDC 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. For details of how to set up the connector in Matillion CDC, read Db2 for IBM i Connector.

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 <user>;

Where <schema> is the schema name and <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 <user>;

Where <schema> is the schema name, <table> is the table name, and <user> is the user account name.

• Read-only access to the journals that are being used to track CDC changes. You set this within the operating system terminal with the command:

GRTOBJAUT OBJ(<schema>/<jrn>) OBJTYPE(*JRN) USER(<user>) AUT(*USE)

Where <schema> is the schema name, <jrn> is the journal name, and <user> is the user account name:

• Read-only access to the journal receivers that are being used to track CDC changes. The operating system command to grant access to all journal receivers associated with the journal is:

GRTOBJAUT OBJ(<schema>/*ALL) OBJTYPE(*JRNRCV) USER(<user>) AUT(*USE)

Where <schema> is the schema name, and <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 CDC where the table isn't already being journaled.

1. 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) for further details.

2. 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) for further details.

3. 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.

Limitations

Schema changes

There are scenarios where the Db2 for IBM i connector can fail to parse changes to journal entries if the schema has changed while the connector is stopped.

For this reason, the connector should only be used where the table schema is fixed—or only modified—while the connector is running.

Multi-member files

Multi-member files are not supported for this private preview version.

Next steps

With the above configuration items completed, you should now be in a position to connect the CDC agent to your Db2 for IBM i database. Please consult the Matillion Data Loader Pipeline UI guide for the next steps.