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.

IBM® DB2®

IBM DB2 supports the DECLARE GLOBAL TEMPORARY TABLE instruction.

DB2 global temporary tables are quite similar to Informix temporary tables with some exceptions:

A user temporary table space must exist for the database.
Users must have 'USE' privilege on a 'user temporary table space'.
For usage, the temporary table name must be prefixed by 'SESSION'.
No constraints or indexes can be created on temporary tables.

Note: IBM DB2 version 11.x supports the CREATE TEMP TABLE syntax for compatibility with Netezza. However, IBM recommends to use the DECLARE GLOBAL TEMPORARY TABLE syntax, to create a temporary table.

For more details, see the DB2 documentation.

Solution

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

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.

How does it work ?

Informix-specific statements involving temporary table creation are automatically converted to IBM DB2 DECLARE GLOBAL TEMPORARY TABLE statements.
Once the temporary table has been created, all other SQL statements performed in the current SQL session are parsed to add the SESSION prefix to the table name automatically.

Prerequisites

Fulfill the DB2 prerequisites to create global temporary tables, at minimum you must create a user temporary table space and grant the usage to database users:
```
CREATE USER TEMPORARY TABLESPACE tempspace01 MANAGED BY AUTOMATIC STORAGE
GRANT USE OF TABLESPACE tempspace01 TO PUBLIC
```
See DB2 documentation for more details.

Limitations

Tokens matching the original table names are converted to unique names in all SQL statements. Make sure you are not using a temp table name for other database objects, like columns. The following example illustrates this limitation:
```
CREATE TEMP TABLE tmp1 ( col1 INTEGER, col2 CHAR(20) )
SELECT tmp1 FROM table_x WHERE ... 
```
Only the 'native' serial emulation mode is supported with temporary tables. See the issue about SERIALs for more details.