14.7 Temporary table RDAREAs

A temporary table RDAREA is a user RDAREA for storing temporary tables and temporary table indexes.

Organization of this section
(1) Criteria
(2) Attributes of temporary table RDAREAs
(3) How to create temporary table RDAREAs
(4) Initializing the temporary table RDAREAs
(5) Backing up temporary table RDAREAs
(6) Notes about using temporary table RDAREAs

(1) Criteria

Temporary table RDAREAs are required in order to use temporary tables.

(2) Attributes of temporary table RDAREAs

There are two types of temporary table RDAREAs:

(3) How to create temporary table RDAREAs

The following describes how to create temporary table RDAREAs:

  1. Specify the maximum number of temporary table RDAREAs in the pd_max_tmp_table_rdarea_no operand, and specify the maximum number of temporary tables and temporary table indexes that can be used at the same time in the pd_max_temporary_object_no operand.
  2. Specify DB in the -k option of the pdfmkfs command to create a HiRDB file system area.
  3. Create a user RDAREA by specifying the following two operands in the create rdarea statement in the database initialization utility (pdinit) or the database structure modification utility (pdmod):
    • Specify for user used by PUBLIC.
      To use an RDAREA as a temporary table RDAREA, it must be a public user RDAREA.
    • Specify temporary table use.
      If the temporary table RDAREA is to have the SQL session shared attribute, specify temporary table use shared. If it is to have the SQL session-specific attribute, specify temporary table use occupied.
Notes on creation
  • Make sure that the pd_max_tmp_table_rdarea_no operand's value is smaller than the pd_max_rdarea_no operand's value. When you add temporary table RDAREAs, make sure that the total number of RDAREAs, including the temporary table RDAREAs being added, does not exceed the maximum number of RDAREAs specified in the pd_max_rdarea_no operand. If the total number will exceed the set maximum, change the pd_max_rdarea_no operand's value.
  • If there are multiple available temporary table RDAREAs, HiRDB determines automatically the temporary table RDAREA to be used. Therefore, we recommend that you set all temporary table RDAREAs that can be used by the same UAP to have the same RDAREA size, segment size, and page size.
    If the temporary table RDAREAs have different RDAREA sizes, segment sizes, or page sizes, the following problems might arise:
    [Figure]Each time a temporary table is instantiated by the first INSERT statement executed, an RDAREA with a different page size might be chosen for the storage. In such a case, whether the first INSERT statement will execute will depend on the relationship between the row length of the data to be inserted and the page size of the storage RDAREA.
    [Figure]HiRDB chooses an RDAREA with the largest number of segments as the storage RDAREA. Therefore, if the storage candidate RDAREAs have different page or segment sizes, HiRDB might choose an RDAREA with a smaller amount of free space as the storage RDAREA.
  • A temporary table RDAREA is initialized when HiRDB starts or when the first INSERT statement is executed on its temporary table. Therefore, if its size is large, the amount of overhead increases. For details about initialization of temporary table RDAREAs, see (4) Initializing the temporary table RDAREAs.
Example
This example creates a temporary table RDAREA (RDTMP01) with the SQL session shared attribute on BES1 of a HiRDB/Parallel Server. The following shows the control statement in the database structure modification utility (pdmod).

create rdarea RDTMP01 Specifies the name of the temporary table RDAREA
 globalbuffer tmpbuf01
 for user used by PUBLIC Specifies a public user RDAREA
 server name BES1
 open attribute INITIAL
 page 4096 characters
 storage control segment 100 pages
 temporary table use shared Specifies the SQL-session shared attribute
 file name "\hirdb\db\rdtmp01_f01"
   initial 500 segments ;

(4) Initializing the temporary table RDAREAs

When HiRDB starts, it initializes the temporary table RDAREAs that were used during the previous session, regardless of the start mode. Therefore, if a temporary table RDAREA to be initialized is large in size, it will take time for HiRDB to start. If you use the rapid system switchover facility or the standby-less system switchover facility and need to reduce the time required for system switchover, you can skip initialization of temporary table RDAREAs during HiRDB startup by specifying ACCESS in the pd_tmp_table_initialize_timing operand. Note that when ACCESS is specified, HiRDB initializes a temporary table RDAREA when the first INSERT statement is executed on a temporary table in the RDAREA. Therefore, if the size to be initialized is large, the overhead for executing the INSERT statement becomes large. To reduce the overhead, reduce the size of the temporary table RDAREA.

For details about the pd_tmp_table_initialize_timing operand and how to estimate the overhead for initializing temporary table RDAREAs, see the manual HiRDB Version 9 System Definition.

(5) Backing up temporary table RDAREAs

There is no need to back up a temporary table RDAREA because the data in temporary tables is retained only during the transaction or SQL session. If an error occurs in a temporary table RDAREA, re-initialize the RDAREA with the initialize rdarea statement of the database structure modification utility (pdmod).

(6) Notes about using temporary table RDAREAs

(a) Limitations

Some limitations apply when the operation commands and utilities listed below are executed on a temporary table RDAREA. For details, see the manual HiRDB Version 9 Command Reference.

(b) Using a temporary table RDAREA that has the SQL session-specific attribute

A temporary table RDAREA that has the SQL session-specific attribute can be used only by the SQL sessions for which the RDAREA is specified in PDTMPTBLRDAREA in the client environment definition. To use such an RDAREA more exclusively (so that only one SQL session can use it), allocate a local buffer. The corresponding temporary table RDAREA will be locked in lock mode (EX).

(c) Creating a non-FIX temporary table

To create a non-FIX temporary table, the temporary table RDAREA must satisfy the applicable condition listed below. If the temporary table RDAREA does not satisfy the applicable condition below, the KFPA11809-I message might be issued when data is stored.

(d) Linking with HiRDB Datareplicator

An update operation on a temporary table is not subject to extraction. Therefore, there are no considerations related specifically to temporary tables when HiRDB Datareplicator is used.