Structure of delimited messages for Event Publishing

Each row operation is published as a change-data record with two parts:

Header

The header consists of information such as the source table, sending program, and commit time.

Data from source table

Data from the source table begins immediately after the header.

Entries in both the header and data sections of the record are separated by column delimiters. The default column delimiter is a comma (,). Change-data records are separated by a record delimiter. The default record delimiter is a new-line character.

A string-delimiter encloses CHAR, VARCHAR, LONG VARCHAR, GRAPHIC, LONG VARGRAPHIC, VARGRAPHIC, CLOB, DBCLOB, BLOB, TIME, DATE, and TIMESTAMP data. Strings have no padding or null-termination. If string data contains its string-delimiter, the delimiter is escaped by prefixing it with the same delimiter. Numeric data is published without the string-delimiters.

You can use the Replication Center to specify your own column delimiter, record delimiter, string delimiter, and decimal character.

Change-data records include both before and after values. The columns for a change-data record are ordered by column ID.

Some large transactions might require multiple messages.

Message header

The message header occupies the first 12 positions of the message. The structure of the message header is as follows:

type,"identifier","date","time","table_owner","table_name",
"operation","transaction_identifier","commit_lsn","commit_time",
"plan_name",segment_number,

Details

type

An integer value that is used internally by IBM®.

identifier

An identifier for the sending program. This field also indicates whether invalid character data exists in the row. If the publishing queue map specifies to send data in hexidecimal format when code page conversion errors occur (SENDRAW_IFERROR=Y in the IBMQREP_SENDQUEUES table) and any column in the row contains invalid data, this identifier is set to IBM-INVALID-COLUMN-####-@-HEX, and all character columns in the row are sent in hex string format. The #### field represents the first column in which the Q Capture program detected invalid data and @ is either B (before value) or A (after value). If SENDRAW_IFERROR=N and any column in the row contains invalid data, then the identifier is set to IBM-INVALID-COLUMN-####-@-NULL and all character columns are sent as null. In either case, LOB and XML columns in rows with invalid character data are sent as null.

Examples below show delimited messages with a normal identifier and an identifier for invalid character data.

date

The date that the delimited message was put on the send queue, in the YYYYnnn format. Where nnn is the number of the date for the year. For example, 6 August, 2007 is the 218th day of the year and the format is represented as 2007218.

time

The time that the delimited message was put on the send queue, in HHMMSSmmmmmm format.

table_owner

The source table owner.

table_name

The source table name.

operation

A four-character code for the SQL operation that caused the row change:

ISRT

An INSERT operation. For inserts, the before values of each column are null and are represented by column delimiters with no values between them. For example, an insert into a three-column table that uses a comma (,) for the column delimiter can be represented by the following syntax:

,,,col1_value,col2_value,col3_value

REPL

An UPDATE or replace operation. Updates include the before values for each column followed by the after values. For example, an update of a row in a three-column table that uses a comma for the column delimiter can be represented by the following syntax:

col1_beforevalue,col2_beforevalue,col3_beforevalue,
col1_aftervalue,col2_aftervalue,col3_aftervalue

DLET

A DELETE operation. For deletes, the after values of each column are null and are represented by column delimiters with no values between them. For example, a delete from a three-column table that uses a comma (,) for the column delimiter can be represented by the following syntax:

col1_value,col2_value,col3_value,,,

transaction_identifier

A character string that uniquely identifies a DB2® unit of work.

The length is CHAR(10) FOR BIT DATA for DB2 for z/OS® Version 10 or lower and looks like this: 0000:0000:0388:4642:0000. The length is VARCHAR(12) FOR BIT DATA for DB2 for z/OS V11 or higher and looks like this: 0000:0000:0388:4642:0000:0000.

commit_lsn

A time-based log sequence number of the COMMIT statement for the transaction.

The length is CHAR(10) FOR BIT DATA for replication Version 10.1 (ARCH_LEVEL 1001) or lower and looks like this: 0000:0000:0388:4642:0000. The length is VARCHAR(16) FOR BIT DATA for replication 10.2.1 (ARCH_LEVEL 1021) or higher and looks like this: 0000:0000:0000:0388:4642:0000:0000:0000.

commit_time

The timestamp of the COMMIT statement for the transaction. This value is a 19-character string in YYYY-MM-DD-HH.mm.ss format.

plan_name

z/OS: The DB2 application plan name. This value is null for all platforms except z/OS.

segment_number

Four numeric characters that are used to link segmented transaction messages. The last message in a transaction has segment number 0000. If the transaction fits into a single message, the segment number is 0000. For example, the segment numbers for a five-message transaction are 0001, 0002, 0003, 0004, and 0000.

Data from source table

Data from the source table begins in the 13th position in the message. The structure of the data is as follows:

column_value column_delimiter column_value column_delimiter record_delimiter

Details

column_value

The data value from the source table. A null column value is represented by two consecutive column delimiters.

column_delimiter

A single character that is used to separate column values. The default is a comma (,).

record_delimiter

A single character that is used to denote the end of a change-data record. The default is a new-line character (X'25' in EBCDIC hexadecimal or X'0A' in ASCII hexadecimal).