Restrictions of accelerator-only tables

Current limitations

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.

Data-definition (DDL) statements

• The IN ACCELERATOR clause can be used only once in a statement.
• A CREATE TABLE must not contain a materialized query definition if the table is an accelerator-only table.
• A CREATE MASK statement must not define a mask for an accelerator-only table.
• A CREATE PERMISSION statement must not define a column permission for an accelerator-only table.
• A DECLARE GLOBAL TEMPORARY TABLE statement must not reference an accelerator-only table.
• A CREATE TRIGGER or ALTER TRIGGER statement must not reference an accelerator-only table in the body of the trigger.
• A CREATE TRIGGER or ALTER TRIGGER statement with an INSTEAD OF clause must not reference a view that references an accelerator-only table in the body of the trigger.
• An ALTER TABLE statement is not allowed if the table is an accelerator-only table.
• An ALTER TABLESPACE statement is not allowed if the table space contains accelerator-only tables.
• A DROP TABLESPACE statement fails if the table space contains accelerator-only tables. You must first drop the accelerator-only tables by using a DROP TABLE statement.
• The DROP DATABASE statement does not behave as expected:
  • If you create an accelerator-only table without specifying a database, the table is created in the default database as usual, but a DROP DATABASE statement does not remove that database.
  • A DROP DATABASE statement does not remove a database and the tables therein if some of the tables are accelerator-only tables. To remove such a database, first remove all accelerator-only tables by using separate DROP TABLE statements, and then the database.
• A RENAME TABLE statement fails if the table is an accelerator-only table.
• A LOCK TABLE statement must not refer to an accelerator-only table.
• A CREATE INDEX statement fails if the table to be indexed is an accelerator-only table.
• A CREATE SYNONYM statement fails if the synonym is for an accelerator-only table.
• You cannot create more than one accelerator-only table in a single transaction. The following example does not work:

  CREATE TABLE AOT1 ... IN ACCELERATOR ...
  CREATE TABLE AOT2 ... IN ACCELERATOR ...
  COMMIT;

  This example, on the other hand, does work:

  CREATE TABLE AOT1 ... IN ACCELERATOR ...
  COMMIT;
  CREATE TABLE AOT2 ... IN ACCELERATOR ...
  COMMIT;

In addition, the following clauses or keywords cannot be used in DDL statements that refer to accelerator-only tables:

Other SQL statements

The following statements are not allowed:

• You cannot run an INSERT INTO <accelerator-only table> SELECT ... FROM ... statement if the SELECT part of the statement references a normal Db2 table that has no loaded counterpart on the accelerator. The statements succeeds only if the SELECT part references one or more tables on the same accelerator as the accelerator-only table.
• You cannot submit an UPDATE statement against table columns that serve as distribution keys.
• You cannot insert values directly into a decimal column of an accelerator-only table if these values are the result of a division where the dividend, the divisor, or both are decimals. However, the operation works if the division is part of the SELECT portion in an INSERT INTO ... SELECT FROM … statement.

Restrictions on data types, column types, and code pages

Note that restrictions that apply to certain data types in general also apply to the use of these types in accelerator-only tables. In addition to that, the following restrictions exist for data types, column types, and code pages (CCSIDs), or are especially noteworthy:

• The column names in an accelerator-only table must not start with a tilde (~) followed by an underscore (_) character.
• You cannot have string columns of the type CHAR FOR BIT DATA or VARCHAR FOR BIT DATA in accelerator-only tables if these tables are encoded in ASCII or UNICODE. Only EBCDIC tables can have such columns.
• The CHAR FOR BIT DATA and VARCHAR FOR BIT DATA data types are not supported in accelerator-only tables if the environment has been set up for the use of mixed data (ZPARM MIXED=YES).
• You cannot define a large-object (LOB) column for an accelerator-only table.
• You cannot define a security label column for an accelerator-only table.

Stored procedures

SYSPROC.ACCEL_ADD_TABLES

Submit a CREATE TABLE ... IN ACCELERATOR statement instead.

SYSPROC.ACCEL_REMOVE_TABLES

Submit a suitable DROP TABLE statement instead.

SYSPROC.ACCEL_SET_TABLES_REPLICATION

Accelerator-only tables cannot be enabled for incremental updates.

SYSPROC.ACCEL_ARCHIVE_TABLES

You cannot archive partitions of accelerator-only tables because accelerator-only tables are not partitioned.

SYSPROC.ACCEL_RESTORE_ARCHIVE_TABLES

Since you cannot archive partitions of accelerator-only tables, you can neither restore archived partitions.

SYSPROC.ACCEL_SET_TABLES_ACCELERATION

You cannot disable acceleration on accelerator-only tables because they exist on an accelerator only, and thus must be enabled for acceleration.

Netezza limitations

Different results

• One of the tables that are specified in the query contains a CHAR column in Unicode or in a mixed format, and the column values are padded with blank characters.
• Decimal values with fractional digits and a total length of 31 (the maximum) are allowed in a table column that is referenced by the query. A value to be inserted has a fractional portion that is longer than the defined maximum. In this - and only in this special case - does Netezza round the fractional portion where otherwise, it would be truncated. The different treatment of such values in Db2 for z/OS and Netezza might lead to different results. For example:

  CREATE TABLE AOT(
     N_DEC DECIMAL(31,5) NOT NULL
     ) IN ACCELERATOR A1;
  
  INSERT INTO AOT VALUES(89999999999999999999999999.123455)

  In this case, Db2 would use the value 89999999999999999999999999.12345, while the Netezza database would use 89999999999999999999999999.12346.
  However, the INSERT statement would fail with a numeric data overflow error if the allowed maximum was 31 without fractional digits, but the value to be inserted was longer than that and had a fractional portion equal to or greater than 0.5, as in the following example:

  CREATE TABLE AOT(
     N_DEC DECIMAL(31,0) NOT NULL
     ) IN ACCELERATOR A1;
  
  INSERT INTO AOT VALUES(9999999999999999999999999999999.9)

  The value to be inserted here has a total length of 32. Rounding it would require an extra, non-fractional digit, which is why an overflow error is returned.