IBM Integrated Synchronization

IBM Integrated Synchronization is a built-in product feature that makes it easy to set up incremental updates. It does not require IBM InfoSphere® Change Data Capture for z/OS® (CDC), which is, although bundled with IBM Db2 Analytics Accelerator, an extra product. IBM Integrated Synchronization therefore requires fewer customization and activation steps.

In addition, IBM Integrated Synchronization has further advantages:

  • Reduced complexity.
  • Updates are processed very fast.
  • Reduced CPU consumption on the mainframe because most of the processing is done on the client side (accelerator).
  • On the mainframe, the workload can be handled by IBM z Systems® Integrated Information Processors (zIIPs).
  • Speedier support case processing through better support channel integration

Compatibility

You need not remove existing CDC implementations as long as they serve different Db2 subsystems. A single accelerator can be the common replication target of different Db2 subsystems using either CDC or IBM Integrated Synchronization. However, this type of setup is not recommended.

Feature prerequisites

To use IBM Integrated Synchronization, you need:

  • Db2 12 for z/OS with the latest fix for APAR PH06628 (PTF UI63356 at the time of this writing) installed, running at function level V12R1M500. You can retrieve a list of all recommended fixes from the Prerequisites and Maintenance website.
  • Distributed data facility (DDF) with a secure port, configured for network encryption through AT-TLS
  • For data sharing groups: ability to always connect to the same member using one of the following options:
    • A member-specific secure port that has a local alias
    • The high-availability setup for data sharing groups by means of DDVIPA and the TIMEDAFFINITY option. See Suggestion for a high-availability setup.
  • IBM Db2 Analytics Accelerator for z/OS Version 7.5.0 or later.

Prerequisites for tables and hints

  • Tables to be included must have a unique constraint (primary key or primary index). If such a key does not exist in the accelerator-shadow table or cannot be determined, you must redefine the table and specify such a key. You cannot do this from IBM Db2 Analytics Accelerator Studio; you must run the corresponding stored procedure (SYSPROC.ACCEL_ADD_TABLES) with a special option. The columns that you choose for the key must contain unique values or form such values when they are combined. The values of the selected key columns will then be written to a hidden column in the accelerator-shadow table. This column, called informational unique constraint, then serves as the primary key or primary index. For more information, see the entry about the table_specifications parameter of the SYSPROC.ACCEL_ADD_TABLES stored procedure.
  • If you update Db2 for z/OS tables by running the LOAD utility rather than an INSERT, UPDATE, or DELETE operation, you must set the following parameters (for the LOAD utility):
    • SHRLEVEL CHANGE
    • LOG YES
    Otherwise, the changes that were made by the LOAD utility are not detected by the incremental update function, and will thus not be reflected in your accelerator-shadow tables.
  • The Db2 source tables of your accelerator-shadow tables have an attribute named DATA CAPTURE. The attribute can carry the value Y or N (default), for yes or no. When incremental updates are enabled for a table, the DATA CAPTURE attribute of the table is set to the value Y. Once set, this attribute value persists, even if the table is disabled at a later time. Bear this in mind, especially if you run applications that use the DATA CAPTURE attribute.

    The DATA CAPTURE attribute is set by an ALTER TABLE statement, which is run as part of the SYSPROC.ACCEL_SET_TABLES_REPLICATION stored procedure. However, the attribute can only be set successfully if the ID of the user who runs the stored procedure has ALTER TABLE authorization. If this is not the case, a database administrator must set the attribute for all tables in Db2 for z/OS.

  • You might have to reload or even remove tables from an accelerator after the execution of an ALTER TABLE or ALTER TABLESPACE statement in Db2 for z/OS. For more information, see What to do after changing a table or table space in Db2 for z/OS

Intervention-free handling of faulty tables

A table in error is suspended from the replication process. This means updates are no longer applied and therefore the table data might be outdated.

To prevent queries from running against suspended tables, query acceleration must be disabled for these tables. This is done automatically, and each such event is reported as a warning in the event viewer for incremental updates. An intervention on your part is not required.

All that is required as a preparation is that a few extra privileges are granted. If IBM Integrated Synchronization is already installed, have a look at the steps 4 and 6 in the section Assigning user IDs. These steps describe how to grant the required privileges.

Important:
  • If the required privileges are missing, IBM Integrated Synchronization cannot be enabled for a Db2 subsystem.
  • For already enabled Db2 subsystems, missing privileges lead to error messages in the event viewer for incremental updates. These messages will state that query acceleration could not be disabled.

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.

While the schema change support includes the adding of all sorts of columns (even column types not supported by IBM Db2 Analytics Accelerator), a few restrictions exist none the less:
  • 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).
  • Schema changes are not supported if key columns are affected by the change.
  • Schema changes are not supported for tables of the following types:
    • Tables for which federated access has been configured
    • Accelerator-only tables
    • Tables that have been archived by the High Performance Storage Saver
  • 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.
  • 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.
  • 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 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

  • It is not possible to enable tables if the table name or the schema name contains GB18030 characters (Simplified Chinese) of Unicode plane 2 (U+20000-U+2FFFF: Supplementary Ideographic Plane). Trying to do so results in an error.
  • To be able to load or reload replication-enabled tables, replication must be started. Loading or reloading replication-enabled tables does not work if replication is stopped. In the unlikely situation that a replication-enabled table prevents a replication start, proceed as follows:
    1. Disable replication for this table.
    2. Remove the table from the accelerator.
    3. Restart replication.
    4. Redefine (re-add) he table to the accelerator.
    5. Re-enable replication for this table.
    6. Load the table.
  • You cannot enable incremental updates for accelerator-archive tables to replicate the data of non-archived partitions.

Next steps