Federated object changes

You can create, alter, and drop wrappers, server definitions, user mappings, nicknames, and federated stored procedures.

Overview of federated systems

The various organizations in a large enterprise often use different database management systems to store and access their data. A federated system transparently unifies the information from the various sources, which allows the enterprise to use the full value of the data.

A federated system is a special type of distributed database management system. A federated system consists of a DB2® instance that operates as a federated server, a database that acts as the federated database, one or more data sources, and clients (users and applications) that access the database and data sources.

The DB2 server in a federated system is referred to as the federated server. Any number of DB2 instances can be configured to function as a federated server. The DB2 instance that manages the federated system is called a server because it responds to requests from users and client applications. Users and applications interface with the federated database that is managed by the federated server.

A federated system is typically created on a DB2 for Linux, UNIX, and Windows system by completing the following steps:
  • Updating the database manager configuration properties for SVCENAME and FEDERATED
  • Creating a DB2 database to manage federated access
  • Installing any prerequisite software that is required by the target data sources, and configuring network access to the systems on which those data sources reside
  • Creating wrapper, server, and user mapping objects for each data source that is to be accessed
  • Creating nicknames for the objects that are to be accessed at each data source

Supported federated objects and data sources

With IBM® Data Studio, you can create, alter, and drop wrappers, server definitions, user mappings, nicknames, and federated stored procedures:
The mechanism that the federated database uses to connect to and retrieve data from a data source. A wrapper must be created for each type of data source that is to be accessed. Supported data sources are automatically discovered when you create a wrapper.
Server definitions
Information that identifies and defines the data source to the federated database.
User mappings
The association between the authorization ID on the federated server and the information that is required to connect to the remote data source. The user ID and password that you use to access the federated server are mapped to the user ID and password that you use to access the data source server.
A local name that you create to identify each object at the data source that you want to access. The object that the nickname identifies is referred to as a data source object. You can manually create nicknames, or you can have IBM Data Studio discover the remote schema and remote tables for the deployed data sources on which nicknames can be created.
Restriction: Automatic discovery for creating nicknames is not supported for JDBC data sources.
Table 1. Data source objects that can have nicknames
Data source Object
DB2 for Linux, UNIX, and Windows Nicknames, materialized query tables, tables, and views
DB2 for i Tables, views, physical files, logical files, and table types
DB2 for z/OS® Tables and views
Federated stored procedures
A local stored procedure that is mapped to a stored procedure at the data source.
Restriction: You can define and deploy changes for federated objects only for these data sources:
DRDA® wrappers
  • DB2 for Linux, UNIX, and Windows
  • DB2 for i
  • DB2 for z/OS
Oracle wrappers
Oracle data sources
Tip: You can use the SQL and XQuery editor to create and run statements for other federated data sources, see SQL and XQuery editor for more information.

Creating federated objects

When creating federated objects for a federated system, you must create the objects in the following order:
  1. Wrapper
  2. Server definitions
  3. User mappings
  4. Nicknames and federated stored procedures

The definitions of the federated objects that you create are stored in the federated database.

For detailed information about configuring your federated server for accessing data sources, see Configuring data sources in the version of the IBM DB2 Database for Linux, UNIX, and Windows Information Center that matches your federated server.

Generated change commands for federated objects

As with any change that you make by using a change plan, you generate change commands for your federated object changes that you then run against the federated system.

When you alter a nickname, an ALTER NICKNAME statement is generated only if you change the constraint for the nickname or change the data type of the column. For all other nickname changes, a DROP NICKNAME statement and a CREATE NICKNAME statement are created.

Data preservation and data maintenance commands are not included in the generated change commands. Data does not need to be preserved because the federated object changes do not affect remote tables. You can use always update statistics by using the utility actions that are available by right-clicking an object in the Object List.