Journal mapping provides a method to track changes to source data without directly altering the target dataset. This feature enables the creation of an audit trail of changes while keeping the target dataset intact.
With journal mapping, each new row added to the target dataset includes metadata detailing the change, such as:
- Type of change (insert, update, delete)
- Timestamps indicating when the change occurred on the source
- Key values extracted from the source row
This metadata facilitates tracking changes over time without modifying the target dataset. For instance, users can query the target dataset to see when a specific row was inserted or updated in the source.
Note: Journal mapping is exclusively supported when the source is DB2 for IBM i and the target is Google BigQuery.
To configure journal mapping in a continuous replication pipeline:
- Select the Audit, when setting up your replication to enable logging of changes in the target dataset.
- In the Target options, activate Batch send to send records after 500 are captured or when the source transaction ends. This batches records for efficiency. For example, a 10 row transaction would send 1 batch of record while a 1000 row transaction would send records in 2 batches of 500.
- While Mapping fields, you have the option to perform custom mapping. Ensure that the Target table has a specific primary key defined, not the Source table. The Target table primary key should be one of the system variables listed below, where:
- Variables with ^1 are recommended fields for primary keys on the Target table if journal mapping is enabled.
- Variables with ^2 are default fields selected for table creation.
| Name | Description | Target Column type |
|---|---|---|
| sv_program_name | Program Name | CHAR(10) |
| sv_job_name | Job Name | CHAR(10) |
| sv_job_user | Job User | CHAR(10) |
| sv_job_number | Job Number | CHAR(6) |
| sv_op_timestamp | Row timestamp | CHAR(20) |
| sv_manip_type 2 | Row Manipulation Type | CHAR(1) |
| sv_receiver_library | Receiver Library | CHAR(10) |
| sv_receiver_name | Receiver Name | CHAR(10) |
| sv_journal_seqno | Journal Sequence Number | CHAR(20) |
| sv_file_member | Sending file member name | CHAR(10) |
| sv_sending_dbms | Sending DBMS Type | VARCHAR(20) |
| sv_sending_server | Sending Server Name | VARCHAR(32) |
| sv_sending_table | Sending Table Name | VARCHAR(128) |
| sv_trans_id 1 2 | Transaction Id | VARCHAR(20) |
| sv_trans_row_seq 1 2 | Transaction Row Sequence | INTEGER |
| sv_trans_timestamp 2 | Transaction Timestamp | VARCHAR(20) |
| sv_trans_username | User name | VARCHAR(70) |
| sv_trans_commit_seqno | Transaction Commit Sequence Number | VARCHAR(20) |
Restriction: To use a same dataset table in a regular replication pipeline and a journal mapping pipeline, you must create two separate replication projects for each pipeline.