If a query includes accesses to foreign tables, access path information consists of information about the local HiRDB retrieval processing and information about the foreign server retrieval processing (prediction).
HiRDB first predicts the access path within the foreign server and obtains information about the foreign server retrieval processing (prediction). Next, based on this prediction, HiRDB determines information about the local HiRDB retrieval processing.
Information about the foreign server retrieval processing (prediction) is predicted by HiRDB in order to determine information about the local HiRDB retrieval processing; it is not the actual access path used for retrieval by the foreign DBMS. Information about the access path used by the foreign DBMS must be obtained at the foreign DBMS. If the foreign DBMS is a HiRDB, you can obtain the access path information file by executing an SQL statement with 1 or 2 specified in the PDVWOPTMODE operand of the foreign server definition.
--------- FOREIGN SERVER(PREDICTION) : aa...a ---------
Derived Table :bb...b
Foreign Server :cc...c(dd...d) [ee...e ff...f]
SQL :gg...g
Table Mapping :hh...h(hh...h) [ii...i(ii...i)],...
Var Mapping :jj...j[kk...k],...
Foreign Cost :SERVER-ll...l,COMM-mm...m
----- QUERY EXPRESSION BODY ID : ... ----- ...........1
:
----- QUERY ID : ... ----- ...........................2
:
JOIN .................................................3
:
SCAN .................................................4
: |
- Explanation
- Set operation information; for details, see 17.5.2 Set operation information.
- Query processing information; for details, see 17.5.3 Query processing information.
- Join processing information; for details, see 17.5.4 Join processing information.
- Base table retrieval processing information; for details, see 17.5.5 Base table retrieval processing information.
- aa...a
- Displays the access number of the query that includes an access to a foreign table. The access number is assigned sequentially to each foreign server retrieval processing (prediction) information item beginning at 1.
- bb...b
- Displays the internally created table name (FOREIGNSQL table-number) for the result of retrieval from the foreign server. The table number is a sequential 3-digit integer beginning at 1.
- cc...c(dd...d) [ee...e ff...f]
- Displays information about the foreign server and foreign DBMS.
- cc...c
- Displays the name of the foreign server.
- dd...d
- Displays the name of the back-end server assigned to access the foreign server.
- ee...e
- Displays the server type of the foreign DBMS.
- ff...f
- Displays the server version of the foreign DBMS that was specified when the foreign server was defined.
- gg...g
- Displays the SQL statement that is issued to the foreign server.
- Some SQL statement components are displayed using the foreign DBMS format and some are displayed without using the foreign DBMS format.
- Components that are displayed using the foreign DBMS format:
- Table identifier
The table identifier is converted to the actual table identifier defined in the foreign DBMS. If the table identifier defined in the foreign server is different from the table identifier defined in the foreign DBMS, the utility displays their correspondence in Table Mapping.
- Variables
Embedded variables, ? parameters, SQL variables, SQL parameters, USER, CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP are converted to the foreign DBMS's variable format.
If a specified condition includes a column of another foreign table, the utility may convert it to the variable depending on the join method.
Correspondence of variables before and after conversion is displayed in Var Mapping.
If the foreign DBMS is ORACLE, :?-number is displayed for variables (?-number: sequential number assigned to :?-number, which is in the SQL statement issued to the foreign server, from left to right beginning at 1).
- Components that are displayed without using the foreign DBMS format:
- When executing actual retrieval, the utility issues to the foreign server an SQL statement in the format usable by the foreign DBMS.
- Lock option
The utility specifies the lock option as required. The lock option is displayed in the format usable by HiRDB.
- Update option
The utility adds the update option (FOR READ ONLY and FOR UPDATE clauses) as required and displays it in the format usable by HiRDB.
- hh...h(hh...h)[ii...i(ii...i)],...
- Displays the correspondence of the table identifier hh...h and correlation name (hh...h) contained in the SQL statement issued to the foreign server to the table identifier ii...i and correlation name (ii...i) contained in the SQL statement issued to the local HiRDB. If the correlation name is not used, neither (hh...h) nor (ii...i) is displayed.
- If there is more than one table, each set of information is separated by a comma.
- If the table identifiers are the same in both SQL statements, the correspondence is not displayed (same applies to the correlation names).
- jj...j[kk...k],...
- Displays the correspondence between the variable jj...j contained in the SQL statement issued to the foreign server and the embedded variable, ? parameter, SQL variable, SQL parameter, USER, CURRENT_DATE, CURRENT_TIME, or CURRENT_TIMESTAMP, or column specification kk...k contained in the SQL statement issued to the local HiRDB.
- If there is more than one variable, each set of information is separated by a comma.
- If the foreign DBMS is ORACLE, :?-number is displayed for variable jj...j; otherwise, ?(?-number) is displayed for variable jj...j (?-number: sequential number assigned to :?-number, which is in the SQL statement issued to the foreign server, from left to right beginning at 1).
- For kk...k, the actual value specified in the SQL statement is usually displayed as is, but in the case of an embedded variable or ? parameter, ?(?-number) is displayed (?-number: sequential number assigned to the embedded variable or ? parameter, which is in the SQL statement issued to the local HiRDB, from left to right beginning at 1).
- SERVER-ll...l,COMM-mm...m
- Displays the predicted cost for the access to the foreign server.
- ll...l
- Displays the predicated cost for foreign server internal processing.
- mm...m
- Displays the predicted cost for communication from the foreign server to the local HiRDB.