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.

SAP HANA®

SAP HANA supports several types of temporary tables (local/global).

Then equivalent of Informix temporary tables are session temporary tables created with CREATE LOCAL TEMPORARY TABLE instruction:

CREATE LOCAL TEMPORARY TABLE #tt1 ( pk INT, name NVARCHAR(50) )

Note: SAP HANA temporary tables must be specified with a # number sign prefix.

For more details, see the SAP HANA documentation.

Solution

In accordance with some prerequisites, temporary tables creation in BDL programs can be supported with SAP HANA.

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 SAP HANA ® CREATE LOCAL TEMPORARY TABLE statements.

The database interface automatically adds a # number sign prefix before the table name. When used in other SQL statement the temporary table name is also prefixed with a # character.

Limitations

Tokens matching the original table names all get a # number sign prefix 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.

The serial emulation based on triggers is not supported, because triggers cannot be created on temporary tables.

For mored details see SERIAL data types.
SAP HANA does not support index creation on temporary tables.