Configuring the expiration behavior of WAITFORDATA queries

In the context of Hybrid Transactional Analytical Processing (HTAP), you can determine whether WAITFORDATA queries are aborted or executed despite the fact that not all incremental updates could be applied within the specified period.

About this task

WAITFORDATA is an option (specified as a special register, ZPARM, or bind option) that can be used for queries against incrementally updated tables. It effects a hold on the query execution until the most recent updates (changes committed in Db2 for z/OS) have been applied to the tables referenced by the query. For a more detailed description, follow the link to Making queries wait for incremental updates at the end of this topic.

Query execution will be stopped for the period specified by the WAITFORDATA option. During that time, it is expected that all pending incremental updates can be applied. You can specify how to proceed with a query in case the updates cannot be applied within the time allotted for this task:

FAIL: This setting leads to the failure and the subsequent abortion of the query. It is the default.
CONTINUE: This setting effects the execution of a query despite the fact that the updates could not be applied within the specified time. It bears the risk that the query is run on obsolete data and hence delivers results that are not up-to-date.

Procedure

Log on to the IBM Db2 Analytics Accelerator Console.

Press the Pause key, then Enter to display the following screen:

*****************************************************************************
*         Welcome to the IBM Db2 Analytics Accelerator Console
*****************************************************************************

You have the following options:
                                                
 (1) - Change the Configuration Console Password                                
 (2) - (Menu) Run Netezza Commands                                              
 (3) - (Menu) Run Accelerator Functions
 (4) - (Menu) Manage Hardware                                                   
 (5) - (Menu) Manage Incremental Updates 
 (6) - (Menu) Manage 'Call Home'
 (7) - (Menu) Manage Encryption
                                                                                
 ----------------------------------------------------------------------         
 (x) - Exit the Configuration Console

Type 5 and press Enter to display the submenu:

 main -> 5                                               
 --------------------------------------------------------
 You have the following options:                         
                                                         
(0) - Go back one level                
(1) - Enable incremental updates       
(2) - Disable incremental updates      
(3) - Update Db2 subsystem credentials 
(4) - Restart replication processes    
(5) - (Menu) Define keys automatically
(6) - Configure parallel apply
(7) - Configure continuous replication
(8) - Suspend faulty tables
(9) - Clean up capture agent catalog
(10) - Disable query acceleration for suspended tables
(11) - Change Db2 group IP and DRDA port
(12) - Change Capture Agent IP address and TCP port on z/OS
(13) - Change the delay expiration behavior for WAITFORDATA queries
(14) - Include or exclude tables not enabled for incremental updates 
       in WAITFORDATA queries



(Default 0) >

Type 13 and press Enter.

The following screen is displayed:

Specify which action to take after the passing of the delay for queries
that were run with the WAITFORDATA option.

FAIL
means that the query will abort with SQLCODE -904 if the delay expired 
before the table data on the accelerator could be updated.

CONTINUE
means that the query will run on the accelerator after the passing of 
the delay despite the absence of current data. The query might thus run 
on obsolete data. Passing of the delay is indicated by SQLCODE +904. This SQL 
warning will only show if your application program fetches the entire 
result set and propagates warnings to end users.

The current setting is: FAIL

Enter your choice ('fail', 'continue', or <return> to cancel): <cursor>

To change the value from FAIL to CONTINUE or vice versa, type c or f at the prompt (position of <cursor> in the screen extract). Then press Enter.
To exit the dialog without changes, just press Enter.
After a change or confirmation of the present value, you see the following message:
```
Done. The new setting is now active and will be used with the next query 
that is offloaded to the accelerator.
```
Press Enter again to return to the submenu.

What to do next

To change your settings, repeat steps 4 through 6. Specify a different value in step 5.