Limitations

Consider the following limitations if you are planning to use the Automatic Schema Evolution functionality:

• Replicat supports Automatic Schema only in Classic and Coordinated mode (Parallel mode is not supported).

• Automatic Schema is not supported for bidirectional replication.

• Automatic Schema is not applicable to the table used in the MAPEXCEPTION clause. Users must explicitly create the corresponding exceptions table before starting the replicat.

• Automatic Schema does not support COLMATCH parameter.

• Automatic Schema does not propagate source invisible/hidden columns from source to target.

• Automatic Schema does not honour the DDLSUBST parameter.

• Automatic Schema does not propagate all the indices or constraints, present on the source table, onto the corresponding target table.

• Automatic Schema does not honour any source column-level character set and/or collation. The corresponding column-level character set or collation is not associated with the target columns.

  • Automatic Schema does not associate any table-level character set with the target tables.

  • Automatic Schema does not associate any column-level character set or collation with the target columns.

    When using the Automatic Schema, make sure to check the character sets that are used on the source and the character sets that are available on the target system(s). Do not assume that the source character sets are necessarily available on the target system. This is especially true if the databases are not all using some form of Unicode. At this time when using Automatic Schema, the best compatibility with source database character sets is provided by ensuring that any target database is using UTF-8 or UTF-16 for its character columns. This is shown most distinctly when IBM Db2 databases are the source. For Db2 for z/OS and Db2 for i, the source data is often some form of EBCDIC and if the target is any other database family it is unlikely that those character sets are directly representable at the target. A similar concern can be made for ASCII character sets as well, since not all databases universally support them all.

  IMPORTANT: Using Unicode for the target database may require an expansion of the length of the character fields to ensure that the proper size is available when mapping from a source that is not Unicode itself. If the target database uses more number of bytes to represent a character than the number of bytes used by the source database, then users must use AUTOSCHEMAOPTIONS TYPEMAP for all such columns, and use a wider and compatible target data type to avoid any possible data truncation.

• Automatic Schema does not support the AUTO-INCREMENT type source columns, and does not create/alter the corresponding target columns with the same auto-increment properties.

• Automatic Schema may not create the primary/unique key correctly on the target table when the source MySQL table contains only unique key and no primary key. MySQL database that does not provide the correct key information.

NOTE: This is not some limitation imposed by Automatic Schema, but the MySQL source.

• If the source database is Oracle Database 19c, then Automatic Schema may not be able to derive the nullability of the target columns correctly.

NOTE: This is not an Automatic Schema limitation, but it’s the source Oracle Database 19c that does not provide the correct key information. Oracle source char and varchar2 type columns are always evaluated using byte semantics, even if the source DDL uses char semantics.

• If the Oracle source table has no primary key defined but has only a unique key containing any of the virtual columns, nullable columns, user-defined datatypes, extended long varchar columns, or function-based index columns, as valid key column(s), then Automatic Schema does not create such column(s) as primary/unique key on the target table.

• If the source is PostgreSQL database and the source table has any LOB columns as part of the key, then the users are required to specify DBOPTIONS MAXLOBKEYLEN <value> in their Extract parameter file. Otherwise, by default (without any AUTOSCHEMAOPTIONS TYPEMAP), Automatic Schema will create corresponding columns with zero length on the target.

• For source PostgreSQL Array Type, Automatic Schema creates CLOB type, or its equivalent for the corresponding target column. AUTOSCHEMAOPTIONS TYPEMAP must be specified to select the suitable target type, based on source PostgreSQL Array column name.

• For Oracle source CLOB columns with multi-byte database character set, NCLOB or equivalent target type gets chosen for the target column, by default. To override, TYPEMAP must be used.

• For Db2 for z/OS, replicating UTF-16 data requires an EBCDIC or Unicode database. Other double byte encodings are not explicitly supported.

• For IBM Db2 (formerly Db2 for LUW) databases replicating UTF-16 data requires a Unicode database. Other double byte encodings are not explicitly supported.

• For source ENUM type columns, Automatic Schema applies char as the target type on MySQL. Use TYPEMAP, if required, to use VARCHAR instead.

• AUTOSCHEMAOPTIONS TYPEMAP must be specified based on source column name, and not source data type name, for source numeric/decimal columns with precision less than 8.

• AUTOSCHEMAOPTIONS TYPEMAP must be specified based on the source column name, and not based on the source data type name for source columns of types:
  • Char/Varchar type columns with length > 4000.
  • TIME(p)
  • TIME(p) WITHOUT TIME ZONE
  • TIME(p) WITH TIME ZONE
  • TIMESTAMP(p)
  • TIMESTAMP(p) WITHOUT TIME ZONE
  • TIMESTAMP(p) WITH TIME ZONE
  • All INTERVAL variants
  • Source SQL Server Vector type columns

• Automatic Schema does not support using the COLMAP clause with column names containing double-quotes within them. The target column name used in a specific column mapping in the COLMAP clause, which is mapped to one source column name, cannot be same as any other column name in the source table.

• Few of the Automatic Schema error messages may appear in ASCII when the locale of the system where GoldenGate is running, is multibyte character set, such as Chinese or Japanese (for example, zh_CN.UTF-8).

• On SQL Server target, for any source datetime/timestamp type with no fractional seconds, you must specify the AUTOSCHEMAOPTIONS TYPEMAP for the corresponding source type (or column name) with datetime2(0) as the specified replacement target type.

• On Db2 z/OS target, Automatic Schema does not support dropping of multiple columns using single ALTER TABLE DDL. You can re-run the failed DDL that is reported in the OGG error (from the Replicat report file) and restart the Replicat.

• On SQLServer target, Automatic Schema does not correctly detect/execute the column modification from VARCHAR(n) to TEXT or VARCHAR(max).

• AUTOSCHEMAOPTIONS TYPEMAP does not work for target type specifications:
  • NUMBER (p,s)
  • NUMERIC(p,s)
  • DECIMAL(p,s)
  • TIMESTAMP(p) with time zone
  • TIME(p) with time zone: Workaround is to use TIME(p) as the target type name specification instead of TIME(p) without time zone because both are semantically equivalent.
  • TIMESTAMP(p) without time zone As a workaround, you can use TIMESTAMP(p) as the target type name specification instead of TIMESTAMP(p) without time zone because both are semantically equivalent.
  • Varbinary(max)
  • Varchar(max): Workaround is to use CLOB as the target type name specification instead of varchar(max)
  • Nvarchar(max): Workaround is to use NCLOB as the target type name specification instead of nvarchar(max)

• On SQLServer target, specifying the Vector dimension in the type specification is mandatory. Specifying the base type (like float16 or float32) is optional.

• For SQLServer source columns of type DATETIME, Autoschema cannot distinguish between DATETIME and DATETIME2(3). So, while using explicit TYPEMAP clause, you must configure a source column name-based TYPEMAP instead of source type name-based typemap.

• For DB2AS400 target NCHAR columns, Automatic Schema does not correctly detect the required column type modifications.

• If any Oracle GoldenGate Delivery on PostgreSQL (or PostgreSQL compatible) database is configured to use PostgreSQL native ODBC driver, then Automatic Schema does not detect the Alter Table Modify Column cases correctly.

• For IBM Db2 for i, it is possible that a job may appear to “hang” and make no progress. For example, this is more likely on the replicat for when Automatic Schema is used if the requested operation may cause a data truncation issue. Depending on how the IBM i system is configured, the replicat job could be put into a Message Wait (MSGW) state where the system is waiting for a user to provide what action the job should take to resolve the specific issue. Currently there is no way for GoldenGate to address this directly, and you must handle it on the IBM i side.

• For the source Snowflake column types of TIMESTAMP_NTZ(p) or TIMESTAMP_LTZ(p) or TIMESTAMP_TZ(p), Automatic Schema maps them to the corresponding timestamp columns but with a date time precision of 0. To avoid data truncation of the fractional seconds, you must use AUTOSCHEMAOPTIONS TYPEMAP to select the required type on the target.

• Automatic Schema does not differentiate PostgreSQL source JSON and JSONB column types. You must use AUTOSCHEMAOPTIONS TYPEMAP based on source column name instead of column type for PostgreSQL source JSONB types.

• Automatic Schema does not differentiate between PostgreSQL source unbounded numeric column (numeric with no precision and scale) and PostgreSQL source numeric (1000, 100) types. You must use AUTOSCHEMAOPTIONS TYPEMAP based on source column name instead of column type for PostgreSQL source numeric(1000,100) type columns.

• For the Snowflake source column types of ARRAY, GEOGRAPHY, and GEOMETRY, Automatic Schema maps to JSON type (if supported) or CLOB type on the target. To select a different type on the target, users must use AUTOSCHEMAOPTIONS TYPEMAP.

• The informative message(s), containing source column name based specifications part of AUTOSCHEMAOPTIONS TYPEMAP is not displayed correctly.