The METADATA Header Tags provide the means to alter the default behavior of the REPLICATE and APPLY Commands when writing to target datastores using either JSON or AVRO formatting:
- Only the Header Tags specified in the METADATA Options block will be generated in the target record payload section allowing items to be excluded from the output simply by leaving them off the list.
- The literal text name of the Individual Tags can also be renamed by assigning an alias.
- A single "user_data" tag can be added to the list and aliased. It can contain any other information required through specification of its content in the script through a variable or expression.
- Some Header Tags are Source Datastore specific.
Syntax
OPTIONS METADATA (object_name, change_op, uow, seq, user_data, <header_tag> [AS <metadata_alias> ] [,<header_tag> [AS <metadata_alias> ]] [, ...])Note: If the OPTION METADATA is used, you must include the tags change_op and change_object or they will not be emitted. When noMETADATA header tag is defined, the JSON includes change_op, object_name, as well asalias, timestamp, and seq.
Keyword and Parameter Descriptions
The keywords, corresponding to individual Header Tags are listed below along with their descriptions.
| Header Tag | Description |
|---|---|
| change_op | The change operation, I/U/D from the associated CDC record. DEFAULT |
| object_name | The actual <object_name> from the associated CDC record which could be an IMS segment name, relational table name, or file name. DEFAULT |
| alias | User supplied <alias_name> from the AS <alias> in the DESCRIPTION (if there is one) that will appear in the JSON/AVRO (header/payload) as the "alias":"<alias_name>" pair. |
| stck | Storeclock (for zOS sources, time stamp for others) value associated with the source CDC record. |
| timestamp | Same value as stck above except in ISO format. The precision of the fractional seconds can be adjusted using ISO8601 PRECISION <n> Option where <n> must be less than or equal to the precision of the original source log timestamp. |
| seq | A sequence number representing the order or position of the "record" in the original source transaction UOW. |
| uow | Unit of Work ID, the content will vary by source datastore type but will always uniquely identify the source unit of work from all other UOW's captured from the same source. |
| region | IMS ONLY, the IMS control region where the UOW was processed. |
| userid | The User ID associated with the transaction or program initiating the UOW. Available for IMS, Db2, Db2/LUW and Oracle sources ONLY. |
| program | IMS ONLY. In IMS, the name of an IMS MPP, BMP or Batch program. |
| psb | IMS ONLY, the IMS Database Program Specification Block (PSB) associated with the program initiating the UOW. |
| txn_name | IMS. In IMS, the name of an IMS Transaction. |
| user_data | User defined metadata. Like the other metadata tag names it can be renamed with an alias. The value or content of the user_data tag is set using the CDC_SET_METADATA function and will retain its value unless or until it is changed |
Notes:
- These Header Tags are included in the topic "payload" and are not related to the Kafka "Topic" header section of the Kafka topic
- Some Header Tags are "always emitted" for reasons of backward compatibility. Other Header Tags once used cannot be removed from the schema even if no longer listed in OPTIONS METADATA because they are treated by the schema registry in a way similar to a relational column with a NOT NULL WITH DEFAULT constraint. Those items will remain in the schema but their associated values will be reduced to a minimal data type value like a single space or zero (0).
- The sequence number "seq" will increment from one record to the next but may skip numbers corresponding to "records" in the original source UOW but not specified for capture or not "selected" for processing within the Engine script.
- See STORECLOCK for more information including how to convert from and to the STCK value outside the product.
Example 1
Select a subset of the available metadata and rename three items.
OPTIONS METADATA (change_op AS cdc_op
,object_name AS table_name
,timestamp
,Txn_name AS tran_code)Example 2
Select a subset of the available metadata and define the data to be included in the user_data Tag as the first three characters of the Transaction code.
OPTIONS METADATA (change_op
,object_name
,timestamp
,uow
,user_data AS tran_prefix)Then in the main select include the following line to populate the user_data
CDC_SET_METADATA(user_data,LEFT(CDC_METADATA(txn_name),3))
Example 3
Select the default set of Db2 metadata and rename the user_data Tag, "processing_period" and conditionally populate its value using CDC_SET_METADATA in the script as, DAY, EOM, EOY.
OPTIONS METADATA (change_op
,object_name
,uow
,region
,userid
,Txn_name
,user_data AS process_period)Then in the main select include the following line to populate the user_data with the first three characters of the Transaction code.
if <condition>
CDC_SET_METADATA(user_data,'EOM')
else...