Accelerator-only tables and in-database transformation

Accelerator-only tables are tables that do not originate from Db2® base tables. Their data exists only on an accelerator. Data in accelerator-only tables can only be modified by data-manipulation (DML) statements (INSERT, UPDATE, and DELETE). With accelerator-only tables, you can do in-database transformation (IDT) in a more efficient way.

Queries and data-manipulation statements against accelerator-only tables are always routed to an accelerator. If a SQL statement does not satisfy the conditions for acceleration, the statement fails. Furthermore, DML statements that are run on an accelerator can only manipulate data in accelerator-only tables.

Accelerator-only tables are useful for statistics and analytic tools that employ temporary data for reports because the high velocity of execution allows these tools to gather all required data quickly. Since the data in these tables can be modified fast, they are also ideal for data-preparation tasks that must be completed before the data can be used for predictive modeling.

Use accelerator-only tables if you want to accelerate queries that access previously calculated, interim result sets. To take full advantage of the product's high-speed capabilities, store all your interim results in accelerator-only tables. This allows subsequent queries or data transformations to process all relevant data on the accelerator.

To create an accelerator-only table, you submit a CREATE TABLE statement in Db2 that uses the IN ACCELERATOR <acceleratorname> clause. Accelerator-only tables are registered in the Db2 catalog with a type of 'D'.

Accelerator-only tables cannot be loaded by the SYSPROC.ACCEL_LOAD_TABLES stored procedure or by the corresponding function in IBM Db2 Analytics Accelerator Studio. You have to use INSERT statements. These can be submitted from interactive client interfaces (SPUFI, IBM Data Studio), or from client application programs using ODBC, JDBC or static SQL. It is possible to transfer many rows in a single batch or transaction (multi-row insert). However, the multi-row insert support is limited to client application programs and cannot be used interactively.

Important:

The use of accelerator-only tables brings extra workloads to your accelerators that compete for the same resources, such as CPU, disk, and system memory. This might impact the stability of your production environment.

Conventional in-database transformation versus in-database transformation with accelerator-only tables

A conventional approach to in-database transformation might consist of the following steps:

  1. CREATE TABLE T1 …;
  2. INSERT INTO T1 SELECT … FROM T0 …;
  3. SELECT … FROM T1 …;
  4. DROP TABLE T1;

Step 1 creates an interim table. Interim results that were calculated on the basis of the data in T0 are stored in table T1 (see step 2). In step 3, a second query reads the interim table T1 to calculate the final result. In step 4, the interim table T1 is dropped because it is no longer needed.

To accelerate the steps above, create table T1 in step 1 as an accelerator-only table. This accelerates the final (second) query and avoids the movement of data in Db2, which occupies considerable processing resources if T1 is large. If T1 is an accelerator-only table, the INSERT statement in step 2 is run on the accelerator rather than in Db2. You also omit the duplication of table T1 in Db2 because you do not need to run IBM Db2 Analytics Accelerator stored procedures to define, load, and enable table T1 on the accelerator. This is a big advantage because many extract-transform-and-load (ETL) tools that generate data transformation statements automatically do not support stored procedure calls. Using accelerator-only tables, you have to modify the statements generated by these tools only slightly to end up with accelerated in-database transformation steps that do not require stored procedure calls.

Before running the following modified steps, you must define, load and enable acceleration for table T0 on the accelerator by using the IBM Db2 Analytics Accelerator stored procedures or the corresponding functions in IBM Db2 Analytics Accelerator Studio. This is just a one-time preparation step. Then you continue with the following steps:

  1. CREATE TABLE Q1.T1 … IN ACCELERATOR … IN DATABASE …;
  2. INSERT INTO Q1.T1 SELECT … FROM T0 …;
  3. SELECT … FROM Q1.T1 …;
  4. DROP TABLE Q1.T1;
Notes:
  1. Depending on your situation, you might want to keep the accelerator-only table for later use and run step 4 at a later time.
  2. In contrast to temporary tables, accelerator-only tables are much more like normal Db2 tables. It is therefore recommended that you create these tables in an authorized database by using a CREATE TABLE statement with the IN DATABASE clause. Otherwise, accelerator-only tables are created in an implicit database, just like normal Db2 tables. You might also want to add an explicit schema qualifier to the table name.

The following examples consist of extended data transformations that use more than one interim table. They show how to change conventional Db2 statements to arrive at an accelerated solution to in-database-transformation. The left column shows the conventional approach. The right column shows the in-database-transformation steps that include accelerator-only tables.

Example 1

The Db2 tables T1, T2 and T3 already exist. The conventional approach uses two declared global temporary tables to store the interim results.

Table 1. Example 1: Declared global temporary tables versus accelerator-only tables
  Conventional approach (Db2) Accelerator-only tables
One-time preparation N/A Define and load the tables T1, T2 and T3 on accelerator ACCEL1 by using stored procedures or the corresponding functions in IBM Db2 Analytics Accelerator Studio.
Step 2
DECLARE GLOBAL TEMPORARY TABLE 
   SESSION.TEMP1 (
   C11 CHAR(2), ...)
   ON COMMIT PRESERVE ROWS;

INSERT INTO SESSION.TEMP1
SELECT ... FROM T1 ...;

DECLARE GLOBAL TEMPORARY TABLE 
   SESSION.TEMP2 (
   C21 CHAR(2), ...)
   ON COMMIT PRESERVE ROWS;

INSERT INTO SESSION.TEMP2 
   SELECT ... FROM T2 ...;

SELECT ... FROM
   FROM SESSION.TEMP1 ...
        SESSION.TEMP2 ...
        T3 ...;

DROP TABLE SESSION.TEMP1;
DROP TABLE SESSION.TEMP2;

SET CURRENT QUERY ACCELERATION = ALL;

CREATE DATABASE DBTEMP;

CREATE TABLE TEMP1 (
   C11 CHAR(2), ...)
   IN ACCELERATOR ACCEL1 
   IN DATABASE DBTEMP;

INSERT INTO TEMP1
   SELECT ... FROM T1 ...;

CREATE TABLE TEMP2 (
   C21 CHAR(2), ...)
   IN ACCELERATOR ACCEL1 
   IN DATABASE DBTEMP;

INSERT INTO TEMP2
   SELECT ... FROM T2 ...;

SELECT ... FROM
   FROM TEMP1 ...
        TEMP2 ...
        T3 ...;

DROP TABLE TEMP1;
DROP TABLE TEMP2;
DROP DATABASE DBTEMP;

Example 2

The Db2 tables T1 and T2 already exist. The final transformation result is stored in the existing Db2 table T3. The conventional approach uses two declared global temporary tables to store the interim transformation results.

Table 2. Example 2: Declared global temporary tables versus accelerator-only tables
  Conventional approach (Db2) Accelerator-only tables
One-time preparation N/A Define and load the tables T1 and T2 on accelerator ACCEL1 by using stored procedures or the corresponding functions in IBM Db2 Analytics Accelerator Studio.
Step 2
DECLARE GLOBAL TEMPORARY TABLE
   SESSION.TEMP1 (
   C11 CHAR(2), ...)
   ON COMMIT PRESERVE ROWS;

INSERT INTO SESSION.TEMP1
   SELECT ... FROM T1 ...;

DECLARE GLOBAL TEMPORARY TABLE
   SESSION.TEMP2 (
   C21 CHAR(2), ...)
   ON COMMIT PRESERVE ROWS;

INSERT INTO SESSION.TEMP2
   SELECT ... 
   FROM SESSION.TEMP1 ...;

INSERT INTO T3
   SELECT ... FROM
   FROM SESSION.TEMP2 ...
   T2 ...;

DROP TABLE SESSION.TEMP1;
DROP TABLE SESSION.TEMP2;

SET CURRENT QUERY ACCELERATION = ALL;

CREATE DATABASE DBTEMP;
CREATE TABLE TEMP1 (
   C11 CHAR(2), ...)
   IN ACCELERATOR ACCEL1
   IN DATABASE DBTEMP;

INSERT INTO TEMP1 
SELECT ... FROM T1 ...;

CREATE TABLE TEMP2 (
   C21 CHAR(2), ...)
   IN ACCELERATOR ACCEL1 
   IN DATABASE DBTEMP;

INSERT INTO TEMP2 
   SELECT ... FROM TEMP1 ...;

INSERT INTO T3
   SELECT ... FROM
   FROM TEMP1 ...
   T2 ...;

DROP TABLE TEMP1;
DROP TABLE TEMP2;

DROP DATABASE DBTEMP;
Notes:
  • Bear in mind that alternative constructs, such as declared global temporary tables, which can exist in Db2, but not on an accelerator, cannot be accelerated by IBM Db2 Analytics Accelerator.
  • It is not possible to create image copies of accelerator-only tables because these tables do not hold any data in Db2. You can only recover the data from their original sources, which is why accelerator-only tables should be used only for temporary data.
  • An accelerator-only table can reside on just a single accelerator. Copies on other accelerators cannot exist (no high availability).
  • For information on how to create an accelerator-only table using the CREATE TABLE command or the restrictions governing the use of these tables, follow the Related information link at the end of this topic.
  • When you submit a CREATE TABLE statement to create an accelerator-only table, the statement is committed in Db2 for z/OS before the corresponding action is completed on the accelerator. That is, there is a brief period during which the table is listed in the Db2 catalog, but not in the accelerator catalog. If you run the SYSPROC.ACCEL_GET_TABLES_INFO stored procedure during that time, the integrity status ViolatedOnAccelerator is reported for that table in the result set. (In IBM Db2 Analytics Accelerator Studio, you see the error icon next to the table name in the Tables section of the Accelerator view. If you move the mouse pointer over the icon, the integrity status is displayed in a fly-over help window.)