A DB2 federated system is a special type of distributed database management system (DBMS). A federated system allows you to query and retrieve data located on other DBMSs. A single SQL statement can refer to multiple DBMSs or individual databases. For example, you can join data located in a DB2 Universal Database table, an Oracle table, and a Sybase view.
A DB2 federated system consists of a server with a DB2 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. Supported data sources include:
DB2 Universal Database federated servers communicate with and retrieve data from data sources using protocols, called wrappers. The wrapper that you use depends on the operating system on which the DB2 instance is running. Nicknames are used to identify the tables and views located at the data sources. Applications can connect to the federated database just like any other DB2 database, and query the data sources using nicknames as if they were tables or views in the federated database.
After a federated system is set up, the information in the data sources can be accessed as though the data is in a single local database. Users and applications send queries to the federated database, which retrieves data from the data sources.
A DB2 federated system operates under some restrictions. Distributed requests are limited to read-only operations in DB2 Version 7. 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 DBMSs using the SQL dialect associated with that data source.
To successfully create nicknames for DB2 for UNIX and Windows Version 8 tables and views, you must apply the DB2 for UNIX and Windows Version 7.2 Fixpak 8 to your DB2 for UNIX and Windows Version 7.2 federated database. If you do not apply Fixpak 8 to your DB2 for UNIX and Windows Version 7.2 federated database, an error will occur when you access the nicknames.
The new wrappers in Version 7.2 (such as Informix on AIX, HP, and Solaris Operating Environment; Oracle on Linux, HP, and Solaris Operating Environment; Sybase on AIX and Solaris Operating Environment; and Microsoft SQL Server on AIX and NT) are not available in this FixPak ; you must purchase DB2 Relational Connect Version 7.2.
This section provides instructions for installing DB2 Relational Connect on the server that you will use as your federated system server. Relational Connect is required to access Oracle, Sybase, Microsoft SQL Server, and Informix data sources. DB2 Relational Connect is not required to access members of the DB2 Universal Database family.
Before Installing DB2 Relational Connect:
x:\setup /i language
where:
The installation launchpad opens.
When the installation is complete, DB2 Relational Connect will be installed in the directory along with you other DB2 products. For example, the wrapper library for the Oracle NET8 client software (net8.dll) will be installed in the c:\Program Files\SQLLIB\bin directory.
To install DB2 Relational Connect on your UNIX federated server, use the db2setup utility.
Note: The screens that appear when you use the db2setup utility depend on what you already have installed on the federated server. These steps assume that you do not have Relational Connect installed.
When the installation is complete, DB2 Relational Connect will be installed in the directory along with your other DB2 products.
The nickname parameter in a CREATE NICKNAME statement is a two-part name--the schema and the nickname. If you omit the schema when creating the nickname, the schema of the nickname will be the authid of the user creating the nickname. After a nickname is created, information about the nickname is stored in the catlaog views SYSCAT.TABLES, SYSCAT.TABOPTIONS, SYSCAT.COLUMNS, SYSCAT.COLOPTIONS, and SYSCAT.INDEXES.
When you restore a federated database backup onto a different federated server, the database image does not contain the new database and node directory information it needs to access the DB2 family data sources. You must catalogue this information when you perform the restore.