Appendix D. Restrictions on Using a Foreign Table

This appendix explains restrictions applicable to the use of foreign tables.

Organization of this section
(1) Transactions
(2) Data guarantee levels and the lock option
(3) Cursors
(4) Update SQL statements that cannot be executed on foreign servers
(5) Operation by row
(6) LOCK statement
(7) Operations on foreign server
(8) Subquery
(9) Named derived tables (Foreign server: HiRDB)
(10) Named derived tables (Foreign server: XDM/RD E2)
(11) Embedded variables and ? parameters
(12) Hexadecimal character string literal
(13) Number of retrieval items for retrieval executed on the foreign server
(14) Correlation name
(15) Conversion and substitution into national character data types

(1) Transactions

All transactions involving a foreign server are treated as a 1-phase commit.

If, under the following circumstances, an error occurs during the post-update commit process, some servers (foreign servers) may be committed while other servers (foreign servers) may be rolled back:

For this reason, you should update only one foreign server at a time within the same transaction (this applies also to updating the local HiRDB).

(2) Data guarantee levels and the lock option

HiRDB transfers the data guarantee level specified in the PDISLLVL client environment definition to the foreign server. The data guarantee level that is specified for each SQL statement by using the lock option is also transferred to the foreign server. However, if the lock option WITH EXCLUSIVE LOCK is specified, the lock status is retained until the end of the transaction. Table D-1 shows locking that is performed on the foreign server when the lock option is specified. Table D-2 shows locking that is performed on the foreign server when a lock option with WITH ROLLBACK and NO WAIT specifications is specified.

Table D-1 Locking on the foreign server with the lock option specified

Lock optionLocking on foreign server
HiRDBXDM/RD E2OracleDB2
Nothing specifiedThe settings on the HiRDB client prevail.1This is 1 when the data guarantee level is 0 or 1. FOR UPDATE is applied when the SELECT statement is executed.3The settings on the HiRDB client prevail.2
WITH SHARE LOCKWITH SHARE LOCK appliedFOR UPDATE is applied when the SELECT statement is executed.3This is changed to 2 if the data guarantee level is 0 or 1.
WITH EXCLUSIVE LOCKWITH EXCLUSIVE LOCK appliedFOR UPDATE is applied when the SELECT statement is executed.5This is changed to 2 if the data guarantee level is 0 or 1.4
WITHOUT LOCK[WAIT]WITHOUT LOCK appliedThe data guarantee level is 1 (the lock option is not applied).This is changed to 1 if the data guarantee level is 0.
WITHOUT LOCK NOWAITWITHOUT LOCK NOWAIT appliedThe data guarantee level is 1 (the lock option is not applied).This is changed to 0 if the data guarantee level is non-zero.
1 The lock option associated with the value specified in the PDISLLVL client environment definition is applied to the SQL statement. If the value is 0, 1, or 2, WITHOUT LOCK NOWAIT, WITHOUT LOCK WAIT, or WITH SHARE LOCK is applied, respectively. The default is application of WITH SHARE LOCK to the SQL statement.
2 Inherits the value specified in the PDISLLVL client environment definition. The default is 2.
3 If Y is specified in the pd_hb_get_lock operand of the foreign server information definition, the FOR UPDATE specification is assumed during execution of the SELECT statement. For details about foreign server information definitions, see the manual HiRDB Version 8 System Definition.
4 DB2 does not have an equivalent function. Even when WITH EXCLUSIVE LOCK is specified, lock in the shared mode takes effect.
5 Application of FOR UPDATE in ORACLE may cause errors in DISTINCT, GROUP BY, set functions, and aggregate functions. When using DISTINCT, GROUP BY, set functions, or aggregate functions, consider ways to prevent specification of WITH EXCLUSIVE LOCK in HiRDB.

Table D-2 Lock on foreign server with lock option specified with WITH ROLLBACK and NO WAIT specifications

Lock optionLock on foreign server
HiRDBXDM/RD E2OracleDB2
WITH ROLLBACKWITH ROLLBACK appliedNot applicable1, 5Not applicable2
NO WAITNO WAIT appliedNot applicable4Not applicable1Not applicable3
1 Because no lock is performed on the foreign server, lock contention does not occur. If HiRDB internally specifies FOR UPDATE, NO WAIT is applied.
2 In DB2, if the value of RESOURCE TIMEOUT is 0, the result is the same as WITH ROLLBACK. If the value is not 0, the transaction is rolled back when the lock request times out.
3 Waits until locked resources are allocated, a time-out occurs, or a deadlock is detected. When time-out or deadlock is detected, the transaction is rolled back.
4 Because this item is not available in XDM/RD E2, an error occurs on the foreign server side.
5 WITH ROLLBACK cannot be specified in an update SQL statement.

(3) Cursors

A portable cursor cannot be used on a foreign table or a view table having a foreign table as a base table. In other words, any of the following specifications is not supported:

(4) Update SQL statements that cannot be executed on foreign servers

  1. When the target of row insertion, updating, or deletion is a foreign table, you cannot specify an INSERT, UPDATE, or DELETE statement that spans multiple foreign servers. Whether or not execution is possible is determined by the Hub optimization information definition and the content of the SQL statement. For details, see the notes on the Hub optimization information definition in the manual HiRDB Version 8 System Definition.
  2. A syntax that cannot be executed by the foreign server cannot be used in an update SQL statement.
  3. When the foreign server is DB2, an update SQL statement that specifies one of its foreign tables cannot be executed.
  4. When the table into which a row is to be inserted is a foreign table, an INSERT statement that includes a set operation in the query expression body cannot be executed.
  5. When WITH ROLLBACK is not supported by the foreign server, WITH ROLLBACK cannot be specified in an update SQL statement for one of its foreign tables.

(5) Operation by row

By-row operations cannot be performed on foreign tables.

(6) LOCK statement

If the lock on a foreign table specified in the LOCK statement fails, the transaction that executed the LOCK statement is rolled back.

(7) Operations on foreign server

Data greater than 256 bytes cannot be acquired from a foreign server.

If a column greater than or equal to 256 bytes on a foreign server is defined as a column in a foreign table (length: n bytes: n[Figure] 255), the first n bytes are acquired. In this case, the definition length on the foreign server is assigned to the indicator variable.

(8) Subquery

Subqueries cannot be specified in SQL statements that access a foreign table. View tables containing a subquery cannot be specified on a mixed basis with foreign tables.

(9) Named derived tables (Foreign server: HiRDB)

If a view table meeting the conditions as an inner derived table on a foreign server is defined as a foreign table, and if the foreign table is specified in a query that satisfies the conditions as an inner derived table, an error may occur on the foreign server.

(10) Named derived tables (Foreign server: XDM/RD E2)

If 14 or more double-byte characters are used in a column name for a named derived table that is derived from a foreign table, and the named derived table becomes an inner derived table, an error may occur on the foreign server.

(11) Embedded variables and ? parameters

  1. If an embedded variable and a ? parameter of any of the character types listed below are specified in an item other than a value expression, the processing is not executed on HiRDB, even if the actual length of the embedded variable and the ? parameter is greater than the value specified in the pd_hub_opt_data_len operand of the Hub optimization information definition. In this case, the processing is executed on the foreign server, and an error may occur. For details about the Hub optimization information definition, see the manual HiRDB Version 8 System Definition.
    • Fixed-length character type
    • Variable-length character type
    • Fixed-length national character type
    • Variable-length national character type
    • Fixed-length mixed character type
    • Variable-length mixed character type
  2. If an embedded variable and a ? parameter that require data conversion between character data and numeric data, an unexpected result may be produced depending on the foreign server, and a runtime error may occur.
  3. If the number of ? parameters that can be executed on a foreign server is exceeded, an unexpected result may be produced, depending on the foreign server, and a runtime error may occur.

(12) Hexadecimal character string literal

If the value X'00' is included, a hexadecimal character string literal is specified on the foreign server. Therefore, some foreign servers may generate a runtime error.

(13) Number of retrieval items for retrieval executed on the foreign server

A runtime error may occur on a foreign server when the number of retrieval items that can be executed is less than on HiRDB.

(14) Correlation name

  1. Any of the following names occurring in correlation-name can cause a run-time error:
    PDHUBxx:
    where xx denotes a 1-digit integer greater than or equal to 1
    Example: SELECT C1 FROM FT1 PDHUB1,FT2
    [Figure]
    SELECT C1 FROM T1 PDHUB1,T2 PDHUB1
    Legend: T1: Base table of FT1
    T2: Base table of FT2
  2. Specifying a correlation name identical to the table identifier for the base table for a foreign table can cause a run-time error on some foreign servers.
    Example: SELECT C1 FROM FT1 T1
    [Figure]
    SELECT C1 FROM T1 T1
    Legend: T1: Base table of FT1
  3. Any of the following names occurring on the foreign server can cause a run-time error:
    PDHUBxx:
    where xx denotes a 1-digit integer greater than or equal to 1.
    Example: SELECT C1 FROM FT1
    [Figure]
    SELECT C1 FROM T1 PDHUB1,T2 PDHUB1
    Legend: PDHUB1: Base table of FT1

(15) Conversion and substitution into national character data types

When there is a character string literal that must be converted or substituted into a national character data type and the value includes a 1-byte character, an error may occur in the foreign server during SQL execution in the following cases: