Using the WITH NO LOG option

Use the WITH NO LOG option to reduce the overhead of transaction logging for the temporary table. If you specify WITH NO LOG, data manipulation language (DML) operations on the temporary table are not included in the transaction log records.

The WITH NO LOG keywords are required on all temporary tables that you create in temporary dbspaces. Within a cluster environment, the WITH NO LOG keywords are required when you create a temporary table on a secondary server.

If the ONCONFIG parameter TEMPTAB_NOLOG is set to 1, logging of temporary tables is disabled and all temporary tables are non-logging by default. This setting can improve the performance of operations that use temporary tables, such as HDR operations. The WITH NO LOG option is not needed when the TEMPTAB_NOLOG setting has disabled logging of temporary tables. For information about how to set the TEMPTAB_NOLOG parameter, see your HCL OneDB™ Administrator's Reference.

If you use the WITH NO LOG option in a database that does not use logging, the WITH NO LOG keywords of the CREATE TEMP TABLE statement have no effect. If your database does not support transaction logging, every table behaves as if the WITH NO LOG option were specified.

The ALTER TABLE statement cannot change the logging status of a temporary table. Once you turn off logging on a temporary table, you cannot turn it back on; a temporary table, therefore, is either always logged or else never logged.

The following temporary table is not logged in a database that uses transaction logging:

CREATE TEMP TABLE tab2 (fname CHAR(15), lname CHAR(15))
   WITH NO LOG;

Like all data definition language (DDL) statements of SQL, the CREATE TEMP TABLE statement above that creates tab2 is logged. The WITH NO LOG keywords, however, prevent transaction logging of any DELETE, INSERT, LOAD, MERGE, SELECT, UNLOAD, or UPDATE operations on tab2.