14.7 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:
- Temporary table RDAREAs with the SQL session shared attribute
This type of temporary table RDAREA can be used in any SQL session. Use this type to reduce the number of temporary table RDAREAs because such RDAREAs can be shared among SQL sessions.
- Temporary table RDAREAs with the SQL session-specific attribute
This type of temporary table RDAREA can be used only in specific SQL sessions (for which the temporary table RDAREA that is to be used is specified in PDTMPTBLRDAREA in the client environment definition). When a particular user handles a large amount of data, this type is used to prevent a shortage of space from occurring for the temporary table RDAREAs that are used by other users.
(3) How to create temporary table RDAREAs
The following describes how to create temporary table RDAREAs:
- 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.
- Specify DB in the -k option of the pdfmkfs command to create a HiRDB file system area.
- 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:
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.
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.
- pdhold command
- pdorcheck command
- pdorcreate command
- pdrdrefls command
- Database copy utility (pdcopy)
- Database condition analysis utility (pddbst)
- Database structure modification utility (pdmod)
This utility does not support moving RDAREAs or defining replicas for RDAREAs.
- Database recovery utility (pdrstr)
(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.
- When PDTMPTBLRDAREA is specified in the client environment definition
The same page length# is defined for all temporary table RDAREAs that are specified in PDTMPTBLRDAREA.
- When PDTMPTBLRDAREA is not specified in the client environment definition or an XDS client is used
The same page length# is defined for all temporary table RDAREAs with the SQL session shared attribute.
- #
- The page length is greater than the basic row length. To determine the basic row length, use the formula for determining the data length in Data lengths in the manual HiRDB Version 9 SQL Reference. If the KFPA11809-I message is issued, specify a value that is greater than the row length to be stored shown in the KFPA11809-I message.
(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.