Types of accelerator tables
There are three types of accelerator tables.
- Accelerator-only table
- An accelerator-only table is automatically created in the accelerator when the CREATE TABLE
statement is issued on DB2® with the IN
ACCELERATOR clause. The table and column definitions of the accelerator-only table are reflected in
the DB2 catalog with a D in the TYPE column
of SYSIBM.SYSTABLES.
You can specify an alias (logical name) when defining an accelerator. For more information, see Using an alias for an accelerator.
Any queries that reference an accelerator-only table must be executed in the accelerator and will be accelerated. If the query is not eligible for query acceleration, an error is returned.
A data change statement for an accelerator-only table must be executed in the accelerator. If the data change statement type is not supported by the accelerator, or if the statement contains any expression that is not supported by the accelerator, an error is returned.
When validate run behavior is in effect, a static query or data change statement that references an accelerator-only table is incrementally bound at run time. A static query or data change statement that references an accelerator-only table is eligible for acceleration during an incremental bind only if the QUERYACCELERATION bind option that is in effect is ENABLE or ELIGIBLE. When a static query or data change statement that references an accelerator-only table is issued and the QUERYACCELERATION bind option that is in effect is ALL, an error is returned.
Running queries and data change statements on the accelerator can significantly speed up SQL statements, such as INSERT from SELECT statements. This is not possible with the other two types of accelerator tables. Accelerator-only tables are used by statistics and analytics tools, which can quickly gather all the data that is required for reports. Because the data in these tables can be modified so quickly, they are also ideal for data-preparation tasks that must be completed before the data can be used for predictive modeling.
- Accelerator-shadow table
- An accelerator-shadow table exists both in DB2 and in the accelerator. The table in the accelerator contains all or a subset of the columns in the DB2 table. After the table is defined on the accelerator, you can load data into the table on the accelerator by copying data from the original DB2 table to the corresponding table on the accelerator. After the data is loaded on the accelerator, you can enable query acceleration for this table by using one of the following methods: QUERY_ACCELERATION subsystem parameter, CURRENT QUERY ACCELERATION special register, QUERYACCELERATION bind option, or connection properties (JDBC and ODBC) and profile tables.
- Accelerator-archived table
- An accelerator-archived
table is a table on the accelerator for which partitions of a DB2 table, or the entire table, are archived on the
accelerator. When the data is archived on the accelerator, the original DB2 data is deleted. The table space for a DB2 table that is associated with an archived
accelerator table is set to a permanent read-only state so that the original DB2 table can no longer be changed. An image copy of
the data is also created as part of the archive, to enable recovery of the data in an emergency
situation. The recovery function in IBM®
DB2 Analytics Accelerator for z/OS® uses the image copies to restore the data in the original DB2 table.
Accelerator-archived tables are used primarily for historical data that is no longer actively used or maintained. The archive saves storage space on IBM Z. Normally, the archived data is not included in an accelerated query. However, you can specify that archived data is to be included in an accelerated query by using one of the following methods: QUERY_ACCELERATION subsystem parameter, CURRENT QUERY ACCELERATION special register, QUERYACCELERATION bind option, and connection properties (JDBC and ODBC) and profile tables.
For certain scenarios, query results for the accelerator tables might differ from results that are executed in DB2. For more information, see the information about restrictions in IBM DB2 Analytics Accelerator for z/OStables.
