A federated database system or federated system is a database management system (DBMS) that supports applications and users submitting SQL statements referencing two or more DBMSs or databases in a single statement. An example is a join between tables in two different DB2 databases. This type of statement is called a distributed request.
A DB2 UDB Version 6 federated system provides support for distributed requests across databases and DBMSs. You can, for example, perform a UNION operation between a DB2 table and an Oracle view. Supported DBMSs include DB2, members of the DB2 Family (such as DB2 for OS/390 and DB2 for AS/400), and Oracle.
A DB2 federated system provides location transparency for database objects. If information (tables and views) is moved, references to that information (called nicknames) can be updated without any changes to applications that request the information. A DB2 federated system also provides compensation for DBMSs that do not support all of the DB2 SQL dialect or certain optimization capabilities. Operations that cannot be performed at a DBMS, such as recursive SQL, are run at DB2.
A DB2 federated system functions in a semi-autonomous manner: DB2 queries containing references to Oracle objects can be submitted while Oracle applications are accessing the same server. A DB2 federated system does not monopolize or restrict access to Oracle or other DBMS objects (beyond integrity/locking constraints).
A DB2 federated system consists of a DB2 UDB Version 6 instance, a database that will serve as the federated database, and one or more data sources. The federated database contains catalog entries identifying data sources and their characteristics. A data source consists of a DBMS and data. Applications connect to the federated database just like any other DB2 database. See Figure 12 for a visual representation of a federated database environment.
Figure 12. A Federated Database System
|
DB2 federated database catalog entries contain information about data source objects: what they are called, information they contain, and conditions under which they can be used. Because this DB2 catalog stores information about objects in many DBMSs, it is called a global catalog. Object attributes are stored in the catalog. The actual DBMSs being referenced, modules used to communicate with the data source, and DBMS data objects (such as tables) that will be accessed are outside the database. (One exception: the federated database can be a data source for the federated system.) You can create federated objects using the Control Center or SQL DDL statements. Required federated database objects are:
Depending on your specific needs, you can create additional objects:
After a federated system is set up, the information in data sources can be accessed as if it was in one big database. Users and applications send queries to one federated database, which then retrieves data from DB2 Family and Oracle systems as needed. User and applications specify nicknames in queries; these nicknames provide references to tables and views located at data sources. From an end-user perspective, nicknames are similar to aliases.
There are many factors affecting federated system performance. The most critical step is to ensure that accurate and up-to-date information about data sources and their objects is stored in the federated database global catalog. This information is used by the DB2 optimizer and can affect decisions to push down operations for evaluation at data sources. See the Administration Guide, Performance for additional information on federated system performance.
A DB2 federated system operates under some restrictions. Distributed requests are limited to read-only operations. In addition, you cannot execute utility operations (LOAD, REORG, REORGCHK, IMPORT, RUNSTATS, and so on) against nicknames.
You can, however, use a pass-through facility to submit DDL and DML statements directly to database managers using the SQL dialect associated with that data source.
Federated systems tolerate parallel environments. Performance gains are delimited by the extent to which a federated database query can be semantically broken down into local object (table, view) references and nickname references. Requests for nickname data are processed sequentially; local objects can be processed in parallel. For example, given the query SELECT * FROM A, B, C, D where A and B are local tables and C and D are nicknames referencing tables at Oracle data sources, one possible plan would join tables A and B with a parallel join. The results are then joined sequentially with nicknames C and D.