Schema changes

Schema changes are supported in the sense that you need not remove, redefine, re-enable replication for, and finally reload an accelerator-shadow table after a schema change to an original Db2 for z/OS table. The schema change will be detected by the IBM Integrated Synchronization function, and will, if necessary, initiate a modification of the target accelerator-shadow table so that incremental updates can continue.

Note: The schema change feature works best if the workload is low. Remember that tables are modified structurally. A high number of queries running at the same time might lead to lock situations and timeouts for both, the queries and the schema-modifying DDL statements. As a consequence, tables are removed from the replication process, and queries end with an error.

Restrictions

While the schema change support includes the adding of all sorts of columns (even column types not supported by IBM Db2 Analytics Accelerator), the following restrictions exist none the less:
  • The schema change feature cannot detect RENAME TABLE operations because they are not recorded in the Db2 for z/OS log. This means that IBM Integrated Synchronization cannot track these operations to capture the changes. So if you have renamed a Db2 for z/OS table, you have to follow these steps:
    1. Remove the corresponding accelerator-shadow table from the accelerator.
    2. Add the table with the new name to the accelerator.
    3. Load the new table.
    4. Re-enable replication for the table.
  • Data-definition-language (DDL) modifications that effect a schema change cannot be written to the spill queue. That is, if a manual load operation suspends an incremental update that includes a schema change, the affected table will be set to the Error state, and incremental updates will not continue for this table (no draining of the spill queue).
  • 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 multiple statements for different operations on a table, such as ADD COLUMN and SET DATA TYPE.
  • If you try to handle DML (data manipulation language) and DDL statements in a single transaction, the transaction fails. For example, a transaction fails if you submit an ALTER TABLE ... ADD COLUMN ... statement and an INSERT, UPDATE, or DELETE statement for processing at the same time.
  • If you run a WAITFORDATA query on a table right after changing the table schema (ALTER TABLE), the query might end with SQLCODE -4742 and reason code 14 because the schema change had not been processed on the accelerator at the time the query started. Avoid running a WAITFORDATA query immediately after a schema change.
  • After 255 updates of the table version (data row format version), replication is disabled for a table. That is, replication is disabled after 255 schema changes if the version is updated after each change.
  • After the adding of a column, the table space of the affected table is in advisory REORG-pending (AREO) state. As long as a table space is in this state, a table that shares the table space cannot be enabled for replication. You must first lift the AREO state by running a REORG process. See the description of error AQT10131E.
  • If you use the schema change feature, the maximum string length that can be stored in VARCHAR and VARGRAPHIC columns in the accelerator database is lower. For VARCHAR columns, the limit is 32560 bytes. For VARGRAPHIC columns, it is 16280 bytes. For more information, see Supported data types.

Unsupported columns, table types, and operations

Schema changes are not supported under the following conditions:

  • Key columns (primary key, primary index, foreign key) are affected by the change.
  • Tables of the following types are involved:
    • Tables for which federated access has been configured
    • Accelerator-only tables
    • Tables that have been archived by the High Performance Storage Saver
  • Tables include 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)
  • Columns of data type ROWID are added.
  • Columns are dropped (DROP COLUMN operations).