This section provides an overview of the elements of a DB2 federated system, an overview of the tasks that administrators and users of the system perform, and explanations of the concepts associated with these tasks.
A DB2 federated system is a distributed computing system that consists of:
In a DB2 installation, any number of DB2 instances can be configured to function as federated servers.
Each data source consists of an instance of a relational database management system plus the database or databases that the instance supports. The data sources in a DB2 federated system can include Oracle instances and instances of the members of the DB2 family.
The data sources are semi-autonomous. For example, the federated server can send queries to Oracle data sources at the same time that Oracle applications can access these data sources. A DB2 federated system does not monopolize or restrict access to Oracle or other data sources (beyond integrity and locking constraints).
To end users and client applications, the data sources appear as a single collective database. In actuality, users and applications interface with a database, called the federated database, that is within the federated server. To obtain data from data sources, they submit queries in DB2 SQL to the federated database. DB2 then distributes the queries to the appropriate data sources. DB2 also provides access plans for optimizing the queries (in some cases, these plans call for processing the queries at the federated server rather than at the data source). Finally, DB2 collects the requested data and passes it to the users and applications.
Queries submitted from the federated server to data sources must be read-only. To write to a data source (for example, to update a data source table), users and applications must use the data source's own SQL in a special mode called pass-through.
This section introduces concepts that are associated with tasks that users perform to establish and use a federated system. (In this section and those that follow, the term users refers to all types of personnel who work with federated systems; for example, database administrators [DBAs], application programmers, and end users).
The following list of tasks identifies the types of users who typically perform the tasks. Be aware that other types of users can also perform these tasks. For example, the list indicates that DBAs typically create mappings between authorizations to access the federated database and authorizations to access data sources. But application programmers and end users can also perform this task.
To establish and use a DB2 federated system:
In processing a query, the federated server can perform operations that are supported by DB2 SQL but not by the data source's SQL. (This capability is called compensation. Refer to Compensation for details.)
The following sections discuss the concepts mentioned in this task list in the same order in which they appear in the list. Some of these sections also introduce related concepts.
A wrapper is the mechanism by which the federated server communicates with, and retrieves data from, a data source. To implement a wrapper, the server uses routines stored in a library called a wrapper module. These routines allow the server to perform operations such as connecting to a data source and retrieving data from it iteratively.
There are three wrappers:
A wrapper is registered to the federated server with the CREATE WRAPPER statement. Refer to CREATE WRAPPER for details.
After the DBA registers a wrapper that allows the federated server to interact with data sources, the DBA defines those data sources to the federated database. This section:
In defining a data source to the federated database, the DBA supplies a name for the data source as well as information that pertains to the data source. This information includes the type and version of the RDBMS of which the data source is an instance, and the RDBMS's name for the data source. It also includes metadata that is specific to the RDBMS. For example, a DB2 family data source can have multiple databases, and the definition of such a data source must specify which database the federated server can connect to. In contrast, an Oracle data source has one database, and the federated server can connect to the database without needing to know its name. Thus, the name is not included in the federated server's definition of the data source.
The name and information that the DBA supplies is collectively called a server definition. This term reflects the fact that data sources answer requests for data and are therefore servers in their own right. Other terms reflect this fact also. For example:
Values are assigned to server options through the CREATE SERVER, ALTER SERVER, and SET SERVER OPTION statements.
The CREATE SERVER and ALTER SERVER statements set server options to values that persist over successive connections to the data source. These values are stored in the catalog. Consider this scenario: A federated system DBA uses the CREATE SERVER statement to define a new Oracle data source to the federated system. This data source's database uses the same collating sequence that the federated database uses. The DBA wants the optimizer to know about this match, so that the optimizer can take advantage of it to expedite performance. Accordingly, in the CREATE SERVER statement, the DBA sets a server option called COLLATING_SEQUENCE to 'Y' (yes, the collating sequences at the data source and federated database are the same). The setting of 'Y' is recorded in the catalog, and it remains in force while users and applications access the data source.
Some months later, the Oracle DBA changes the Oracle data source's collating sequence. Therefore, the federated system DBA resets COLLATING_SEQUENCE to 'N' (no, the data source's collating sequence is not the same as the federated database's). The DBA uses the ALTER SERVER statement to make this update. The catalog is updated also, and the new setting remains in effect as users and applications continue to access the data source.
The SET SERVER OPTION statement overrides a server option value temporarily, for the duration of a single connection to the federated database. The overriding value does not get stored in the catalog.
To illustrate: A server option called PLAN_HINTS can be set to a value that enables DB2 to supply Oracle data sources with statement fragments, called plan hints, that help Oracle optimizers to do their job. For example, plan hints can help an optimizer to decide what index to use in accessing a table, or what table join sequence to use in retrieving data for a result set.
Consider another scenario: For data sources ORACLE1 and ORACLE2, the PLAN_HINTS server option is set to its default, 'N' (no, do not furnish these data sources with plan hints). Then a programmer writes a distributed request for data from ORACLE1 and ORACLE2; and the programmer expects that plan hints would help the optimizers at these data sources to improve their strategies for accessing this data. Accordingly, the programmer uses the SET SERVER OPTION statement to override the 'N' with 'Y' (yes, furnish plan hints). The 'Y' stays in effect only while the application that contains the request is connected to the federated database; it does not get stored in the catalog.
Refer to CREATE SERVER, ALTER SERVER, and SET SERVER OPTION for more information. Refer to Server Options for descriptions of all server options and their settings.
In the terms "server definition" and "server option", and in the SQL statements discussed in the preceding section, the word "server" refers to data sources only. It does not refer to the federated server, or to DB2 application servers.
The concepts of DB2 application servers and federated servers, however, overlap. As indicated in Distributed Relational Database, an application server is a database manager instance to which application processes connect and submit requests. This is true of a federated server; thus, a federated server is a type of application server. But two main things distinguish it from other application servers:
The federated server can send the distributed request of an authorized user or application to a data source under either of these conditions:
User mappings can be defined and modified with the CREATE USER MAPPING and ALTER USER MAPPING statements. These statements include parameters, called user options, to which values related to authorization are assigned. For example, suppose that a user has the same ID, but different passwords, for the federated database and a data source. For the user to access the data source, it is necessary to map the passwords to one another. This can be done with a CREATE USER MAPPING statement in which the password at the data source is assigned as a value to a user option called REMOTE_PASSWORD.
Refer to CREATE USER MAPPING, ALTER USER MAPPING, and Administration Guide, Design and Implementation for more information. Refer to User Options for descriptions of the user options and their settings.
For the federated server to retrieve data from columns of data source tables and views, the columns' data types at the data source must map to corresponding data types that are already defined to the federated database. DB2 supplies default mappings for most kinds of data types. For example, the Oracle type FLOAT maps by default to the DB2 type DOUBLE, and the DB2 Universal Database for OS/390 type DATE maps by default to the DB2 type DATE. There are no mappings for the data types that DB2 federated servers do not support: LONG VARCHAR, LONG VARGRAPHIC, DATALINK, large object (LOB) types, and user-defined types.
Refer to Default Data Type Mappings for listings of the default data type mappings.
When values from a data source column are returned, they conform fully to the DB2 type in the type mapping that applies to the column. If this mapping is a default, the values also conform fully to the data source type in the mapping. For example, when an Oracle table with a FLOAT column is defined to the federated database, the default mapping of Oracle FLOAT to DB2 DOUBLE will, unless it has been overridden, automatically apply to that column. Consequently, the values returned from the column will conform fully to both FLOAT and DOUBLE.
It is possible to change the format or length of values returned by changing the DB2 type that the values must conform to. For example, the Oracle type DATE is for time stamps. By default, it maps to the DB2 type TIMESTAMP. Suppose that several Oracle table columns have a data type of DATE, and that a user wants queries of these columns to yield times only. The user could then map the Oracle type DATE to the DB2 type TIME, overriding the default. That way, when the columns are queried, only the time portion of the time stamps would be returned.
The CREATE TYPE MAPPING statement can be used to create a modified data type mapping that applies to one or more data sources. The ALTER NICKNAME statement can be used to modify a data type mapping for a specific column of a specific table.
Refer to CREATE TYPE MAPPING, ALTER NICKNAME, and the Application Development Guide for more information.
For the federated server to recognize a data source function, there needs to be a mapping between this function and a corresponding DB2 function that already exists at the server. DB2 supplies default mappings between existing built-in data source functions and built-in DB2 functions. If a user wants to use a data source function that the federated server does not recognize--for example, a new built-in function or a user-defined function--then the user must create a mapping between this function and a counterpart at the federated database. If a counterpart does not exist, the user must create one that meets the following requirements:
The counterpart can be either a complete function or a function template. A function template is a partial function that has no executable code. It cannot be invoked independently; its only purpose is to participate in a mapping with a data source function, so that the data source function can be invoked from the federated server.
Function mappings are created with the CREATE FUNCTION MAPPING statement. This statement includes parameters, called function mapping options, to which the user can assign values that pertain to the mapping being created or to the data source function within the mapping. Such values, for example, can include estimated statistics on the overhead that would be consumed when the data source function is invoked. The optimizer uses these estimates in developing strategies for invoking the function.
Refer to CREATE FUNCTION (Source or Template) and CREATE FUNCTION MAPPING for details about creating function templates and function mappings. Refer to Function Mapping Options for descriptions of the function mapping options and their values. Refer to the Application Development Guide for guidelines on optimizing the invocation of data source functions.
When a client application submits a distributed request to the federated server, the server parcels out the request to the appropriate data sources. The request does not need to specify these data sources. Instead, it references data source tables and views by identifiers, called nicknames, that map to the tables' and views' names at the data source. The mappings obviate the need to qualify the nicknames by data source names. The locations of the tables and views are transparent to the client application.
Nicknames are not alternate names for tables and views in the same way that aliases are; they are pointers by which the federated server references these objects. Nicknames are defined with the CREATE NICKNAME statement. Refer to CREATE NICKNAME for details.
When a nickname is created for a table or view, the catalog is populated with metadata that the optimizer can use to facilitate access to the table or view. For example, the catalog is supplied with the names of the DB2 data types to which the data types of the table's or view's columns map. If the nickname is for a table with an index, the catalog is supplied also with information related to the index; for example, the name of each column in the index key.
After a nickname is created, the user can supply the catalog with more metadata for the optimizer; for example, metadata that describes values in certain columns of the table or view that the nickname references. The user assigns this metadata to parameters called column options. To illustrate: If a table column contains numeric strings only, the user can indicate this by assigning the value 'Y' to a column option called NUMERIC_STRING. As a result, the optimizer can form strategies to have these strings sorted at the data source, thereby saving the overhead of porting them to the federated server and sorting them there. The savings is especially great when the database that contains the values has a collating sequence that differs from the federated database's collating sequence.
Column options are defined with the ALTER NICKNAME statement. Refer to ALTER NICKNAME for more information about this statement. Refer to Column Options for descriptions of the column options and their settings.
When a nickname is created for a data source table, the federated server supplies the catalog with information about any indexes that a data source table has. The optimizer uses this information to facilitate retrieval of the table's data. If the table has no indexes, the user can nevertheless supply information that an index definition typically contains; for example, which column or columns in the table to search in order to find information quickly. The user would do this by running a CREATE INDEX statement that contains the information and references the table's nickname.
The user can supply the optimizer with similar information for tables that have indexes of which the federated server is unaware. For example, suppose that nickname NICK1 is created for a table that has no index but that acquires one later, or that nickname NICK2 is created for a view over a table that has an index. In these situations, the federated server would be unaware of the indexes. But the user could use CREATE INDEX statements to inform the server that the indexes exist. One statement would reference NICK1 and contain information about the index of the table that NICK1 identifies. The other would reference NICK2 and contain information about the index of the base table that underlies the view that NICK2 identifies.
In cases such as those just described, the information in the CREATE INDEX statement is cataloged as a set of metadata called an index specification. Be aware that when the statement references a nickname, it produces only an index specification, not an actual index. Refer to CREATE INDEX and the Administration Guide, Performance for more information.
A distributed request can use devices such as subqueries and join subselects to specify what table or view columns are be accessed, and what data is to be retrieved.
This section provides examples within the context of the following scenario: A federated server is configured to access a DB2 Universal Database for OS/390 data source, a DB2 Universal Database for AS/400 data source, and an Oracle data source. Stored in each data source is a table that contains employee information. The federated server references these tables by nicknames that refer to where the tables reside: UDB390_EMPLOYEES, AS400_EMPLOYEES, and ORA_EMPLOYEES. In addition to its table of employee information, the Oracle data source has a table that contains information about the countries that the employees live in. The nickname for this second table is ORA_COUNTRIES.
Table AS400_EMPLOYEES contains the phone numbers of employees who live in Asia. It also contains the country codes associated with these phone numbers, but it doesn't list the countries that the codes represent. Table ORA_COUNTRIES, however, does list both codes and countries. The following query uses a subquery to find out the country code for China; and it uses SELECT and WHERE clauses to list those employees in AS400_EMPLOYEES whose phone numbers require this particular code.
SELECT NAME, TELEPHONE
FROM DJADMIN.AS400_EMPLOYEES
WHERE COUNTRY_CODE IN
(SELECT COUNTRY_CODE
FROM DJADMIN.ORA_COUNTRIES
WHERE COUNTRY_NAME = 'CHINA')
When a distributed request such as the one above is compiled, the compiler's query rewrite facility transforms it into a form that can be optimized more easily.
A relational join produces a result set that contains a combination of columns retrieved from two or more tables. Conditions should always be specified to limit the size of the result set's rows.
The query below combines employee names and their corresponding country names by comparing the country codes listed in two tables. Each table resides in a different data source.
SELECT T1.NAME, T2.COUNTRY_NAME
FROM DJADMIN.UDB390_EMPLOYEES T1, DJADMIN.ORA_COUNTRIES T2
WHERE T1.COUNTRY_CODE = T2.COUNTRY_CODE
Compensation is the processing of SQL statements for RDBMSs that do not support those statements. Each type of RDBMS (DB2 Universal Database for AS/400, DB2 Universal Database for OS/390, Oracle, and so on) supports a subset of the international standard of SQL. In addition, some types support SQL constructs that exceed this standard. The totality of SQL that a type of RDBMS supports is called an SQL dialect. If an SQL construct is found in DB2's SQL dialect, but not in a data source's dialect, the federated server can implement this construct on behalf of the data source.
Example 1: DB2's SQL includes the clause, common-table-expression. In this clause, a name can be specified by which all FROM clauses in a fullselect can reference a result set. The federated server will process a common-table-expression for an Oracle database, even though Oracle's SQL dialect does not include common-table-expression.
Example 2: When connecting to a data source that does not support multiple open cursors within an application, the federated server can simulate this function by establishing separate, simultaneous connections to the data source. Similarly, the federated server can simulate CURSOR WITH HOLD capability for a data source that does not provide that function.
Compensation makes it possible to use DB2's SQL dialect to make all queries supported by the federated server. It is not necessary to use dialects specific to RDBMSs other than DB2.
Users can use the pass-through function to communicate with data sources in the data sources' own SQL dialect. In pass-through, users can submit not only queries, but also DML and DDL statements. Refer to SQL Processing in Pass-Through Sessions for information on how DB2 and data sources manage the processing of statements submitted in pass-through sessions.
The federated server provides the following SQL statements to manage pass-through sessions:
There are certain restrictions on using pass-through. For example, in a pass-through session, a cursor cannot be opened directly against a data source object. Refer to Considerations and Restrictions for a complete list of restrictions.