Federation: Selecting data in a federated system

This topic illustrates a scenario in which a federated server access multiple data sources and provides examples of SELECT statements.

Example: A federated server is configured to access a DB2® for z/OS® data source, a DB2 for System i® data source, and an Oracle data source. Stored in each data source is a table that contains sales information. This configuration is depicted in the following figure.

Figure 1. Sample federated system with DB2 and Oracle data sources A sample federated system showing DB2

The sales tables include columns that record the customer number (CUST_NO), the quantity ordered (QUANTITY), and the product number ordered (PROD_NO). Also, a local table in the federated database contains price information. The price table includes columns that record the product number (PROD_NO) and the current price (PRICE).

The nicknames for the remote data source objects are stored in the SYSCAT.TABLES tables, as shown in the following tables. The TYPE column indicates the type of object, such as nickname (N), local table (T), or view (V).

Table 1. Data source information
Data source object name	Type of object	Location
PRICES	Local table	Federated database
EUROPE_SALES	Remote table	DB2 for z/OS database
US_SALES	Remote table	DB2 for System i database
JAPAN_SALES	Remote table	Oracle database
SALES_BY_REGION	Remote view	Oracle database

Table 2. SYSCAT tables
TABNAME	TYPE
PRICES	T
FED_PRICES	N
Z_EU_SALES	N
Si_US_SALES	N
ORA_JAPANSALES	N
ORA_REGIONSALES	N
…

To select data using a nickname, all of the following privileges must be true:

The privileges held by the authorization ID of the statement must include the SELECT privilege on the nickname (for the federated database to accept the request).
The user ID at the data source must have the SELECT privilege on the underlying table object (for the data source to accept the request).
The user ID at the data source must be mapped to the authorization ID at the federated server through a user mapping.

The following SELECT statement examples use the sample federated system described above.

Example: Querying a single data source:

Z_EU_SALES contains the products ordered by your European customers. It also includes the quantity ordered at each sale. This query uses a SELECT statement with an ORDER BY clause to list the sales in Europe and sorts the list by customer number:

SELECT CUST_NO, PROD_NO, QUANTITY
     FROM Z_EU_SALES
     ORDER BY CUST_NO

Example: Joining a local data source and a remote data source:

PRICES is a table that resides in the federated database. It contains the price list for the products that you sell. You want to select the prices from this local table that correspond to the products listed in Z_EU_SALES. You also want to sort the result set by the customer number.

SELECT sales.CUST_NO, sales.PROD_NO, sales.QUANTITY
     FROM Z_EU_SALES sales, PRICES
     WHERE sales.PROD_NO=PRICES.PROD_NO
     ORDER BY sales.CUST_NO

Example: Querying multiple remote data sources:

You want to gather all the sales information from each region and order the result set by product number.

WITH GLOBAL_SALES (Customer, Product, Quantity) AS
     (SELECT CUST_NO, PROD_NO, QUANTITY FROM Z_EU_SALES
       UNION ALL
     SELECT CUST.NO,PROD.NO, QUANTITY FROM iS_US_SALES
       UNION ALL
     SELECT CUST.NO,PROD.NO, QUANTITY FROM ORA_JAPANSALES)
     SELECT Customer, Product, Quantity
     FROM GLOBAL_SALES
     ORDER BY Product

A view at the Oracle data source lists the sales for Japan and Indonesia. The nickname for this view is ORA_SALESREGION. You want to combine this information with the sales from the United States and display the product prices next to each sale.

SELECT us_jpn_ind.CUST_NO, us_jpn_ind.PROD_NO,
        us_jpn_ind.QUANTITY, us_jpn_ind.QUANTITY*PRICES.PRICE
     AS SALEPRICE FROM
     (SELECT CUST_NO, PROD_NO, QUANTITY
     FROM ORA_SALESREGION
     UNION ALL
     SELECT CUST_NO, PROD_NO, QUANTITY
     FROM iS_US_SALES us ) us_jpn_ind,PRICES
     WHERE us_jpn_ind.PROD_NO = PRICES.PROD_NO
     ORDER BY SALEPRICE DESC

Selecting data in a federated system - examples