Schema modification language (SML)

Important: Schema modification language is applicable to IDR 11.4.0.4-5644 for Kafka onwards.
All KCOPs can modify the following aspect of the Avro schema for each input table:
  • Change Name
  • Change Namespace
  • Change Doc
  • Change Error
  • Add Aliases
  • Add additional attributes (metadata) if they do not conflict with reserved words that are used by Avro.
To apply those effects, add SML (schema modification language) commands to KCOP parameter file. The following list is the SML syntax:
  • MODIFY_SCHEMA_schema-fqn$field-name_SET_keyword = value
  • MODIFY_FIELD_schema-fqn$field-name_SET_field-keyword = value
  • keyword -> NAME,NAMESPACE,DOC,IS-ERROR,ALIAS-RP,ATTRIBUTE
  • field-keyword -> NAME,ALIAS-AP,ATTRIBUTE
Definition of keywords in the syntax
  • NAME: A string of pattern [a-zA-Z0-9_]+, field/schema name.
  • NAMESPACE: A string of pattern ([a-zA-Z0-9_]+)(\\.([a-zA-Z0-9_]+))*, schema namespace.
  • DOC: A string, schema documentation, treated as a comment.
  • IS-ERROR: A Boolean (true|false), indicates whether the schema is for an error object.
  • ALIAS-RP: A comma-separated string of pattern [a-zA-Z0-9_]+, replaces existing aliases with given aliases for a schema.
  • ALIAS-AP: A comma-separated string of pattern [a-zA-Z0-9_]+, replaces existing aliases with given aliases for a field schema.
  • ATTRIBUTE: A comma-separated list of name:value pairs. A name is a quoted string. A value is a string in double quotation marks, or a number, or true or false. The name cannot be a reserved word (name, namespace, doc, type, fields, isError, default, aliases, or order). The attributes are injected into the Avro schema as metadata. They will not affect JSON KCOPs.
Fully qualified name
schema-fqn is the fully qualified name in the schema definition for table, which is defined as namespace.name.field-name is an optional identifier for a column name. The schema-fqn and field-name are separated by $. The modification will take effect after you restart the subscription and perform at least one DML operation (insert, delete, or update).
Single row avro/json KCOPs:
before.image.prefix and after.image.prefix applies to every case. You can always override the properties in your kcops.properties for single row.
Non-nullable LOB:
Non-nullable LOB makes the beforeImage nullable. For single-row Avro/json schema, if there is a non-nullable LOB column in the source table, CDC defines the schema for the before image and after image differently. The non-nullable LOB column schema in the beforeImage must be a union type to allow nulls, since CDC cannot get the before image of LOB columns from the log. If before.image.prefix is not set, CDC will prefix the namespace of the beforeImage schema with "before." If before.image.prefix is set, then CDC uses the specified prefix for the namespace of the beforeImage and for all columns in the before image. Similarly, if after.image.prefix is set, then CDC will use the specified prefix for the namespace of the afterImage and for all columns in the after image.
MODIFY_* properties for KcopSingleRowAvroAuditIntegrated
  • MODIFY_SCHEMA_* will update or add the keywords (that is, namespace, name, and so on) of or to the before and after image schemas.
  • MODIFY_FIELD _*$COLUMNNAME_* will update the fields in the before and after image schemas (that is, a field in this case would be your database column in which you can add/update their properties).
before.image.prefix and after.image.prefix
Image prefixes apply to all KCOPs without any special case. For KcopSingleRowAvroAuditIntegrated, adding this property adds the prefix your column names and the namespace of the after_image and before_image. For KcopJsonSingleRowAuditFormatIntegrated, adding this property adds the prefix to your column names.
For clarification, here are some sample lines for a presumed schema with the fully qualified name value.SOURCEDB.IIDR.IBMERS and field IMAGE2:
  • MODIFY_SCHEMA_value.SOURCEDB.IIDR.IBMERS_SET_NAMESPACE = x.y.z
  • MODIFY_SCHEMA_value.SOURCEDB.IIDR.IBMERS_SET_NAME = alpha
  • MODIFY_SCHEMA_value.SOURCEDB.IIDR.IBMERS_SET_DOC = sampleDoc
  • MODIFY_SCHEMA_value.SOURCEDB.IIDR.IBMERS_SET_IS-ERROR = true
  • MODIFY_SCHEMA_value.SOURCEDB.IIDR.IBMERS_SET_ALIAS-RP = ibmers,blues
  • MODIFY_SCHEMA_value.SOURCEDB.IIDR.IBMERS_SET_ATTRIBUTE =
"header1":"cool","header2":25,"header3":true
  • MODIFY_FIELD_value.SOURCEDB.IIDR.IBMERS$IMAGE2_SET_NAME = IMAGE3
  • MODIFY_FIELD_value.SOURCEDB.IIDR.IBMERS$IMAGE2_SET_ALIAS-AP = image2
  • MODIFY_FIELD_value.SOURCEDB.IIDR.IBMERS$image2_SET_ATTRIBUTE =
"fheader1":"cool","fheader2":25,"fheader3":true
Note: All of these properties are applicable to Avro KCOPs; however, only MODIFY_FIELD_value.SOURCEDB.IIDR.IBMERS$IMAGE2_SET_NAME = IMAGE3 and before.image.prefix and after.image.prefix are applicable to JSON KCOPs.