Shared tables

In the case of a HiRDB/Parallel Server, when multiple tables are joined, table data is read from the back-end servers where the individual tables are located and then matching is performed at a separate back-end server. This means that multiple servers are connected to transfer data. If the range of data to be searched for matches is located on a single back-end server, matching can be completed at the single back-end server by creating that data as a shared table. A shared table is a table stored in a shared RDAREA that can be referenced by all back-end servers. An index defined for a shared table is called a shared index. For details about shared tables, see the HiRDB Version 8 Installation and Design Guide.

Shared tables and shared indexes can also be defined for a HiRDB/Single Server. This provides SQL and UAP compatibility with a HiRDB/Parallel Server. Shared tables and shared indexes are usually used with a HiRDB/Parallel Server because they are especially effective in HiRDB/Parallel Servers. This section describes the use of shared tables with a HiRDB/Parallel Server. For details about using shared tables with a HiRDB/Single Server, see (6) Using shared tables with a HiRDB/Single Server below.

Organization of this subsection: (1) Advantages of shared tables; (2) Application criteria; (3) Notes on updating shared tables; (4) Definition method; (5) Limitations; (6) Using shared tables with a HiRDB/Single Server

(1) Advantages of shared tables

Because join processing can be completed by a single back-end server, the overhead associated with connecting between back-end servers and transferring data is reduced. Additionally, the number of back-end servers required for each transaction can be reduced, thereby improving the efficiency of parallel processing, particularly in the event of multiple executions.

(2) Application criteria

(3) Notes on updating shared tables

When you update a shared table, all back-end servers in which RDAREAs are allocated are locked. If you execute any statement other than the UPDATE statement or PURGE TABLE statement, which do not update index key values, you must specify IN EXCLUSIVE MODE with the LOCK TABLE statement and lock all back-end servers to update the shared table. This means that any jobs that access a locked RDAREA may result in deadlock, or that global deadlock may occur among the servers.

When you use a local buffer to update a shared table, issue the LOCK statement before you update. If you update without issuing the LOCK statement and the server process terminates abnormally, the abort code Phb3008 is output (which indicates that the unit may have terminated abnormally).

(4) Definition method

To define a shared table, specify SHARE with CREATE TABLE in the definition SQL (specify CREATE SHARE FIX TABLE). Note that shared tables must satisfy the following conditions:

The shared table is a non-partitioned FIX table.
The RDAREA storing the shared table or shared index is a shared RDAREA (specify SDB for the -k option of the pdfmkfs command).
The WITHOUT ROLLBACK option is not specified.
The table is not a referencing table defined with a referential constraint.

(5) Limitations

The following limitations apply to using a shared table:

You cannot search a shared table while the LOCK TABLE statement is being executed with IN EXCLUSIVE MODE specified.
You cannot use the ASSIGN LIST statement to create a list of shared tables.
You cannot specify a shared table as a replication copy destination

(6) Using shared tables with a HiRDB/Single Server

You cannot define shared RDAREAs in a HiRDB/Single Server; therefore, you must store shared tables and shared indexes in regular user RDAREAs. When you do this, you must provide separate user RDAREAs for shared tables and shared indexes; you must not store shared tables in user RDAREAs that store regular tables and indexes that are not shared.
When you migrate a HiRDB/Single Server to a HiRDB/Parallel Server, do not use the database structure modification utility (pdmod) while shared tables or shared indexes are defined. For details about the migration procedure, see the HiRDB Version 8 Installation and Design Guide.