Supported Operations with Automatic Schema Evolution

Automatic Schema Evolution is supported on Oracle, MySQL, PostgreSQL, SQL Server, Db2 LUW, DB2400, Teradata, and Oracle TimesTen targets.

Automatic Schema Evolution is supported on Oracle, MySQL, PostgreSQL, SQL Server, Db2 LUW, Db2 zOS, DB2400, Sybase, Teradata, Oracle TimesTen and Snowflake sources.

The following operations are supported:

The default Automatic Schema Evolution response can be overridden using AUTOSCHEMAOPTIONS. The following options are supported:
- TYPEMAP: Use this option to override the default datatype mapping
- IGNORE_DATA_TRUNCATION: If the default data type mapping foresees any possible data truncation, Replicat abends with appropriate error by default. To ignore these data truncation errors and to allow the Replicat to continue without abending, you can specify AUTOSCHEMAOPTIONS IGNORE_DATA_TRUNCATION in the Replicat parameter file.
- IGNORE_MODIFY_KEY_COLUMN: By default, Automatic Schema Evolution does not allow modifying the primary key column(s) and Replicat abends in such cases with an appropriate error. You can specify AUTOSCHEMAOPTIONS IGNORE_MODIFY_KEY_COLUMN to skip any modification(s) to the key column(s).
- ALLOW_MODIFY_KEY_COLUMN: By default, Automatic Schema Evolution does not allow modifying the primary key column(s) and Replicat abends in such cases. You can specify AUTOSCHEMAOPTIONS ALLOW_MODIFY_KEY_COLUMN to allow any modification(s) to the key column(s).
- MODIFY_COLUMN_NULLABILITY_ONLY: You can specify AUTOSCHEMAOPTIONS MODIFY_COLUMN_NULLABILITY_ONLY if they are interested only in modification to nullability of the column and not the type modification.

Automatic Schema Evolution supports the following operations based on the schema changes, detected from the metadata records in the trail files to keep the schema of the source and the target tables synchornized:

Creating the missing table on the target database
Altering the target table by performing ADD/MODIFY/DROP columns

Based on every source column metadata, by default the best-fit data type is chosen for the corresponding target column.

NOTE: Review the default data type mappings applicable for the specific source database to target database replication scenario from Data Type Mapping before implementing Automatic Schema Evolution.

The target data types may not be exactly same as the source data type in all the cases. Wherever possible, the choice of target data type is made such that the target type is always compatible and large enough (with same size as the source column or wider) to be able to accommodate the corresponding source column’s data. In some cases where there is no appropriate type available for corresponding source column data type on the target database (like the available types on the target database may cause data truncation), an error is returned to the user with an appropriate recommendation.

Creating the Missing Table

AutoSchema supports the following use-cases:

Create the missing table on the target while applying the trail data generated by CDC Extract.
Create the missing table on the target while applying the trail data generated by initial load Extract.
While applying the trail generated by the initialLoad Extract only, you can choose to drop the existing target table and create another Extract by specifying OPTYPE RECREATE as part of the DDL AUTOSCHEMA inclusion criteria.
If all the corresponding source key columns from the trail metadata are not nullable then Automatic Schema creates the target table with a PRIMARY KEY clause with all the corresponding columns.
If any of the corresponding source key columns, from the trail metadata, are nullable then Automatic Schema creates the target table with a UNIQUE KEY clause with all the corresponding columns.

Altering the Existing Table

If any columns are added/dropped/modified on the source, then the corresponding ALTER TABLE DDLs are generated and executed onto the corresponding mapped target tables. Automatic Schema supports modifications related to only the type (including data type name, length, precision, scale as applicable) and the nullability constraint. However, it does not honour any modifications to any other column attributes. If any type/nullability related schema changes are detected on the source columns, then they are propagated onto the target columns.

One or multiple schema changes on the source table may result in single or multiple ALTER TABLE DDLs on the mapped target tables, as supported on the specific target database. The number of DDLs on the target depends on the syntax supported on a specific target database such as in the following cases:

Few databases do not allow add/drop/modify of multiple columns in a single ALTER TABLE DDL.
Few databases do not allow modification of both type and nullability of column(s) in a single ALTER TABLE DDL statement.

In such cases the required number of ALTER TABLE DDL statements are executed on the target table, based on the target database requirements.

It is possible that Automatic Schema may impact the target database requiring further action. For example, if an ALTER TABLE MODIFY COLUMN can result in some data truncation, then different databases may handle this in different ways. A database may detect some existing data may be truncated by the operation and cause the Replicat to stop, which usually happens between Oracle and MySQL, when MySQL is running with strict mode enabled. However, if MySQL is configured in non-strict mode, then traction could occur with no warning.

For IBM i, depending on how the system is configured, the Replicat may appear to hang. For some actions on Db2 (formerly Db2 LUW) and Db2 z/OS, certain actions may require table reorganization. On Db2 z/OS, a drop column will not be recognized until a reorganization is performed, and for Db2 the table could end up in a reorganization pending state and stop allowing further operations against it.

NOTE: If required, you can selectively add OPTYPE ADDCOLUMN, OPTYPE DROPCOLUMN, and OPTYPE MODIFYCOLUMN to the DDL AUTOSCHEMA inclusion/exclusion criteria.