Skip to main content
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.
  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) to learn more.
  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) to learn more.
  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.

​
Configure new tables

When adding tables to a pipeline, add them to the journal used to track streaming changes. Newly added tables capture changes from the time they are added. Historical data is not synchronized with the target. The pipeline immediately enters a STREAMING state. To capture historical data for a table, create a snapshot for that table.

​
Migrating tables to a single journal

You may already have tables configured to be tracked by different journals—even though streaming currently only supports a single journal. You can change the journal associated with a table by running the following commands:
  1. 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.
  2. 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

​
Multiple journals

The Db2 for IBM i connector does not support streaming across multiple journals. To stream data from tables in multiple journals, create separate pipelines, one pipeline per journal.

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

​
Snapshots

Snapshots can only be requested for tables across a single journal. If snapshots are required for tables across multiple journals, separate pipelines must be created that correspond to each journal.