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 tt2Temporary 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.
Netezza®
Netezza support temporary tables as Informix, with a little syntax difference in the
SELECT INTO
TEMP instruction:SELECT * INTO TEMP temptab FROM sourceSolution
Temporary tables are well supported with native Netezza temp tables.
The general FGLPROFILE entry to
control temporary table emulation
is:
dbi.database.dsname.ifxemul.temptables = { true | false }