Release Notes

|6.3 Chapter 24. Setting Up a Federated System to Access Multiple Data Sources

|6.3.1 Federated Systems

| | |

|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: |

|Oracle
|Sybase
|Microsoft SQL Server
|Informix
|members of the DB2 Universal Database family (such as DB2 for OS/390, DB2 |for AS/4000, and DB2 for Windows) |

|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.

|6.3.2 Restriction

|The new wrappers in Version 7.2 (such as Informix on AIX, HP, and |Solaris; Oracle on Linux, HP, and Solaris; Sybase on AIX and |Solaris; and Microsoft SQL Server on AIX and NT) are not available in |this FixPak ; you must purchase DB2 Relational Connect Version |7.2.

|6.3.3 Installing DB2 Relational Connect

|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: |

|Make sure that you have either DB2 Universal Database Enterprise Edition |or DB2 Universal Database Enterprise -- Extended Edition installed on the |federated server. |

|On DB2 for UNIX servers:
|If you intend to include DB2 family databases in your distributed |requests, you must have selected the Distributed Join for DB2 data |sources option when you installed DB2 Universal Database. To verify |that this option was implemented, check that the FEDERATED |parameter is set to YES. You can check this setting by |issuing the GET DATABASE MANAGER CONFIGURATION command, which |displays all of the parameters and their current settings. |
|Make sure that the client software for the data source is installed on |your federated server. |

|6.3.3.1 Installing DB2 Relational Connect on Windows NT servers

|Log on to the federated server with the user account that you created to |perform the DB2 Universal Database installation.
|Shut down any programs that are running so that the setup program can |update files as required.
|Invoke the setup program. You can either invoke the setup program |automatically or manually. If the setup program fails to start |automatically, or if you want to run the setup in a different language, invoke |the setup program manually. |
- |To automatically invoke the setup program: |
  1. |Insert the DB2 Relational Connect CD into the drive.
  2. |The auto-run feature automatically starts the setup program. The |system language is determined, and the setup program for that language is |launched. |
- |To manually invoke the setup program: |
  1. |Click Start and select the Run option.
  2. |In the Open field, type the following command:
```
|x:\setup /i language 
```
    |where: |
    
    |x
    |Represents your CD-ROM drive.
    |language
    |Represents the country/region code for your language (for example, EN for |English). |
  3. |Click OK. |
  |
|The installation launchpad opens.
|Click Install to begin the installation process.
|Follow the prompts in the setup program.
|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. |

|6.3.3.2 Installing DB2 Relational Connect on UNIX Servers

|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. |

|Log in as a user with root authority.
|Insert and mount your DB2 product CD-ROM. For information on how to |mount a CD-ROM, see DB2 for UNIX Quick |Beginnings.
|Change to the directory where the CD-ROM is mounted by entering the |cd /cdrom command, where cdrom is the |mount point of your product CD-ROM.
|Type the ./db2setup command. After a few moments, |the Install DB2 V7 window opens. This window lists the items that you |currently have installed, and the items that are available for you to |install.
|Navigate to the distributed join you want to install, such as |Distributed Join for Informix Data sources, and press the space bar |to select it. An asterisk appears next to the option when it is |selected.
|Select OK. The Create DB2 Services window opens.
|Since your federated server already contains a DB2 instance, choose the |Do not create a DB2 instance option and select |OK.
|A warning appears if you have elected not to create an Administration |Server. Select OK. The DB2 Setup Utility window |displays a Summary Report of what will be installed. Since you have not |installed Relational Connect before, there should be two items listed: |
- |the product signature for DB2 Relational Connect
- |the distributed join for the data source you selected |
|Choose Continue. A window appears to indicate this is |your final chance to stop the Relational Connect setup. Choose |OK to continue with the setup. It may take a few minutes for |the setup to complete.
|The DB2 Setup Utility window displays a Status Report which indicates |which components installed successfully. Choose OK. |The DB2 Setup Utility window opens. Choose Close and then |OK to exit the utility.
|When the installation is complete, DB2 Relational Connect will be installed |in the directory along with your other DB2 products. |
- |On DB2 for AIX servers, the directory is |/usr/lpp/db2_07_01.
- |On DB2 for Solaris Operating Environment servers, the directory is |/opt/IBMdb2/V7.1.
- |On DB2 for HP-UX servers, the directory is |/opt/IBMdb2/V7.1.
- |On DB2 for Linux servers, the directory is |/usr/IBMdb2/V7.1. |
|

|6.3.4 Chapter 24. Setting Up a Federated System to Access Multiple Data Sources

|6.3.4.1 Understanding the schema used with nicknames

|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.

|6.3.4.2 Issues when restoring a federated database onto a different federated server

|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.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]