Save Report Output as a Native Temporary Table In Report Painter

How to:

Reference:

You can now 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: Display columns, Sort (BY) columns, and COMPUTE columns 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: DB2 (on z/OS, UNIX, and Windows), Informix, Microsoft SQL Server, MySQL, Oracle, and Teradata.


Top of page

x
Procedure: How to Save Report Output as a Native Temporary Table In Report Painter

  1. In Report Painter, select Options from the Report menu.
  2. From the Output Format drop-down list, expand the Database Formats folder and select Native Database Table (SAME DB).

    The top level of the Report Options dialog box shows the selected format and determines what tabs and options appear in the lower part of the dialog box.

  3. Specify the name of the output file. It defaults to HOLD. To select an existing file, click the Browse button.

    Because each subsequent HOLD command overwrites the previous HOLD file, it is advisable 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.

  4. Select the type of table persistence:
    • 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.

      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 the table persistence depends on your DBMS. While the table exists, its schema 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 the table will be permanent. A permanent synonym (a Master File and Access File) is generated automatically.

Top of page

x
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 UNIX, 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 Release 7.1 and up for z/OS only: 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.

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 and 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.


WebFOCUS