Isolation levels

Db2 isolation levels are ignored by IBM Db2 Analytics Accelerator.

Every table on an accelerator has its counterpart. That is, the table has been derived from a table that resides in Db2 for z/OS. An accelerator-shadow table is thus a copy of a projection of a Db2 table. In most cases, the projection is the Db2 table itself. Columns of the original Db2 table are excluded from the projection only if a column uses an unsupported data type.

You can change a Db2 table practically at any time by means of INSERT, UPDATE, or DELETE operations, mass imports (LOAD), and schema modifications (selected DDL). In this version of IBM Db2 Analytics Accelerator for z/OS, changes to a DB2® table are propagated automatically to the associated accelerator-shadow table only if the incremental update feature is used. If incremental updates are not enabled, you must update the accelerator-shadow tables manually in order to synchronize the table data. You can start the update from IBM Db2 Analytics Accelerator Studio or run the SYSPROC.ACCEL_LOAD_TABLES stored procedure for this purpose.

The update of accelerator-shadow tables is an asynchronous process, that is, there is no guarantee that the data in the Db2 tables and the data in the accelerator-shadow tables are always in sync. Even with incremental updates, a certain degree of latency is involved, which means that tables on an accelerator are updated somewhat later than the original DB2 tables. Therefore, the result set that a query returns when it is processed by Db2 might differ from the result set that the same query produces when it is processed by IBM Db2 Analytics Accelerator. This implies that isolation levels set for Db2 data are ignored or must be ignored by applications that send queries to IBM Db2 Analytics Accelerator. Result sets that are returned by IBM Db2 Analytics Accelerator do not necessarily comply with any isolation level setting.

To ensure data consistency on a relatively safe level, update accelerator-shadow tables immediately before you run the query and choose an appropriate lock scope so that the affected Db2 tables cannot be altered during the update. This, however, still does not guarantee that the results of an accelerated query will show the latest changes because the lock might prevent or postpone an update of the original Db2 table data.

If you use the incremental update function, you can ensure that committed Db2 for z/OS changes are propagated to the accelerator by calling the ACCEL_CONTROL_ACCELERATOR stored procedure with the <waitForReplication> command.

If the most recent table changes must be reflected in the result set, set the CURRENT QUERY ACCELERATION special register to the value NONE. This means that the query is executed by Db2 and will not be accelerated.