Nonlogging raw tables that speed up data loading time

If you use a database server utility to load data, you can use nonlogging raw tables in a logging database to speed up the initial loading and validation of data if you are moving data to or from Version 9.21 or any later version.

Data warehousing and other applications can have very large tables that take a long time to load. Nonlogging tables are faster to load than logging tables.

In a logged database, Informix® creates standard tables that use logging by default.

To create a nonlogging table, use the CREATE RAW TABLE statement, or use the ALTER TABLE statement to change the table type from STANDARD to RAW. After the loading of a raw table is complete, you can change the table to a logging table (in a logging database) by changing the table type to STANDARD. Then you can use ALTER TABLE statements to add referential constraints to the table and CREATE INDEX statements to add indexes. For more information about these SQL statements, see the HCL® Informix® Guide to SQL: Syntax.

To load raw tables, you can use any data loading utility, such as dbimport or HPL in express mode. After you load data, perform a level-0 backup. Before you modify any data in a raw table or use it in a transaction, change the table type to STANDARD.

If an error or failure occurs during the loading of a raw table, the resulting data is whatever was on the disk at the time of the failure.

The dbexport and dbschema utilities support the CREATE RAW TABLE and ALTER TABLE...TYPE (RAW) statements.

For more information about nonlogging tables, see your HCL® Informix® Administrator's Guide. For more information on how to improve the performance of loading very large tables, see your HCL® Informix® Performance Guide. For more information about the ALTER TABLE statement, see the HCL® Informix® Guide to SQL: Syntax.

Informix® also supports external tables, which provide an SQL interface to data in text files managed by the operating system or to data from a FIFO device. To create external tables, use the CREATE EXTERNAL TABLE statement. Use the DROP TABLE statement to drop an external table.