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 your administration client. You have to use INSERT statements. These can be submitted from interactive client interfaces (SPUFI, IBM Data Studio, RUN SQL submenu in IBM Data Server Manager), 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.

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;

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;