Making queries wait for Data Gate synchronization updates (WAITFORDATA)

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

About this task

Tables in 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 Data Gate that always postpones the execution of queries to such a point in time.

To cause such a delay, you must 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 an 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 is postponed for up to 120 seconds. For all synchronized tables that are referenced by the query, Data Gate tries to synchronize all committed changes that are accumulated in the Db2 for z/OS source database at the time the query started. The query is executed when all required committed changes are synchronized. If 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 Data Gate instance is not Started.
To change the synchronization state:
  1. Start the Data Gate web UI.
  2. Open your 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 starts 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 ignored by WAITFORDATA processing.

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

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