Updating accelerator tables continually
The incremental update function of IBM Db2 Analytics Accelerator for z/OS allows you to update accelerator-shadow tables continually. Changes to the data in original Db2 for z/OS tables are thus propagated to the corresponding target tables with a high frequency and just a brief delay. This way, query results from an accelerator are always extracted from recent, close-to-real-time data.
The information about changes to be transferred comes from the Db2 for z/OS log.
In the past, data in accelerator-shadow tables could only be updated by use of the table load function. With this function, you can only update entire tables and entire table partitions, and the latter only if the tables are partitioned by range or partitioned by growth. The incremental update function, in contrast, updates individual table rows. Advantages and disadvantages are connected with either method. Table 1 compares the advantages and disadvantages.
| Manual update of entire table using load function | Manual update of entire partitions using load function | Incremental update |
|---|---|---|
| For all types of tables | Only for range-partitioned tables and tables partitioned by growth | For all types of tables |
| Potentially long-running process, depending on the size of the tables | Potentially long-running process, depending on the size of partitions and the distribution of data changes across partitions (the more partitions are involved, the more time-consuming the update becomes) | Short-running process after initial load |
| Fast (up to one TB per hour) | Fast (up to one TB per hour) | Slower (in the range of GBs per hour) |
| Redundant load of table rows that have not changed, resulting in waste of processing resources (MIPS) | Redundant load of table rows that have not changed, resulting in waste of processing resources (MIPS) | No redundant load of table rows, no waste of processing resources. |
| Just a snapshot of the original data at some point in the past | Just a snapshot of the original data at some point in the past | Continual updates, recent data |
Use cases
Incremental updates are the preferable method with regard to the following points of interest:- Realtime business intelligence
- If real-time data is very important for your business intelligence operations, incremental updates are the preferable choice. Incremental updates do not guarantee that query data is the most recent available, but the latency is low, and this makes incremental updates a cost-efficient alternative.
- Quick reporting on operational data
- Incremental updates provide an uncomplicated, easy-to-use, and fast way to reporting on data in an operational data store (ODS) because the function is deeply integrated into your Db2 for z/OS and IBM Db2 Analytics Accelerator for z/OS setup. No up-front extraction and transformation of data is required to obtain results.
- Cost-efficiency
- Incremental updates are more cost-efficient than traditional update methods because there is no
redundancy during updates and hence no waste of processing resources.
In addition, internal tests have shown that running incremental update processes affect the query response time only marginally.
- High availability
- Incremental updates can be configured in such a way that data changes in a Db2 for z/OS table are propagated to more than one accelerator-shadow table on multiple accelerators. If one accelerator fails, another one can take over to process incoming queries. This happens with just a short delay.