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.

Important:

Simple Informix-style SQL statement creating temporary tables can be converted to a native SQL equivalent instruction. However, complex SQL statements such as SELECT .. INTO TEMP with subqueries may fail. In such cases, create a view from the complex query and then create the temp table from the view. Or, disable Informix emulation and use the native SQL syntax to create the temporary table (EXECUTE IMMEDIATE "/* fglhint_no_ifxemul */ …")

With Informix SQL, if the source table has a column defined as SERIAL or BIGSERIAL, a SELECT ... INTO TEMP will produce a new temp table with an auto-incremented serial column. With the SELECT … INTO TEMP emulation for non-Informix databases, not using the native sequence generators (such as IDENTITY columns in SQL Server), the resulting temporary table will get a simple INTEGER or BIGINT column, instead of an auto-incremented column.

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 and BIGSERIAL data types.
SAP HANA does not support index creation on temporary tables.