Restrictions of accelerator-only tables
Here is a summary of the restrictions that exist for 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 following restrictions exist for data-definition statements and accelerator-only tables:- 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
orALTER TRIGGER
statement must not reference an accelerator-only table in the body of the trigger. - A
CREATE TRIGGER
orALTER TRIGGER
statement with anINSTEAD 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 aDROP TABLE
statement. - The
statement does not behave as expected:DROP DATABASE
- 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 separateDROP TABLE
statements, and then the database.
- If you create an accelerator-only table
without specifying a database, the table is created in the default database as usual, but a
- 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:
This example, on the other hand, does work:CREATE TABLE AOT1 ... IN ACCELERATOR ... CREATE TABLE AOT2 ... IN ACCELERATOR ... COMMIT;
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:
APPEND |
AS FULLSELECT 1 |
AUDIT |
BUFFERPOOL |
CHECK |
COMPRESS |
DATA CAPTURE |
DSSIZE |
EDITPROC |
FIELDPROC |
FOREIGN KEY |
GENERATED |
HASH |
IMPLICITLY HIDDEN |
IN TABLESPACE |
INLINE LENGTH |
LIKE |
LOGGED |
MEMBER CLUSTER |
NOT LOGGED
|
PARTITION BY |
PERIOD |
PRIMARY KEY |
TRACKMOD |
UNIQUE INDEX |
VALIDPROC |
VOLATILE |
WITH DEFAULT |
Other SQL statements
The following statements are not allowed:
- You cannot run an
INSERT INTO <accelerator-only table> SELECT ... FROM ...
statement if theSELECT
part of the statement references a normal Db2 table that has no loaded counterpart on the accelerator. The statements succeeds only if theSELECT
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 anINSERT 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
Some of the IBM Db2 Analytics Accelerator stored procedures do not work on accelerator-only tables:- 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
In general, all limitations of the Netezza® database on the IBM PureData® System for Analytics also apply to accelerator-only tables.Different results
Results obtained by running the same query against a normal Db2 table and an accelerator-only table might differ under the following conditions:- 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:
In this case, Db2 would use the value 89999999999999999999999999.12345, while the Netezza database would use 89999999999999999999999999.12346.CREATE TABLE AOT( N_DEC DECIMAL(31,5) NOT NULL ) IN ACCELERATOR A1; INSERT INTO AOT VALUES(89999999999999999999999999.123455)
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:
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.CREATE TABLE AOT( N_DEC DECIMAL(31,0) NOT NULL ) IN ACCELERATOR A1; INSERT INTO AOT VALUES(9999999999999999999999999999999.9)
1 The restriction for the
AS FULLSELECT
clause is limited to the CREATE TABLE
statement. It does work with other statements
that reference an accelerator-only table,
such as the CREATE VIEW
statement.