Temporary tables

Informix®

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.

SAP ASE

SAP® ASE supports temporary tables by using the # pound sign before the table name:

CREATE TABL #temp1 ( kcol INTEGER, .... )
SELECT * INTO #temp2 FROM customers WHERE ...

Solution

In BDL, Informix temporary tables instructions are converted to generate native SAP ASE temporary tables.

SELECT INTO TEMP statements cannot be converted, because SAP ASE does not provide a way to create a temporary table from a result set, such as CREATE TABLE xx AS (SELECT ... ).

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