SQL Programming Techniques › Creating and Using a Temporary Table

Creating and Using a Temporary Table

A temporary table differs from a database table in these ways:

• A temporary table exists only as long as the database transaction in which it is created
• You cannot create an index on a temporary table
• A temporary table cannot be referenced in a view or a referential constraint
• A temporary table cannot be accessed by another database transaction

With the above exceptions, a program can access a temporary table and manipulate temporary table data as it does with a database table.

Why Use a Temporary Table

A temporary table can be useful for certain processing requirements, such as to:

• Take a snapshot of information in the database
• Avoid re-accessing base tables multiple times to retrieve the same information, to process efficiently and assure that the information does not change
• Perform certain operations that cannot be done with a single SQL statement, such as inserting rows into a table using data retrieved from the same table

Caution Using a Temporary Table

Since you cannot create an index on a temporary table, access to a temporary table is always serial. Accessing data in a temporary table with many rows may degrade the performance of the program.

How You Create a Temporary Table

You create a temporary table in the procedural section of the program by issuing a CREATE TEMPORARY TABLE statement. This statement requires:

• A temporary table name
• Column names
• Column definitions

CA IDMS maintains temporary tables in the scratch area. The program does not supply information about the physical characteristics of a temporary table.

Note: For more information about creating temporary tables in particular, see the CA IDMS SQL Reference Guide. For more information about creating tables in general, see the CA IDMS Database Administration Guide.

Naming a Temporary Table

When you create a temporary table, you should name it in a way that cannot match the name of any table or view that may be created. If a temporary table name matches the name of a base table or view, the optimizer will assume the name refers to the base table or view, and the temporary table will not be accessed.

Cursor for a Temporary Table

The program can declare a cursor for a temporary table. However, when you create the access module for the program, the optimizer issues a warning in response to any reference to the temporary table other than in the CREATE TEMPORARY TABLE statement.

The programmer has the responsibility of verifying that the cursor declaration and the CREATE TEMPORARY TABLE statement are compatible.

Temporary Table Example

In this example, the program creates a temporary table of manager names and ids using information in the EMPLOYEE table. (The EMPLOYEE table itself associates the id of a manager with the name of the subordinate employee, not the name of the manager.) Using a cursor, the program accesses a row of the temporary table and selects employees from the EMPLOYEE table who report to the manager identified in the temporary table row.

This is the cursor declaration and the statement to create the temporary table:

 WORKING STORAGE SECTION.
 EXEC SQL
 DECLARE TEMP_CRSR CURSOR FOR
   SELECT *
     FROM TEMP_MGR
     ORDER BY 3
 END EXEC
  .
  .
  .
 PROCEDURE DIVISION.

 EXEC SQL
   CREATE TEMPORARY TABLE TEMP_MGR
          (TEMP_MGR_ID             INTEGER,
          TEMP_FNAME              CHAR(20),
          TEMP_LNAME              CHAR(20))
 END-EXEC.

This statement adds manager information to the temporary table:

EXEC SQL
  INSERT INTO TEMP_MGR
  SELECT DISTINCT E.MANAGER_ID,
         M.EMP_FNAME,
         M.EMP_LNAME
    FROM EMPLOYEE E, EMPLOYEE M
    WHERE E.MANAGER_ID = M.EMP_ID
END-EXEC.

This statement establishes a current cursor row for the temporary table:

EXEC SQL
  FETCH TEMP_CRSR
    INTO :MGR-ID,
         :MGR-FNAME,
         :MGR-LNAME
END-EXEC.

This statement performs a bulk select of employees who report to the manager in the current cursor row. Depending on processing requirements, this statement could be a bulk fetch:

EXEC SQL
  SELECT EMP_FNAME,
         EMP_LNAME,
         DEPT_ID
    BULK :BULK-EMPLOYEE
    FROM EMPLOYEE
    WHERE MANAGER_ID = :MGR-ID
      AND TERMINATION_DATE IS NULL
END-EXEC.