Making queries wait for Db2 Data Gate synchronization updates (WAITFORDATA)

You can postpone the execution of queries against tables in Db2 Data Gate target databases until the latest synchronization updates have been applied.

About this task

Tables in Db2 Data Gate target databases (Db2 or Db2 Warehouse) are synchronized with tables in Db2 for z/OS®.

The synchronization process runs asynchronously, and synchronization updates are applied in batches. This causes a data recency gap (latency) between the tables in the source database and the tables in the target database. That is, the data in the source database is usually newer than the data in the target database. The gap is zero or minimal shortly after synchronization updates have been applied to the target database. After that, the gap widens again.

If it is a requirement that queries against the target database retrieve data that is as up-to-date as possible, then the best point in time to do this is shortly after the latest batch of updates has been applied. For that reason, a function has been added to Db2 Data Gate that always postpones the execution of queries to such a point in time.

To cause such a delay, you have to add a special register setting (SET CURRENT QUERY WAITFORDATA) at the beginning of the SQL query that is submitted against the target database.

WAITFORDATA delays are valid for synchronized tables only. The thinkable use cases are thus handled as follows:

Only synchronized tables are referenced by a query
Query execution is delayed until all referenced tables are synchronized.
Synchronized and non-synchronized tables are referenced by a query
The non-synchronized tables are ignored. Query processing starts when the synchronized tables have been updated within the WAITFORDATA delay period.
Only non-synchronized tables are referenced by a query
The WAITFORDATA delay is ignored and query processing starts immediately.
A query references no tables at all
The delay will be applied none the less, so query processing will be attempted or start after the delay period.

A WAITFORDATA delay period is only considered for SQL queries that read data from tables. Other queries or operations, such as DDL operations, will not be delayed.

Procedure

At the beginning of a SQL query that is submitted against the target database (by an application, ODBC connection, and so on), add the following special register setting: 
SET CURRENT QUERY WAITFORDATA = n.m;

where n.m is a decimal number denoting seconds. The minimum is 0.0 (no delay). The maximum is 3600.0 (a delay of one hour)

Example: 
SET CURRENT QUERY WAITFORDATA = 120.0;
SELECT * FROM XYZ WHERE [...];

The first line sets the delay of the query execution to 120 seconds. What follows is the SQL code for the query. The execution of the query will be postponed for up to 120 seconds. For all synchronized tables that are referenced by the query, Db2 Data Gate tries to synchronize all committed changes that have been accumulated in the Db2 for z/OS source database at the time the query started. The query will be executed as soon as all required committed changes have been synchronized. If Db2 Data Gate cannot finish the data synchronization during that period, the query fails with a timeout error.

Results

If the synchronization job can be completed during the delay period set by the SET CURRENT QUERY WAITFORDATA special register, the query is executed, and the result is returned as usual.

If the query fails, one of the error conditions in Table 1 might apply:

Table 1. Error conditions that lead to a failure of WAITFORDATA queries
Reason Message text Common causes/solution
SUBSCRIPTION_NOT_RUNNING Data synchronization state is invalid. The data synchronization state of the Db2 Data Gate instance is not Started.
To change the synchronization state:
  1. Start the Db2 Data Gate web UI.
  2. Open your Db2 Data Gate instance to display the dashboard.
  3. On the Overview tab of the dashboard, you can see whether synchronization is enabled.

    If synchronization is disabled, the dashboard shows Synchronization stopped, and the toggle button next to this label is set to Off.

  4. In this case, click this button to change the setting to On. This will start the synchronization process.
INVALID_TABLE_STATE Tables are in an invalid state. This condition affects tables in the Error state. It does not apply to tables that are not enabled for synchronization, which are simply ignored by WAITFORDATA processing.

If this error occurs, reload the affected tables.
TIMEOUT Timeout for WAITFORDATA reached. Pending synchronization updates could not be applied within the WAITFORDATA period.

Rerun the query. If it fails again, try a longer WAITFORDATA delay.