Distributed requests for querying data sources - examples

The examples in this topic illustrate distributed requests with a subquery, set operators, and a join operation.

In the following examples, the federated server is configured to access a Db2® for z/OS® data source, a Db2 for IBM® i 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.

zOS_EMPLOYEES: Nickname for a table on a Db2 for z/OS data source that contains employee information.
SYSTEMi_EMPLOYEES: Nickname for a table on a Db2 for IBM i data source that contains employee information.
ORA_EMPLOYEES: Nickname for a table on an Oracle data source that contains employee information.
ORA_REGIONS: Nickname for a table on an Oracle data source that contains information about the regions that the employees live in.

The following examples illustrate the three SQL conventions used with distributed requests, using the nicknames defined for each of the tables.

Example: A distributed request with a subquery

SYSTEMi_EMPLOYEES contains the phone numbers of employees who live in Asia. It also contains the region codes associated with these phone numbers, but it does not list the regions that the codes represent. ORA_REGIONS lists both codes and regions. The following query uses a subquery to find the region code for China. Then it uses the region code to return a list of those employees in SYSTEMi_EMPLOYEES who have a phone number in China.

SELECT name, telephone FROM db2admin.SYSTEMi_employees
   WHERE region_code IN
   (SELECT region_code FROM dbadmin.ora_regions
   WHERE region_name = 'CHINA')

Example: A distributed request with set operators

The federated server supports three set operators: UNION, EXCEPT, and INTERSECT.

Use the UNION set operator to combine the rows that satisfy any of two or more SELECT statements.
Use the EXCEPT set operator to retrieve those rows that satisfy the first SELECT statement but not the second.
Use the INTERSECT set operator to retrieve those rows that satisfy both SELECT statements.

All three set operators can use the ALL operand to indicate that duplicate rows are not to be removed from the result. This eliminates the need for an extra sort.

The following query retrieves all employee names and region codes that are present in both SYSTEMi_EMPLOYEES and zOS_EMPLOYEES, even though each table resides in a different data source.

SELECT name, region_code
   FROM as400_employees
INTERSECT
SELECT name, region_code
   FROM zOS_employees

Example: A distributed request for a join

A relational join produces a result set that contains a combination of columns retrieved from two or more tables. You should specify conditions to limit the size of the rows in the result set.

The query below combines employee names and their corresponding region names by comparing the region codes listed in two tables. Each table resides in a different data source.

SELECT t1.name, t2.region_name
   FROM dbadmin.SYSTEMi_employees t1, dbadmin.ora_regions t2
   WHERE t1.region_code = t2.region_code