Joining the result sets of federated procedures

You can join the result sets returned by a federated procedure with the DB2FEDGENTF command.

Before you begin

To use the -create parameter of the DB2FEDGENTF command, you must have either the DBADM authority on the federated database or a combination of the following authorities or privileges on the federated database:
1. You must have one of the following authorities:
  - CREATE_EXTERNAL_ROUTINE
  - CREATETAB
2. You also must have one of the following authorities or privileges:
  - Write privilege for the install_dir/sqllib/function directory, where install_dir is the directory where the federated server is installed
  - IMPLICIT_SCHEMA authority if the implicit or explicit schema name of the function does not exist
  - CREATEIN privilege on the schema if the schema name of the function exists
To use the -drop parameter of the DB2FEDGENTF command you must have at least one of the following authorities or privileges on the federated database:
- DROPIN privilege on the schema for the object
- OWNER of the object
- DBADM authority

About this task

You join the result sets that are returned by federated procedures to join data from local and remote tables, particularly in systems that only allow access through federated procedures.

Procedure

Create and register a table function with the -create parameter of DB2FEDGENTF command, for example:

DB2FEDGENTF –db sample –u user1 –p password1
  -create
    -stpn S1_INVENTORY 
    -tfn S1_INVTRY_TF 
    -c ‘PART_NUM CHAR(10), S1_QTY INT'

Use a join to combine the data in the federated procedure result sets.
You can join the result set of a federated procedure with a local table or you can join the result sets from two federated procedures.

Examples

In the following examples, the stored procedures named ProINVENTORY and ProINVENTORY2 exist and each return the inventory of two suppliers as result sets. The PARTS table also exists and contains the manufacturer's inventory with the following column names and types:

Table 1. Columns of the PARTS table
Column name	Column type
PART_NUM	CHAR(10)
PART_DESC	VARCHAR(400)
INVENTORY	INT

Joining result sets with local tables example

In this example, the inventory between one supplier and the manufacturer is combined to determine the total inventory available:

Use the CREATE PROCEDURE statement to create the S1_INVENTORY federated procedure from the ProINVENTORY stored procedure:
```
CREATE PROCEDURE S1_INVENTORY
 SOURCE ProINVENTORY
 FOR SERVER ORA1;
```

Use the DB2FEDGENTF command to create the S1_INVTRY_TF table function that includes the PART_NUM and S1_QTY result set:

DB2FEDGENTF –db sample –u user1 –p password1
  -create
    -stpn S1_INVENTORY 
    -tfn S1_INVTRY_TF 
    -c ‘PART_NUM CHAR(10), S1_QTY INT'

Use a join to combine the data of the PARTS table with the result set of the S1_INVTRY_TF table function:

SELECT a.PART_NUM, a.PART_DESC,	a.INVENTORY + b.S1_QTY as MAX_QTY
 FROM PARTS a, TABLE(S1_INVTRY_TF()) b
 WHERE a. PART_NUM = b. PART_NUM

Joining two result sets example

In this example, the manufacturer combines the inventory from both suppliers to determine their total available inventory:

Create the S1_INVTRY_TF table function for the first supplier:
1. Use the CREATE PROCEDURE statement to create the S1_INVENTORY federated procedure from the ProINVENTORY stored procedure:
```
CREATE PROCEDURE S1_INVENTORY
 SOURCE ProINVENTORY
 FOR SERVER ORA1;
```
2. Use the DB2FEDGENTF command to create the S1_INVTRY_TF table function that includes the PART_NUM and S1_QTY result set:
```
DB2FEDGENTF –db sample –u user1 –p password1
  -create
    -stpn S1_INVENTORY 
    -tfn S1_INVTRY_TF 
    -c ‘PART_NUM CHAR(10), S1_QTY INT'
```
Create the S2_INVTRY_TF table function for the second supplier:
1. Use the CREATE PROCEDURE statement to create the S2_INVENTORY federated procedure from the ProINVENTORY2 stored procedure:
```
CREATE PROCEDURE S2_INVENTORY
 SOURCE ProINVENTORY2
 FOR SERVER SYBA1;
```
2. Use the DB2FEDGENTF command to create the S2_INVTRY_TF table function that includes the PART_NUM and S2_QTY result set:
```
DB2FEDGENTF –db sample –u user1 –p password1
  -create
    -stpn S2_INVENTORY 
    -tfn S2_INVTRY_TF 
    -c ‘PART_NUM CHAR(10), S2_QTY INT'
```

Use a join to combine the result sets of the S1_INVTRY_TF and S2_INVTRY_TF table functions:

SELECT a.PART_NUM, a.PART_DESC, b.S1_QTY + c.S2_QTY as MAX_SUPP_QTY
 FROM PARTS a, TABLE(S1_INVTRY_TF()) b, TABLE(S2_INVTRY_TF()) c
 WHERE a. PART_NUM = b. PART_NUM
 AND a. PART_NUM = c. PART_NUM