Support for schema changes in Db2 Data Gate tables

In earlier versions of Db2 Data Gate, the synchronization cycle broke when the structure of a Db2 for z/OS® table changed (schema change), for example, because a column had been added to the table. The table was suspended from the synchronization process and the table state was changed to Error. To get the table back into the synchronization process, you had to remove, redefine, and reload the table for the Db2 Data Gate service. This is no longer necessary.

Schema changes will be detected by the synchronization function of Db2 Data Gate. This function will, if necessary, initiate a modification of the target table so that the synchronization of the table can continue.

While the schema change support includes the adding of all sorts of columns (even column types not supported by Db2 Data Gate), a few restrictions exist none the less:
  • Manual load operations suspend the synchronization of a table until the load has finished. However, if schema changes are part of the synchronization changes, then these changes are not kept and applied when the synchronization process continues. Instead, the affected table goes into Error state, and synchronization does not continue for this table.
  • Schema changes are not supported if key columns are affected by the change.
  • Schema changes are not supported for columns of the following data types:
    • Distinct types (user-defined types based on existing, built-in Db2 for z/OS data types)
    • Large object types (BLOB, CLOB, or DBCLOB)
  • Mixed DDL modifications cannot be handled, only modifications of the same type can. That is, an ALTER TABLE statement that adds two columns to a table is allowed, but not a statement that performs two different operations on a table, such as ADD COLUMN and SET DATA TYPE.
  • DROP COLUMN schema changes are not supported.
  • After 255 updates of the table version (data row format version), synchronization is disabled for a table. That is, synchronization is disabled after 255 schema changes if the version is updated after each change.
  • You cannot add columns of data type ROWID.
Note: The schema change feature works best if the workload is low. Remember that tables are modified structurally. A high number of table accesses at the same time might lead to lock situations and timeouts for both, the access requests and the schema-modifying DDL statements. As a consequence, tables are removed from the synchronization process, and access requests end with an error.