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.

Microsoft™ SQL Server

Microsoft SQL Server provides local (SQL session wide) or global (database wide) temporary tables by using the '#' or '##' characters as table name prefix:
CREATE TABLE #temp1 ( kcol INTEGER, .... )

The INTO clause can be used within a SELECT statement to create and fill a temporary table in one step:

SELECT * INTO #temp2 FROM customers WHERE ...
Important: SQL Server temporary tables are created by default with the collation of the tempdb database, instead of inheriting the collation of the current database you are connected to.

Solution

In BDL, Informix temporary tables instructions are converted to generate native SQL Server temporary tables.

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.
Note: Microsoft SQL Server does not support scroll cursors based on a temporary table.
Important: In order to use the same collation in your temp tables as in your permanent tables, install SQL Server with the same collation as your database. See Installation for more details.