12.22.3 Rules for choosing an RDAREA for storage
(1) Choosing a target back-end server for storage (applicable to HiRDB/Parallel Servers only)
For a HiRDB/Parallel Server, HiRDB first chooses a back-end server for storing data. HiRDB narrows down the candidate back-end servers based on the rules described below and then chooses a back-end server that accesses a base table that is not a temporary table among all the base tables specified in the INSERT statement.
- When RDAREAs are specified in PDTMPTBLRDAREA in the client environment definition
HiRDB chooses as the storage candidate the back-end server containing the specified RDAREAs.
If the specified RDAREAs include both temporary table RDAREAs with the SQL session-specific attribute and with the SQL session shared attribute, HiRDB uses the back-end server that contains the temporary table RDAREA with the SQL session-specific attribute.
- When no RDAREAs are specified in PDTMPTBLRDAREA in the client environment definition
HiRDB chooses as the storage candidate the back-end server containing a temporary table RDAREA with the SQL session shared attribute.
- Hint
- When HiRDB chooses a target back-end server for storage, it preferentially chooses a back-end server that accesses a base table that is not a temporary table among all the base tables specified in the INSERT statement. Therefore, you can avoid data transfer between back-end servers if you use INSERT SELECT as shown in the following example.
- Example: INSERT INTO TMP1 SELECT C1,C2,C3 FROM T1
- This SQL statement inserts into temporary table TMP1 columns C1, C2, and C3 from table T1.
- The figure below show the configuration for executing this SQL statement. In this example, RDTMP1 and RDTMP2 are specified in PDTMPTBLRDAREA.
![[Figure]](figure/zu122203.gif)
- In this example, HiRDB chooses BES1 that contains table T1 as the target back-end server for storage. As a result, SQL statements can be executed without having to transfer data between BES1 and BES2.
(2) Choosing storage candidate RDAREAs
(3) Choosing the RDAREAs that satisfy the conditions
From the storage candidate RDAREAs, HiRDB chooses RDAREAs that satisfy all the following conditions:
- RDAREAs that allow locks for temporary table operations to be acquired.
For details about acquiring locks for temporary table operations, see 12.22.5 Locking for temporary tables.
- RDAREAs that are accessible from the UAP.
- If the temporary table to be stored is a FIX table, RDAREAs that can accommodate the temporary table's row length.
For details, see rule 3 for the FIX operand in CREATE TABLE in the manual HiRDB Version 9 SQL Reference.
- RDAREAs that can accommodate the total length of the columns that comprise any temporary table indexes that are to be stored.
For details, see common rule 5 in CREATE INDEX in the manual HiRDB Version 9 SQL Reference.
- RDAREAs for which the usage count for temporary tables is less than 500.
- RDAREAs for which the usage count for temporary table indexes is less than 500.
- RDAREAs that have unused segments.
- RDAREAs for which the total number of temporary tables and temporary table indexes does not exceed the specified pd_max_temporary_object_no operand value.
- If ACCESS is specified in the pd_tmp_table_initialize_timing operand, RDAREAs that are uninitialized temporary table RDAREAs.
For details about initialization of temporary table RDAREAs, see 14.7(4) Initializing the temporary table RDAREAs.
(4) Choosing the temporary table RDAREA in which to store data
Among the RDAREAs satisfying the conditions, HiRDB preferentially uses the following temporary table RDAREAs:
- The temporary table RDAREA that contains the largest number of unused segments
- An uninitialized temporary table RDAREA if ACCESS is specified in the pd_tmp_table_initialize_timing operand.
For details about initialization of temporary table RDAREAs, see 14.7(4) Initializing the temporary table RDAREAs.