DB2 data capture and replication

DATA CAPTURE on source tables

DATA CAPTURE CHANGES is a table attribute that tells Db2 to write additional information to the recovery log so replication products can reconstruct changes reliably. It is normally required for log-based capture from Db2 for z/OS sources. DATA CAPTURE NONE is the default for tables that are not published. Treat the decision as an application-data contract: changing it affects logging volume, utility planning, and the ability to publish before/after row information.

Enable data capture only on supported source tables selected for replication. Confirm that source rows have a stable key, that the target has matching mapping rules, and that applications do not rely on unsupported changes being silently replicated. A replication project begins with table design and ownership, not with a queue manager install.

1
2
3
4
5
6
7
CREATE TABLE SALES.ORDER_HEADER (
  ORDER_ID BIGINT NOT NULL PRIMARY KEY,
  STATUS CHAR(12) NOT NULL,
  UPDATED_AT TIMESTAMP NOT NULL
) DATA CAPTURE CHANGES;
 
ALTER TABLE SALES.ORDER_HEADER DATA CAPTURE NONE; -- use only when publishing is retired

Log-based replication and change data capture

Log-based replication reads committed changes from the Db2 recovery log instead of repeatedly scanning base tables. A capture program interprets log records, preserves transaction boundaries, and hands changes to an apply process or event consumer. This design lowers source query overhead and can support low latency, but it makes archive-log retention and log-reading permissions part of the availability design.

Change data capture, or CDC, is the broader pattern: observe INSERT, UPDATE, and DELETE events and deliver them to targets, queues, or ETL consumers. IBM Data Replication offerings include Q Replication, SQL Replication, Event Publishing, and CDC-oriented products. Product names and supported target matrices change, so validate the current entitlement and version support before selecting a topology.

Q Replication

Q Replication is IBM’s high-volume, low-latency replication technology. Q Capture reads source Db2 log changes, serializes transactions into IBM MQ messages, and Q Apply reads those messages to update target tables or call target procedures. Queue maps and Q subscriptions define routing, row and column selection, load behavior, and conflict-related options.

MQ decouples source and target availability but does not remove operational responsibility. Monitor queue depth, channel health, capture position, apply position, and poison-message handling. Size logs and queues for an outage window. A target outage should create a measured backlog that can drain within its recovery objective, not an unbounded queue that eventually forces an emergency resynchronization.

• Q Capture: reads source log records.
• IBM MQ: transports transactional change messages.
• Q Apply: applies changes at the target.
• Q subscriptions and queue maps: define what moves and where.

SQL Replication, CDC, and event publishing

SQL Replication uses capture and apply programs with staging tables for committed transactional data. It is a useful fit where SQL-based staging and administration match the target landscape. Event Publishing is a change-data-capture pattern that publishes row or transaction events, often through MQ, for integration services, business intelligence, or event-driven actions rather than only another relational table.

IBM InfoSphere Change Data Capture and successor/related IBM data replication offerings can deliver changes to databases, queues, and ETL environments according to configured mappings. Compare latency, source impact, target types, operational skills, licensing, and recovery behavior. “CDC” describes an outcome; Q Replication, SQL Replication, and log readers describe different implementation paths.

Transaction ordering, positions, and latency

Transaction ordering is non-negotiable for related rows. Capture must not expose a committed child row before its committed parent when the target enforces referential integrity. Products use commit order, transaction identifiers, and source log positions to preserve or deliberately manage order. Long-running units of work create a common surprise: small early changes cannot be safely released until the whole transaction commits.

Db2 log positions are commonly described as LSN, RBA, or LRSN depending on architecture and data-sharing context. They identify where capture and apply have progressed. Record them in runbooks and dashboards, but do not manually move a restart point without vendor/product procedures: skipping or replaying a range can create data loss or duplicates. Replication latency is the elapsed distance from source commit to target availability; track both oldest unprocessed commit and current throughput.

Conflicts, sources, and targets

Unidirectional replication is simplest: one source owns writes and targets are read-only copies. Active-active and peer topologies allow multiple sites to update the same logical data, which creates conflicts. Define a conflict policy before enabling writers: source priority, timestamp winner, column-level merge, stored-procedure arbitration, or reject-and-reconcile. There is no universally correct winner for a double update to a customer balance.

Source and target considerations include compatible encodings, nullable columns, keys, DDL evolution, identity generation, triggers, constraints, permissions, and application retry behavior. Test initial load, steady state, target outage, source outage, schema change, and full resync. A target that “looks close enough” is not a disaster-recovery copy until recovery exercises prove it.

Temporal tables, LOBs, XML, and schema change

Temporal tables add history semantics. Decide whether the target receives only current rows, system-time history, business-time values, or independently managed history. Do not assume a replication mapping automatically preserves every temporal behavior; test updates, deletes, retention jobs, and point-in-time queries on the target.

LOB and XML values can be large, externalized, or encoded differently from ordinary scalar columns. They affect log volume, queue payload, apply throughput, and target storage. Confirm the replication product supports the exact LOB/XML type and limit you use, then test a maximum-size row and a rollback. Treat DDL changes as versioned releases with compatible ordering across source, capture, and target.