Configuring Db2 for IBM i

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.

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

The Db2 for IBM i connector might fail to process changes to journal entries if you alter the table's structure when the connector is not 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 within SQL statements—such as for tables with lower/mixed case or with special characters in their names—are not supported.

Next steps

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