Temporary tables

Informix®

Informix temporary tables are created with the CREATE TEMP TABLE DDL instruction or with SELECT ... INTO TEMP statement:

CREATE TEMP TABLE tt1 ( pkey INT, name VARCHAR(50) )
CREATE TEMP TABLE tt2 ( pkey INT, name VARCHAR(50) ) WITH NO LOG 
SELECT * FROM tab1 WHERE pkey > 100 INTO TEMP tt2

Temporary tables are automatically dropped when the SQL session ends, but they can also be dropped with the DROP TABLE command. There is no name conflict when several users create temporary tables with the same name.

BDL REPORTs can create a temporary table when the rows are not sorted externally (by the source SQL statement).

Informix allows you to create indexes on temporary tables. No name conflict occurs when several users create an index on a temporary table by using the same index identifier.

When creating temporary tables in Informix, the WITH NO LOG clause can be used to avoid the overhead of recording DML operations in transaction logs.

Oracle

Oracle® does not support temporary tables as Informix.

Oracle provides GLOBAL TEMPORARY TABLE command to create tables shared among several processes (only data is temporary and local to an SQL process).

Solution

In accordance with some prerequisites, temporary table creation in BDL programs can be supported by the database interface.

Important: When creating a temporary table, you perform a Data Definition Language statement. Oracle automatically commits the current transaction when executing a DDL statement. Therefore, you must avoid temp table creation/destruction in transactions.

The general FGLPROFILE entry to control temporary table emulation is:

dbi.database.dsname.ifxemul.temptables = { true | false }

For more details see IBM Informix emulation parameters in FGLPROFILE.

The temporary table emulation can use regular tables or GLOBAL TEMPORARY tables. The way the driver converts Informix temp table statements to Oracle regular tables or global temporary tables is driven by the following FGLPROFILE entry:

dbi.database.dbname.ifxemul.temptables.emulation = { "default" | "global" }

By default, the database driver uses regular tables (the "default" emulation). This default emulation provides maximum compatibility with Informix temporary tables, but requires real table creation which can be a significant overhead with Oracle. For more details, see Using the default temporary table emulation.

The "global" emulation uses native Oracle Global Temporary Tables, requiring only one initial table creation and thus making programs run faster. However, the global emulation mode has to be used carefully because of some limitations and constraints. For more details, see Using the global temporary table emulation.