Making queries wait for synchronization updates

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 the delay in seconds. The range of possible values is between 0.0 and 3600.0 (a delay of one hour). A value of 0.0 means that WAITFORDATA is turned off and that queries will be executed immediately without delay. If you do not set a value for the CURRENT QUERY WAITFORDATA special register, then this default value of 0.0 is used.

Tip:

WAITFORDATA query processing causes a communication overhead. To compensate for this overhead, a timeout period of more than 8 seconds is recommended. Otherwise, queries might fail with a TIMEOUT error.

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 an error occurs in connection with WAITFORDATA processing, an error message with SQLCODE = -20581 and SQLSTATE 57019 is returned in the following format:

WAITFORDATA rsn=<reason code>:<reason code text>

where

<reason code>: Is an integer, which can be positive, negative, or 0
<reason code text>: Is the message text

Example:

SQLCODE=-20581, SQLSTATE=57019, SQLERRMC=WAITFORDATA 
rsn=2:Data synchronization state is invalid..;
/mnt/blumeta0/home/, DRIVER=4.22.29

Table 1. Error conditions that lead to a failure of WAITFORDATA queries
Reason code (`rsn`)	Message text	Common causes/solution
`1`	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.
`2`	Data synchronization state is invalid.	The data synchronization state of the Db2 Data Gate instance is not Started. Restart the Db2 Data Gate pod. This will start the synchronization process.
`5`	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.