Prohibiting Globally Detached Indexes on Nonlogging Tables

Version 8.50 of Extended Parallel Server introduced support for indexes on nonlogging permanent and temporary tables. This previously undocumented feature supports table-level restore operations on RAW tables.

This feature uses the same syntax as for creating indexes on logged tables, but can specify RAW, SCRATCH, and TEMP tables:

CREATE RAW TABLE jcraw (col1 INT, col2 CHAR(20));
CREATE INDEX jcind1 ON jcraw (col2, col1);

CREATE SCRATCH TABLE jcscratch (col1 INT, col2 DATE);
CREATE INDEX jcind2 ON jcscratch (col2);

SELECT * FROM systables INTO TEMP systemp WITH NO LOG;
CREATE INDEX systempidx ON systemp (tabname);
		

As in earlier releases, triggers cannot be created on RAW, SCRATCH, or STATIC tables. Similarly, referential constraints, except for NOT NULL and DEFAULT, are valid only for logged tables. Light appends continue to be disabled for indexed tables.

In terms of storage location, Extended Parallel Server supports two types of indexes on nonlogging tables:

• attached indexes, whose keys reside in the same storage spaces as the data records that they reference
• locally detached indexes, whose keys reside in different storage spaces from the data records, but on the same coserver.

The database server issues error -24151, however, if CREATE INDEX attempts to define a globally detached index (GDI) on a nonlogging table. A GDI is an index for which you specify storage on another coserver when the index is created, so that the index keys reside on one coserver, but the data rows that the index references reside on a different coserver. No error is issued by XPS 8.50, however, if the ALTER TABLE statement of SQL attempts to change the logging type of a logged table that has a GDI into a nonlogging type, such as RAW.

In this release, Extended Parallel Server issues error -24151 if the ALTER TABLE table TYPE statement attempts to change to RAW or STATIC the logging type of a logged table that has a GDI.