Configuring the federated server to access data sources
After you install and set up the federated server and create a federated database, plan and then configure access to the data sources.
The process for configuring access to data sources is the same, regardless of the data source. What differs in the process are the particular settings that you apply as you complete each configuration task for each data source. The process described here is generic; for complete information on configuring a particular data source, see the detailed configuration information for the data source.
Review how to name and specify objects
During the configuration process, you use Db2® SQL statements to register objects. Before you perform any configuration tasks, be sure that you understand the federated naming rules and understand how the use of quotation marks in SQL statements affects the case-sensitivity of the objects that you specify.
Register the wrapper
After you use the federation installation program to install support for a data source, you must register the corresponding wrapper. A wrapper is a set of library files that the federated server uses to communicate with the data source and to retrieve data from it. For each type of data source that you want to access, you register one wrapper. For example, to access one table in Db2, one table in Db2 for IBM® i, and one table in Teradata, you register two wrappers: the DRDA wrapper for the Db2 data sources and the Teradata wrapper for Teradata data sources.
As part of your planning, decide whether or not to use the default wrapper name or assign a different name to the wrapper, and review the wrapper options that are available for each data source that you are configure. Each data source has one or more required wrapper options that you must set.
Register the server definitions
Before you can access specific data source objects, you register one or more server definitions. For a relational data source, a server definition represents a remote database, a database partition, or a node. For a nonrelational data source, a server definition often maps to other types of external data objects. Each data source has required and optional parameters that you must specify when you register the server definition.
As part of your planning, review the server options that are available for the specific data source that you are configuring. Each data source has one or more required server options that you must set.
Register user mappings
If a remote data source requires user authentication and if a user's remote user ID and remote password are different from the ones that the user uses to connect to the federated database, you define a user mapping. A user mapping is an association between a federation server authorization ID and a data source user ID and password. By default, user mappings are stored in the catalog on the federated server.
As part of your planning, decide if you want to store user mapping information in an external repository, such as on an LDAP server or in a file. To use an external repository, you must create a plug-in that provides the federated server with the interface to the repository.
Update data source statistics
For each relational data source that you plan to access, use a command that is equivalent to the Db2 RUNSTATS command to update the statistics at the remote data source. Then when you create nicknames, the most up-to-date statistical information is added to the system catalog in the federated database. Later when you run a query on the data source, the query optimizer uses this information to determine the most efficient way to perform the query.
After you create nicknames, statistics at the data source might change. When statistics for a relational data source change, use the SYSPROC.NNSTAT stored procedure to update the statistical information in the system catalog. When statistics for a nonrelational data source change, use the tool that is provided by the nonrelational data source, or manually update the statistics in the SYSTAT catalog views.
Register nicknames
You create a nickname for each relational data source object that you want to access. For some nonrelational data sources, you define a fixed list of input and output columns when you register the nickname. Each column that you specify is mapped to a particular field, column, or element in the data source object.
As part of your planning, review the nickname and column options that are available for the data source that you are configuring. Some data sources have required nickname and column options that you must set.
Perform additional configuration tasks
- Create index specifications
- You can define an index specification for objects that do not have an index. For example, you create an index specification when a table acquires a new index or if the data source object, such as a view, does not have an index.
- Define alternative data type mappings
- In the federated system, there are default mappings between the data source data types and the federated database data types. For relational data sources, you can define alternative data type mappings. For example, you can change a type mapping for all data source objects located on a specific server or change a type mapping for a specific data source object, data source type, or data source object and type.
- Define alternative function mappings
- In the federated system, there are default function mappings between the built-in data source functions and the built-in federated database functions. For relational data sources, you can define alternative function mappings. For example, you can define an alternative function mapping when you want to use a new built-in function or a user-defined function that is available at the data source but for which the federated database lacks a mapped function.