Connect CDC SQData

The OPTIONS statement provides for the specification of certain global behavior and tuning parameters. The applicable options depend on the nature of the source and target involved. Non-applicable options are silently ignored.

Syntax

OPTIONS [AVRO COMPATIBLE NAMES]

,[CONFLUENT REPOSITORY <registry_url>]

,[MAX IN FLIGHT <number>]

,[ISO8601 PRECISION <number>]

,[IGNORE NOMAPPING]

,[LOGICAL DECIMAL TYPE]

,[METADATA (<metadata_name> [AS <metadata_alias> ] [,<metadata_name> [AS <metadata_alias> ]] [, ...])]

,[STRIP TRAILING SPACES]

,[ROTATE DELAY <duration>]

,[ROTATE SIZE <size> [K|M|G]]

,[NAMESPACE '<namespace>']

 

Keyword and Parameter Descriptions

AVRO COMPATIBLE NAMES
Applies to JSON formatted Targets. AVRO restricts the valid names that can be used to describe objects. This parameter affects JSON formatting; fields and objects names are automatically transformed to meet AVRO’s requirements:
Non Alphanumeric characters are transformed to ‘_’ (underscore)
Alphabetic character are turned to lowercase

CONFLUENT REPOSITORY <registry_url>
Only applies to AVRO formatted Kafka targets. The url of the Confluent Schema Registry to be used to retrieve/register AVRO schemas. The presence of that option also triggers the generation of a confluent header in front of each AVRO encoded Kafka topic written.

MAX IN FLIGHT <number>
Only applies to Kafka targets. When number of Units of Work (UOWs), regardless of the number of records within each UOW, pending acknowledgment by Kafka reaches the <number>, a SQDUR10i message  will be printed. If this parameter is not specified the default is 100. The consequence of running out of in-flight UOWs is not necessarily a problem, only reduction in throughput, since the Replicator has to wait for UOW's to be acknowledged by Kafka, before resuming the processing of new UOWs. The larger the Kafka ACK latency, the bigger the number of allowed in-flight UOWs needs to be to avoid stalling.
 
If for example you need to process a flow of well behaved UOWs containing 10 CDC records per UOW on average and typically ACK latency is 2 seconds, a MAX IN FLIGHT value of at least 10,000 (UOW rate * latency) will be required to sustain 5000 UOWs/second.

ISO8601 PRECISION <n>
Specifies the precision of the fractional seconds of the METADATA "timestamp" option where <n> must be less than or equal to the precision of the original source log timestamp, zOS Storeclock.

IGNORE NOMAPPING
Specifies that CDC records for objects that have no Static Mapping SOURCE specification will be ignored. The first occurrence for any given Source table/IMSDBD will result in an message SQDRNNNN : SQData Replicator has encountered unknown source: <table_name>|<dbd_name>. This option will cause a syntax error if Dynamic Mappings through a MAPPINGS EXIT statement is also present in the script.
 
Note, a MAPPINGS EXIT can alternatively duplicate this functionality and determine if unexpected Source CDC records should be ignored or cause termination.

LOGICAL DECIMAL TYPE
Applies to AVRO formatted target data payloads and specifies that the AVRO "Logical Decimal Format" will be used instead of the "string" datatype which is the default for decimal numbers.

METADATA (<header_tag> [AS <metadata_alias> ] [,<header_tag> [AS <metadata_alias> ]] [, ...])

The keywords, corresponding to individual Header Tags are listed below along with their descriptions.

Notes:

1.All of the header Tags will be included by default for JSON and AVRO format target datastores unless explicitly left off the list specified in OPTIONS METADATA.

2.These Header Tags are included in the topic "payload" and are not related to the Kafka "Topic" header section of the Kafka topic.

3.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).

4.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.

5.object_name is populated with the name published to the engine making it possible to identify specific "objects" by "name" that the script is not yet ready to process.

6.See STORECLOCK for more information including how to convert from and to the STCK value outside the product.

STRIP TRAILING SPACES
Applies to both JSON and AVRO formatted Targets. Removes trailing spaces from CHAR data type fields.

ROTATE DELAY <duration>
HDFS Hadoop or flat file targets only, trigger a file rotation after the indicated delay, in minutes, has elapsed since the current file has been opened.

ROTATE SIZE <size> [K|M|G]
HDFS Hadoop or flat files targets only, target, trigger a file rotation after the indicated size, in Kilobytes, Megabytes or Gigabytes has been written.

NAMESPACE ' <name_space>'
Optional name_space assigned to the Objects created, that will appear in the JSON/AVRO (header/payload) as the "namespace":"<name_space>" pair.

Note, While not explicitly stated above, ALL JSON and AVRO payload data are automatically converted from the source code page to UTF-8, the JSON/AVRO standard.