Queries submitted to the federated database can request results that are yielded by a single data source; but typically they request results that are yielded by multiple data sources. Because a typical query is distributed to multiple data sources, it is called a distributed request.
This section:
In general, a distributed request uses one or more of three SQL conventions to specify where data is to be retrieved from: subqueries, set operators, and join subselects. This section provides examples within the context of the following scenario: A federated server is configured to access a DB2 Universal Database for OS/390 data source, a DB2 Universal Database for AS/400 data source, and an Oracle data source. Stored in each data source is a table that contains employee information. The federated server references these tables by nicknames that point to where the tables reside: UDB390_EMPLOYEES, AS400_EMPLOYEES, and ORA_EMPLOYEES. (Nicknames do not have to reference data sources; the ones in this scenario do so only to underline the point that the tables reside in different RDBMSs.) In addition to ORA_EMPLOYEES, the Oracle data source has a table, nicknamed ORA_COUNTRIES, that contains information about the countries that the employees live in.
Table AS400_EMPLOYEES contains the phone numbers of employees who live in Asia. It also contains the country codes associated with these phone numbers, but it does not list the countries that the codes represent. Table ORA_COUNTRIES, however, does list both codes and countries. The following query uses a subquery to find out the country code for China; and it uses SELECT and WHERE clauses to list those employees in AS400_EMPLOYEES whose phone numbers require this particular code.
SELECT name, telephone
FROM djadmin.as400_employees
WHERE country_code IN
(SELECT country_code
FROM djadmin.ora_countries
WHERE country_name = 'CHINA')
The federated server supports three set operators:
Use this set operator to combine the rows that satisfy any of two or more SELECT statements.
Use this set operator to retrieve those rows that satisfy the first SELECT statement but not the second.
Use this set operator to retrieve those rows that satisfy both SELECT statements.
All three set operators might have the ALL operand to indicate that duplicate rows are not be removed from the result, thus eliminating the need for an extra sort.
The following query retrieves all employee names and country codes that are present in both the AS400_EMPLOYEES and UDB390_EMPLOYEES tables, even though each table resides in a different data source.
SELECT name, country_code
FROM as400_employees
INTERSECT
SELECT name, country_code
FROM udb390_employees
A relational join produces a result set that contains a combination of columns retrieved from two or more tables. Be aware that you should specify conditions to limit the size of the result set's rows.
The query below combines employee names and their corresponding country names by comparing the country codes listed in two tables. Each table resides in a different data source.
SELECT t1.name, t2.country_name
FROM djadmin.as400_employees t1, djadmin.ora_countries t2
WHERE t1.country_code = t2.country_code
Federated system users can use parameters called server options to supply the global catalog with information that applies to a data source as a whole, or to control interaction between DB2 and a data source. For example, to catalog the identifier of the instance that serves as the basis of a data source, the database administrator assigns that identifier as a value to the server option "node".
Several server options address a major area of interaction between DB2 and data sources: optimization of queries. For example, just as you can use the column option "varchar_no_trailing_blanks" to inform the DB2 optimizer of specific data source VARCHAR columns that have no trailing blanks, so can you use a server option--also called "varchar_no_trailing_blanks"--to inform the optimizer of data sources whose VARCHAR columns are all free of trailing blanks. For a summary of how such information helps the optimizer to create an access strategy, see Table 27.
In addition, you can set the server option "plan_hints" to a value that enables DB2 to provide Oracle data sources with statement fragments, called plan hints, that help Oracle optimizers do their job. Specifically, plan hints can help an optimizer to decide matters such as which index to use in accessing a table, and which table join sequence to use in retrieving data for a result set.
Typically, the database administrator sets server options for a federated system. However, a programmer can make good use of those options that help to optimize queries. For example, suppose that for data sources ORACLE1 and ORACLE2, the plan_hints server option is set to its default, 'n' (no, do not furnish this data source with plan hints). Also suppose that you write a distributed request for data from ORACLE1 and ORACLE2, and that you expect that plan hints would help the optimizers at these data sources improve their strategies for accessing this data. You could override the default with a setting of 'y' (yes, furnish the plan hints) while your application is connected to the federated database. When the connection is completed, the setting would automatically revert to 'n'.
To enforce a server option setting for the duration of a connection to the federated database, use the SET SERVER OPTION statement. To ensure that the setting takes effect, you must specify the statement right after the CONNECT statement. In addition, it is advisable to prepare the statement dynamically.
For documentation of the SET SERVER OPTION statement, see the SQL Reference. For descriptions of all server options and their settings, see the Administration Guide: Implementation.