Using DBMS Temporary Tables as HOLD Files

In this section:

How to:

Reference:

You can create a report output file (that is, a HOLD file), as a native DBMS temporary table. This increases performance by keeping the entire reporting operation on the DBMS server, instead of downloading data to your computer and then back to the DBMS server.

For example, if you temporarily store report output for immediate use by another procedure, storing it as a temporary table instead of creating a standard HOLD file avoids the overhead of transmitting the interim data to your computer.

The temporary table columns are created from the following report elements

except for those for which NOPRINT is specified.

The temporary table that you create from your report will be the same data source type (that is, the same DBMS) as the data source from which you reported. If the data source from which you reported contains multiple tables, all must be of the same data source type and reside on the same instance of the DBMS server.

You can choose between several types of table persistence.

You can create extract files as native DBMS tables with the following adapters:

Syntax: How to Save Report Output as a Native Temporary Table Using Commands

The syntax to save report output as a native DBMS temporary table is 

ON TABLE HOLD [AS filename] FORMAT SAME_DB [PERSISTENCE persistValue]

where:

filename
Specifies the name of the HOLD file. If you omit AS filename, the name of the temporary table defaults to "HOLD".

Because each subsequent HOLD command overwrites the previous HOLD file, it is recommended to specify a name in each request to direct the extracted data to a separate file, thereby preventing an earlier file from being overwritten by a later one.

PERSISTENCE
Specifies the type of table persistence and related table properties. This is optional for DBMSs that support volatile tables, and required otherwise. For information about support for volatile tables for a particular DBMS, see Temporary Table Properties for SAME_DB Persistence Values, and consult your DBMS vendor documentation.
persistValue
Is one of the following:
VOLATILE
Specifies that the table is local to the DBMS session. A temporary synonym (a Master File and Access File), is generated automatically. It expires when the server session ends.

This is the default persistence setting for all DBMSs that support volatile tables.

For information about support for the volatile setting, and about persistence and other table properties, for a particular DBMS, see Temporary Table Properties for SAME_DB Persistence Values, and consult your DBMS vendor documentation.

GLOBAL_TEMPORARY
Specifies that while the table exists, its definition will be visible to other database sessions and users though its data will not be. A permanent synonym (a Master File and Access File), is generated automatically.

For information about support for the global temporary setting, and about persistence and other table properties, for a particular DBMS, see Temporary Table Properties for SAME_DB Persistence Values, and consult your DBMS vendor documentation.

PERMANENT
Specifies that a regular permanent table will be created. A permanent synonym (a Master File and Access File), is generated automatically.

Reference: Temporary Table Properties for SAME_DB Persistence Values

The following chart provides additional detail about persistence and other properties of temporary tables of different data source types that are supported for use with HOLD format SAME_DB.

DBMS

VOLATILE

GLOBAL_TEMPORARY

Db2

Db2 on Linux, UNIX, and Windows, and Db2 for z/OS: a volatile table is created using the DECLARE GLOBAL TEMPORARY TABLE command with the ON COMMIT PRESERVE ROWS option. Declared global temporary tables persist and are visible only within the current session (connection). SESSION is the schema name for all declared global temporary tables.

Db2 on Linux, UNIX, and Windows, and Db2 for z/OS: a global temporary table is created using the CREATE GLOBAL TEMPORARY TABLE command. The definition of a created global temporary table is visible to other sessions, but the data is not. The data is deleted at the end of each transaction (COMMIT or ROLLBACK command). The table definition persists after the session ends.

Global tables require the following setting to be in effect:

ENGINE DB2 SET AUTOCOMMIT ON FIN

For information on creating user-defined tablespaces on Linux, UNIX, and Windows for volatile and global temporary tables, see the TIBCO WebFOCUS® Adapter Administration manual.

Informix

A volatile table is created using the CREATE TEMP TABLE command with the WITH NO LOG option. The definition and the data persist, and are visible, only within the current session.

This type of table is not supported by Informix.

Microsoft SQL Server

A volatile table is created as a local temporary table whose name is prefixed with a single number sign (#). Therefore, the name of a volatile table used as a HOLD file is the name specified by the HOLD phrase, prefixed with a number sign (#). The table definition and the data persist, and are visible, only within the current session.

The name of a global temporary table is prefixed with two number signs (##). Therefore, the name of a global temporary table used as a HOLD file is the name specified by the HOLD phrase, prefixed with two number signs (##). The table is dropped automatically when the session that created the table ends and all other tasks have stopped referencing it. The table definition and data are visible to other sessions.

MySQL

A volatile table is created using the CREATE TEMPORARY TABLE command. A temporary table persists and is visible only within the current session (connection). If a temporary table has the same name as a permanent table, the permanent table becomes invisible.

This type of table is not supported by MySQL.

Oracle

This type of table is not supported by Oracle.

The table definition is visible to all sessions. Its data is visible only to the session that inserts data into it. The table definition persists for the same period as the definition of a regular table.

Teradata

A volatile table definition and data are visible only within the session that created the table and inserted the data. The volatile table is created with the ON COMMIT PRESERVE ROWS option.

A global temporary table persists for the same duration as a permanent table. The definition is visible to all sessions, but the data is visible only to the session that inserted the data. The global temporary table is created with the ON COMMIT PRESERVE ROWS option.

Column Names in the HOLD File

Each HOLD file column is assigned its name:

  1. From the AS name specified for the column in the report request.
  2. If there is no AS name specified, the name is assigned from the alias specified in the synonym. (The alias is identical to the column name in the original relational table.)
  3. In all other cases, the name is assigned from the field name as it is specified in the synonym.

Primary Keys and Indexes in the HOLD File

A primary key or an index is created for the HOLD table. The key or index definition is generated from the sort (BY) keys of the TABLE command, except for undisplayed sort keys (that is, sort keys for which NOPRINT is specified). To determine whether a primary key or an index will be created:

  1. If these sort keys provide uniqueness and do not allow nulls (that is, if in the synonym, the MISSING attribute column is unselected or OFF), and if the DBMS supports primary keys on the type of table being created, a primary key is created.
  2. If these sort keys provide uniqueness but either
    1. some of the columns allow nulls.
    2. the DBMS does not support primary keys on the type of table being created then a unique index is created.
  3. If these sort keys do not provide uniqueness, a non-unique index is created.
  4. If there are no displayed sort keys (that is, no sort keys for which NOPRINT has not been specified), no primary key or index is created.