Table Types

Table 20 lists the properties of the six types of tables available with Extended Parallel Server. The flag values are the octal values for each table type in the flags column of systables.

For information about logging, see Logging and Nonlogging Tables. For information about fast recovery, see Fast Recovery of Tables. For information on restoring various table types, see the IBM Informix: Backup and Restore Guide.

Scratch and Temp Tables

Scratch and temp tables are temporary tables that are dropped when the user session closes, the database server shuts down, or on reboot after a failure. You cannot recover, back up, or restore temp and scratch tables.

Scratch and temp tables support bulk operations such as light appends, which add rows quickly to the end of each table fragment. For more information on light appends, refer to your IBM Informix: Performance Guide.

Scratch tables are nonlogging temporary tables that do not support constraints, or rollback.

Temp tables are logged tables by default, and they support indexes, constraints, and rollback.

Once a table is indexed it cannot support light appends, regardless of its type.

Temp tables created with NO LOG and scratch tables support unique and duplicate indexes.

Extended Parallel Server creates explicit temporary tables according to the following criteria:

• If the query used to populate the TEMP table produces no rows, the database server creates an empty, unfragmented table.
• If the rows that the query produces do not exceed 8 kilobytes, the temporary table resides in only one dbspace.
• If the rows exceed 8 kilobytes, Extended Parallel Server creates multiple fragments and uses a round-robin fragmentation scheme to populate them.

SELECT...INTO TEMP or SELECT...INTO SCRATCH statements operate in parallel across coservers, just as ordinary inserts do. Extended Parallel Server automatically supports fragmented temporary tables across nodes when those tables are explicitly created with SELECT...INTO TEMP or SELECT...INTO SCRATCH.

Raw Permanent Tables

Raw tables are nonlogging permanent tables that use light appends. You can use the express loading mode to load them.

Updates, inserts, and deletes are supported but not logged. Raw tables do not support referential constraints, or rollback. You can restore a raw table from the last physical backup if it has not been updated since then. You can recover a raw table if it has not been updated since the previous checkpoint.

Raw tables support unique and duplicate indexes.

Raw tables are intended for the initial loading and validation of data. Once you have completed these steps, you should alter the table to a higher level. If an error or failure occurs during loading of a raw table, the resulting data is whatever was on the disk at the time of the failure.

Static Permanent Tables

Static tables are nonlogging read-only permanent tables. They do not support inserts, updates, and deletes. However, you can create and drop nonclustered indexes and referential constraints because they do not affect the data. Their advantage is that the server can use light scans and avoid locking during the execution of queries because static tables are read-only.

Static tables do not support rollback. Static tables inherit the recovery characteristics of the tables they were created from. If you alter a raw table to static table, you will be able to recover or restore it if it was not updated since the previous checkpoint or backup. If you alter a standard table to a static table, you always will be able to recover or restore it.

Operational Permanent Tables

Operational tables are logging permanent tables that allow indexes and constraints and fast update operations. They allow light appends only if the table contains no indexes or constraints.

If an operational table has indexes, the database server uses deluxe mode to load it. They perform row-by-row logging of insert, update, and delete operations but do not log light appends. If an operational table does not have indexes, the database server uses express mode to load it.

You can roll back operations or recover after a crash with operational tables. You can restore an operational table unless a light append occurred since the most recent backup. Operational tables are intended for use in situations when the data is derived from another source, so restorability is not an issue, but when rollback and recoverability are required.

Standard Permanent Tables

A standard table is the same as a table in a logged database that the database server creates. All operations are logged, record by record, so standard tables can be restored from a backup and support recoverability and rollback.

Standard tables do not use light appends, so you must use the deluxe loading mode. You must load standard tables record-by-record with every operation logged.

External Tables

An external table is a data file that you use to load and unload data. The database server performs express-mode and deluxe-mode loads. Use the CREATE EXTERNAL TABLE statement to load data into an external table.

Because external tables are outside of the database server, you cannot recover, roll back, or use ON-Bar to restore them. However, you can back up the external tables with a file backup program.

You can use express mode to load a raw or operational table without any indexes to an external table. You can use deluxe mode to load a raw or operational table with indexes, or a standard table to an external table. However, you cannot load a static table to an external table. For more information, refer to the chapter on loading with external tables in the IBM Informix: Administrator's Reference.

Rollback of Operational and Raw Tables

The following examples show how you can roll back transactions for operational tables but not for raw tables.

Rollback of Operational Tables

After you roll back the transaction on tab_op, the SELECT command shows that the inserted row was rolled back.

CREATE OPERATIONAL TABLE tab_op (c1 int); # create op table
BEGIN WORK; #start transaction
INSERT INTO tab_op values (1); # insert a row into table
ROLLBACK WORK; # transaction rolled back
SELECT * FROM tab_op; # inserted row is gone

Rollback of RawTables

After you roll back the transaction on tab_raw, the SELECT command shows that the inserted row is not rolled back.

CREATE RAW TABLE tab_raw (c1 int); # create raw table
BEGIN WORK; #start transaction
INSERT INTO tab_raw values (2); # insert a row into table
ROLLBACK WORK; # transaction rolled back
SELECT * FROM tab_raw; # inserted row remains

Switching Between Table Types

Use the ALTER TABLE command to switch between types of permanent tables. If the table does not meet the restrictions of the new type, the alter fails and produces an explanatory error message. The following restrictions apply to table alteration:

• You must drop indexes and referential constraints before you alter a table to type RAW.
• You must perform a level-0 backup before you alter a table to type STANDARD.
• You cannot alter a TEMP or SCRATCH temporary table.