Format of delimited messages

Delimited messages represent changed records as fields that are separated by a character, such as a comma.

You can delimit character data with characters such as double quotation marks, and delimit rows with characters such as newline characters. You specify which character to use for delimiting data by using the COLDELIMITER, ROWDELIMITER and STRINGDELIMITER parameters.

The following table describes the format of a delimited message.

Table 1.
Column position	Column type	Description
1	Integer	Used only by IBM®. The value "9" identifies delimited messages.
2	Char	Indicates whether valid or invalid data was detected while processing the column. The literal value "IBM" indicates that the column contains valid data. Otherwise, the value is an error flag that identifies the first column that contains badly formed data. You specify the option to flag invalid data by defining the PUBBDCOMPAT configuration parameter.
3	Char	Date, in YYYYDDD format, that the capture service wrote the record to the IBM MQ message queue. This is the unit of recovery (UOR) commit date obtained from the source database system.
4	Char	For IMS, this value represents the IMS commit time. For other data sources, the value represents the date and time of when the commit was reported to the capture service. The date and time are obtained from the source database system.
5	Char	Schema name of the table or view.
6	Char	Name of the table or view.
7	Char	Indicates the operation that took place at the data source. ISRT Insert REPL Replace or update DLET Delete
8	Char	Indicates whether the record contains an after image or before image, if the operation was a replace or update. A After image B Before image
9	Data column 1, data column 2, ... data column `n`	The values of the columns of the row inserted, deleted, or modified. Each column is separated by a single column delimiter (COLDELIMITER). Each column with a data type of CHAR, VARCHAR, LONG VARCHAR, GRAPHIC, LONG VARGRAPHIC, VARGRAPHIC is enclosed in a user-specified string delimiter (STRINGDELIMITER). Each column with a data type of BINARY or VARBINARY when PUBBINASIS=FALSE (default) is represented as a hexadecimal encoded string that is enclosed in a user-specified string delimiter (STRINGDELIMITER). When PUBBINASIS=TRUE is specified, these columns are not surrounded by the string delimiter and are not hexadecimal encoded. The data starts immediately following the column delimiter. For a BINARY column, the amount of data that is included in the message is the length of the column. For a VARBINARY column, the MQ message buffer contains a 2-byte binary “length” value followed by the data. When the VARBINARY column references an IMS variable-length segment (DATAMASP OFFSET 0) the message contains the actual segments contents. The amount of binary data that is included in the message is identified by the length. The next delimiter is located at the current position plus length. When the VARBINARY column references a VSAM record and includes the USE RECORD SIZE clause or the LENGTH is two greater than the VARBINARY column maximum , the binary value that is included in the message excludes the 2-byte length component. The next delimiter is located at current position plus the length plus 2. Numeric data has no character delimiters.

When a delimited message contains badly formed data, you can define configuration parameters to control how the invalid data is processed. The PUBBDACTION parameter identifies the action to take when badly formed data is encountered. The following configuration parameters enable this processing:

PUBBDACTION: Set this parameter to TRUE to publish badly formed data or FALSE to fail when badly formed data is encountered.
PUBBDCOMPAT: Set this parameter to define the options for publishing badly formed data in hexadecimal format or in a format compatible with Version 9.5
- 0: Publish in hexadecimal format
- 1: Version 9.5: No repair, no flag
- 2: Version 9.5: Repair, no flag
- 3: Version 9.5: Repair and flag
PUBBDDIAGLIMIT: Set this parameter if you want to limit the number of diagnostic messages issued for each table mapping.
PUBBDWTOLIMIT: Set this parameter if you want to limit the number of WTO messages issued for all table mappings.

Example

Table TEST.EMPLOYEE has the following fields: First Name, Last Name, Position, Department, Salary, Commission. A message contains two change records: one insert and one update. In this example, the column delimiter is a comma, the row delimiter is a newline character, and the string delimiter is a double quotation mark.

Figure 1. Two example change records in the delimited format

9,"IBM  ","2014346","1523180005","TEST","EMPLOYEE","ISRT","A",100,"John","Doe","MGR","SALES",
120000,12000
9,"IBM  ","2014346","1523180305","TEST","EMPLOYEE","REPL","B",110,"Ed","Son","MGR","SALES",
109000,10000
9,"IBM  ","2014346","1523180305","TEST","EMPLOYEE","REPL","A",110,"Ed","Son","MGR","SALES",
129000,12000

Figure 2. Delimited output with the invalid data flag

The following examples show delimited output with the invalid data flag based on the value of the PUBDCOMPAT configuration parameter.

PUBDCOMPAT: 0

In this example, column 5 is flagged (IBM-INVALID-COLUMN-5-A) because the salary data contains spaces (X404040).

9,"IBM-INVALID-COLUMN-5-A","2014346","1523180005","TEST","EMPLOYEE","ISRT","A",120,
"Jane","Roe","MGR","SALES",X404040,12000

PUBDCOMPAT: 1
In this example, the salary data that contains spaces is treated as a valid numeric field (404.04).
```
9,"IBM", "2014346","1523180005","TEST","EMPLOYEE","ISRT","A",120,
"Jane","Roe","MGR","SALES",404.04,12000
```

PUBDCOMPAT: 2

In this example, the badly formed data in column 5 is replaced with -999999.

9,"IBM","2014346","1523180005","TEST","EMPLOYEE","ISRT","A",120,
"Jane","Roe","MGR","SALES",-999999,12000

PUBDCOMPAT: 3

In this example, the numeric error in column 5 is flagged (IBM-INVALID-NUMERIC-005A) and the badly formed data is replaced with -999999.

9,"IBM-INVALID-NUMERIC-005A","2014346","1523180005","TEST","EMPLOYEE","ISRT","A",120,
"Jane","Roe","MGR","SALES",-999999,12000

In this example, a new column is added and flagged (IBM-INVALID-COLUMN-007A-HEX). Column 7 is a character column that contains a shift-out character without a corresponding shift-in character (X'0E4060').

9,"IBM-INVALID-COLUMN-007A-HEX","2014346","1523180005","TEST","EMPLOYEE","ISRT","A",120,
"Jane","Roe","MGR","SALES",4000.00,12000,X'0E4060'

This example flags both the character column and the numeric column in error.

9,"IBM-INVALID-COLUMN-007A-HEX+IBM-INVALID-NUMERIC-005A","2014346","1523180005","TEST","EMPLOYEE","ISRT","A",120,
"Jane","Roe","MGR","SALES",-999999,12000,X'0E4060'

You can modify the content of delimited messages by using the DELIMITEDFMTOPTS configuration parameter. Details of this parameter can be found in the DELIMITEDFMTOPTS topic.