You are currently on IBM Systems Media’s archival website. Click here to view our new website.

What to Watch For

My first word of advice? If you have a need to reference the DB2 documentation (like the SQL Reference Guide) make sure you are using the flavor of the documentation that applies to DB2 10. Remember that as of this writing, accelerator-only tables are not yet supported by DB2 11 so information about it won’t be in the DB2 11 product publications.

You probably won’t want to use accelerator-only tables if you have any need to update the data in DB2 or if you have a need for a backup of the data.

If IN ACCELERATOR is specified, CREATE…. IN ACCELERATOR must be within its own unit of work. It cannot run in a unit of work with any other SQL statement.

Although an INSERT INTO SELECT ... FROM ... statement can be used to load an accelerator-only table, the table (or tables) specified in the SELECT potion of the statement must exist in the Analytics Accelerator. The data cannot be inserted from outside the Analytics Accelerator.

Not all data types are acceptable on the CREATE TABLE statement. Data types DECFLOAT, LOB, ROWID, TIMESTAMP WITH TIME ZONE and XML are not supported for accelerator-only tables. All data types valid for accelerator-only tables are the same data type valid for accelerator-shadow DB2 tables. Accelerator-only tables carry no additional restrictions.

Along with only selected data types being available, there’s a whole handful of CREATE TABLE keywords that cannot be used when creating an accelerator-only table. I’ve listed a few here in no particular order: UNIQUE, PRIMARY KEY, FOREIGN KEY, FIELDPROC, VALIDPROC, CHECK, AUDIT (NONE, CHANGES, and ALL), DATA CAPTURE, DSSIZE, VOLATILE, LOGGED/NOT LOGGED, BUFFERPOOL, TRACKMOD YES/NO, COMPRESS YES/NO, MEMBER CLUSTER, DEFAULT, AS IDENTITY, IMPLICITLY HIDDEN, PERIOD (period definition), AS SECURITY LABEL, PARTITION BY RANGE or PARTITION BY SIZE, and HASH SPACE or ORGANIZE BY HASH.

The LIKE clause cannot reference an accelerator-only table and when the IN ACCELRATOR clause is specified the LIKE and materialized query tables can’t be specified. Also a fullselect cannot reference an accelerator-only table.

Advertisement

Mainframe News Sign Up Today! Past News Letters