Section 2.4. Federation

2.4. Federation

Database federated support in DB2 allows tables from multiple databases to be presented as local tables to a DB2 server. The databases may be local or remote; they can also belong to different RDBMSs. While Chapter 1 briefly introduced federated support, this section provides an overview of how federation is implemented.

First of all, make sure that your server allows federated support: The database manager parameter FEDERATED must be set to YES.

DB2 uses NICKNAME, SERVER, WRAPPER, and USER MAPPING objects to implement federation. Let's consider the example illustrated in Figure 2.7.

Figure 2.7. An overview of a federation environment

The DB2 user db2user connects to the database db2db. He then issues the statement:

 SELECT * FROM remote_sales 

The table remote_sales, however, is not a local table but a nickname, which is a pointer to a table in another database, possibly in another server and from a different RDBMS. A nickname is created with the CREATE NICKNAME statement, and requires a SERVER object (aries in the example) and the schema and table name to be accessed at this server (csmmgr.sales).

A SERVER object is associated to a WRAPPER. A wrapper is associated to a library that contains all the code required to connect to a given RDBMS. For IBM databases like Informix, these wrappers or libraries are provided with DB2. For other RDBMSs, you need to obtain the IBM DB2 Information Integrator software. In Figure 2.7, the wrapper called informix was created, and it is associated to the library db2informix.dll.

To access the Informix table csmmgr.sales, however, you cannot use the DB2 user id and password directly. You need to establish a mapping between the DB2 user id and an Informix user id that has the authority to access the desired table. This is achieved with the CREATE USER MAPPING statement. Figure 2.7 shows how the DB2 user db2user and the Informix user informixuser are associated with this statement.