| This section discusses all the steps required to set up the federated server and database. Subsequent sections discuss the steps to configure access to specific data sources. A federated server and database are simply a DB2 server and database that you set up and configure to access data sources. To set up your federated system, you need to: To set up the server and database, you need to install the required software on the server, create the DB2 instance, and create the database. To configure access to the server and database, you need to provide the server and database with information about the data sources that you want to access. The edition of DB2 server software you install depends on the data sources you want to access. You must consult the installation documentation to determine the specific software you are installing and the disk spaced required. Setting Up the Server There are basic steps to set up the federated server: A. Setting Up the Server to Access Data Source -
Install and configure the client configuration software. -
Install the DB2 server software on the server that will act as the federated server. This includes: -
Install and configure any additional required software on the federated server. This might include DB2 Relational Connect and DB2 Life Sciences Data Connect. B. Verify Federated Server Setup and Data Source Environment Variables -
Verify the server setup: -
Check the data source environment variables. C. Create a DB2 Database on the Federated Server Instance that Will Act as the Federated Database The DB2 instance owner then proceeds with the steps to configure access to specific data sources. Because the setup steps vary from data source to data source, the specific steps are provided in separate topics. Before you start the setup, ensure that your system meets installation, memory, and disk requirements. Additionally, you must have the right authority to perform the installation. -
On UNIX, log on under a user ID that has root authority. -
On Windows, log on with the Administrator account that you have defined for DB2 installation. Setting Up the Server to Access DB2 Family Data Sources To set up the federated server for DB2 family data sources, you need to install the proper DB2 server software on the server that will act as the federated server. The DB2 server software that you install depends on the data sources that you want to access. You install the DB2 server software by using the DB2 Setup Wizard. To install the DB2 server software: -
Log on to the system. -
Close all open programs so that the DB2 installation program can update files as required. -
Insert the DB2 CD and start the setup program to install the DB2 server software. [View full width] [View full width] On UNIX, change the directory where the CD-ROM is mounted and enter the ./db2setup command  to start the setup program. On Windows, the autorun starts the DB2 Setup Wizard. -
The DB2 Setup Launchpad opens. From this window, review the installation prerequisites and release notes for the setup information. -
Proceed through the DB2 Setup Wizard installation panels and make your selections. As part of the installation: -
Click Finish on the last DB2 Setup Wizard installation panel to copy the DB2 files to your system. When you complete the installation, DB2 is installed in the one of the following directories, depending on your operating system: On AIX: /usr/opt/db2_08_01 On HP-UX, Linux, Solaris: /opt/IBM/db2/V8.1 On Windows: \Program Files\IBM\SQLLIB -
To enable the DB2 server to access data sources, set the FEDERATED parameter to YES, by issuing this DB2 command: db2 update dbm cfg using federated YES -
After the DB2 server software is installed, a user with SYSADM authority should check the setup. -
Create the federated database. -
The DB2 instance owner then configures the server to access the DB2 family data sources. Setting Up the Server to Access Informix Data Sources To set up the federated server for DB2 family data sources, you need to install the proper DB2 server software on the server that will perform as the federated server. You install the DB2 server software by using the DB2 Setup Wizard. The steps to set up the federated server for Informix data sources are: -
Log on to the system. -
Close all open programs so that the DB2 installation program can update files as required. -
Install and configure the Informix Client SDK software on the server that will perform as the DB2 federated server. NOTE Refer to the installation procedures in the documentation that comes with the Informix database software for specific details on how to install the client software. -
To ensure that the client software is able to connect to the Informix server, run the Informix demo program to test the connection. -
Insert the DB2 CD and start the setup program to install the DB2 server software. [View full width] [View full width] On UNIX, change the directory where the CD-ROM is mounted and enter the ./db2setup command to start the setup program. On Windows, the autorun starts the DB2 Setup Wizard. -
The DB2 Setup Launchpad opens. From this window, review the installation prerequisites and release notes for the setup information. -
Select the Custom installation option. You must use the Custom installation to set up support for Informix data sources. -
Proceed through the DB2 Setup Wizard installation panels and choose Set up Informix data source support . The setup will require you to identify: NOTE The Custom installation will update the sqllib/cfg/db2dj.ini file to set several data source environment variables: INFORMIXDIR and INFORMIXSERVER. If you need to set the INFORMIXSQLHOSTS environment variable, you will need to set it manually. On UNIX, the installation will also link DB2 to the Informix client software. If you do not install the Informix client software before you run the DB2 Custom installation, you will have to set the environment variables manually and link DB2 to the client software. -
Proceed through the DB2 Setup Wizard installation panels and make your selections. As part of the installation: -
Click Finish on the last DB2 Setup Wizard installation panel to copy the DB2 files to your system. When you complete the installation, DB2 is installed in the one of the following directories, depending on your operating system: On AIX: /usr/opt/db2_08_01 On HP-UX, Linux, Solaris: /opt/IBM/db2/V8.1 On Windows: \Program Files\IBM\SQLLIB -
To enable the DB2 server to access data sources if required, set the FEDERATED parameter to YES, by issuing this DB2 command: db2 update dbm cfg using federated YES -
After the DB2 server software is installed, a user with SYSADM authority should check the setup. -
Create the federated database. -
The DB2 instance owner then configures the server to access the Informix data sources. Checking the Federated Server Setup After the federated server is set up, you can avoid potential problems by checking several key settings: -
Check the data source environment variables (UNIX and Windows for Informix data sources). -
Confirm the link between DB2 and the data source client libraries (UNIX only). -
Check the wrapper library file permissions (UNIX only). -
Ensure that the FEDERATED parameter is set to YES (UNIX and Windows). Checking the Data Source Environment Variables When you set up the federated server, the installation process attempts to set the environment variables for the Informix, Oracle, Sybase, and Microsoft SQL Server data sources. -
For Oracle, Sybase, and Microsoft SQL Server data sources, you need to check the environment variables only if your federated server uses a UNIX operating system. -
For Informix data sources, you need to check the environment variables on both UNIX and Windows operating systems. Check to make certain that the environment variables for the data sources you want access are set in the sqllib/cfg/db2dj.ini file. The data source environment variables will not be set in the sqllib/cfg/db2dj.ini file if you: To set the environment variables: -
Install the client software (if necessary). -
Set the environment variables. You can also manually set the environment variables. The quickest way to set the data source environment variables is: -
For Informix data sources, run the DB2 server Custom installation again. -
For Oracle, Microsoft SQL Server, and Sybase data sources, run the DB2 Relational Connect installation again. Creating the Federated Database After you set up the federated server, the DB2 instance owner creates a DB2 database on the federated server instance that will act as the federated database. You can create the database two ways: The advantage of using the DB2 Control Center is that you do not have to key in each statement and command. It is the easiest way to create a database quickly. A federated server that is properly set up to access your data sources includes the installation and configuration of any required software, such as the client software, DB2 Relational Connect, or DB2 Life Sciences Data Connect. You need SYSADM or SYSCTRL authority to create a DB2 database. Create a DB2 database on the federated server instance that will act as the federated database. For example: db2 create database sample This command: -
Initializes a new database -
Creates the three initial table spaces -
Creates the system tables -
Allocates the recovery log In a multiple database partitions environment, this command affects all database partitions that are listed in the db2nodes.cfg file. The database partition from which this command is issued becomes the catalog partition for the new database. Configuring Access to Data Sources This section shows how to configure a federated server and database to access your data sources: -
It contains information about the basic steps needed to perform the configuration steps quickly. -
It outlines several optional steps, if you need them, to fine-tune the data source configuration. -
To help you avoid problems, the end of this chapter contains configuration troubleshooting advice. The basic steps and recommended interface are: -
Prepare the federated server for the data source. -
Create the wrappers. -
Create the server definitions. -
Create the user mappings. -
Test the connection to the data source server. -
Create the nicknames. However, before you can configure access to a data source, you must make sure that the federated server has been set up properly. It is especially important that you: Prepare the Federated Database Configuring access to a data source involves supplying the federated database with information about the data source. For DB2 family data sources There are two steps that are required to prepare the federated database to access DB2 family data sources: For example, if TCP/IP is your communication protocol, you must issue the CATALOG TCP/IP NODE command: [View full width] [View full width] CATALOG TCPIP NODE DB2NODE REMOTE HOST390 SERVER 1300; where: DB2NODE is a name that you assign to the node that you are cataloging. HOST390 is the host name of the system where the data source resides. 1300 is the service name or primary port number of the server database manager instance. If a service name is used, it is case sensitive. An example of cataloging the remote database using the CATALOG DATABASE command is: [View full width] [View full width] catalog database DB2DB390 AS CLNTS390 AT NODE DB2NODE AUTHENTICATION SERVER; where: DB2DB390 is a name of the database you are cataloging. CLNTS390 is an alias for the database being cataloged. DB2NODE is the name of the node you specified when cataloging the node entry in the node directory. SERVER is the authentication that takes place on the DB2 data source node. Create the Wrapper It is recommended that you use the default wrapper name. A wrapper can: -
Connect to the data source using the standard connection API of that data source -
Submit queries to the data source. For data sources that do not support SQL, one of two actions will occur: -
For data sources that support SQL, the query is submitted in SQL. -
For data sources that do not support SQL, the query is translated into the native query language of the source or into a series of source API calls. -
Receive results sets from the data source using the data source standard API of that data source -
Respond to the federated server queries about the default data type mappings for a data source -
Respond to the federated server queries about the default function mappings for a data source The following example shows a CREATE WRAPPER statement: CREATE WRAPPER DRDA; Supply the Server Definition After you create the wrapper, you need to identify each data source server that you want to access. To create a server definition, use the DB2 Control Center. You can also issue the CREATE SERVER statement in the DB2 Command Center or the CLP. The parameters and options required with the CREATE SERVER statement depend on the data source you want to access. For DB2 family data sources Suppose that you have two DB2 for OS/390 databases: -
The MYDB database is on the HOST390 server and contains a client table and an employee table. -
The MYDB2 database is on the HOST2390 server and contains a sales table. -
You will need to create two server definitions: one for the HOST390 server and one for the HOST2390 server. -
You will then need to create three nicknames, one for each of the tables. An example of the server definition for the HOST390 server is: [View full width] [View full width] CREATE SERVER HOST390 TYPE DB2/390 VERSION 7.1 WRAPPER DRDA AUTHORIZATION 'DSNOW' PASSWORD 'yyyyyyyy' OPTIONS (DBNAME 'MYDB'); where: HOST390 is a name that you assign to the data source server. This name must be unique. DB2/390 is type of data source to which you are configuring access. 7.1 is the version of data source server software that you want to access. DRDA is the wrapper name that you specified in the CREATE WRAPPER statement. 'DSNOW' is the authorization ID at the data source. This value is case sensitive. 'yyyyyyyy' is the password associated with the authorization ID at the data source. This value is case sensitive. 'MYDB' is the name of the database that you want to access. This value is case sensitive. The AUTHORIZATION parameter, the PASSWORD parameter, and the DBNAME option are required. Additional server options When you create the server definition, you can specify additional server options in the CREATE SERVER statement. There are general server options and data source-specific server options. For example, when connecting to a data source, the federated server tries to connect using all possible combinations of upper- and lowercase for the user ID and password. This means that the server might make up to nine connect attempts before successfully connecting to the data source server. These attempts can slow down connect times. You can prevent this by specifying values for the FOLD_ID and FOLD_PW server options. CREATE SERVER HOST390 type DB2/390 version 7.1 WRAPPER DRDA AUTHORIZATION 'DSNOW' PASSWORD 'yyyyyyyy' OPTIONS (NODE 'MYNODE', DBNAME 'MYDB', FOLD_ID 'U', FOLD_PW 'U', COLLATING_SEQUENCE 'Y'); This specifies whether the data source uses the same collating sequence as DB2 for Linux, UNIX, and Windows. Valid values are Y (the same collating sequence is used) and N (a different collating sequence is used). If the COLLATING_SEQUENCE option is not specified, it is assumed that the data source has a different collating sequence than the DB2 collating sequence. A simple test to compare collating sequences You can use an API to display the collating sequence of the federated database and a data source. Below is a simple way to find out the basics about the collating sequence by seeing the results of its use using an ORDER BY operation simply by creating a table with a character column, inserting a few values, then doing SELECT from the column with an ORDER BY clause. Here is the example of DDL to create such a table and insert numbers , uppercase letters, and lowercase letters : [View full width] [View full width] CREATE TABLE MYTAB (COL1 CHAR(1)); INSERT INTO MYTAB VALUES ('1'); INSERT INTO MYTAB VALUES ('2'); INSERT INTO MYTAB VALUES ('jonathan'); INSERT INTO MYTAB VALUES ('JONATHAN'); INSERT INTO MYTAB VALUES ('michael'); INSERT INTO MYTAB VALUES ('MICHAEL'); INSERT INTO MYTAB VALUES ('tiffany'); INSERT INTO MYTAB VALUES ('TIFFANY'); SELECT COL1 FROM MYTAB ORDER BY 1; The result from DB2 UDB ESE on UNIX: COL1 ------- 1 2 jonathan JONATHAN michael MICHAEL tiffany TIFFANY 8 record(s) selected. The result from Oracle server: COL1 ------- 1 2 JONATHAN MICHAEL TIFFANY jonathan michael tiffany 8 record(s) selected. Note: Oracle does not have the same collating sequence as the DB2 federated server. In a similar test for DB2/400, the result is: COL1 ------- jonathan michael tiffany JONATHAN MICHAEL TIFFANY 1 2 8 record(s) selected. Note: The collating sequence on DB2/400 is different from that in the DB2 ESE on UNIX federated database. With DB2 data sources, a useful option to set is PUSHDOWN. If you set PUSHDOWN to Y, the federated server will consider letting the DB2 data source evaluate operations. This is the default setting. If you set PUSHDOWN to N, the federated server will retrieve only columns from the remote data source and will not let the data source evaluate other operations, such as joins. Once the server definition is created, use the ALTER SERVER statement to apply additional server options. ALTER SERVER HOST390 TYPE DB2/390 VERSION 7.1 OPTIONS (ADD PUSHDOWN 'N'); Create the User Mappings When you request access to a DB2 server, access is granted if the authorization IDs are the same between the federated database and the DB2 server. If a user's authorization ID to access the federated database differs from the user's authorization ID to access a data source, you need to define an associationa user mappingbetween the two authorizations IDs so that distributed requests can be sent to the data source. NOTE The REMOTE_AUTHID is the connect authorization ID, not the bind authorization ID. Use the CREATE USER MAPPING statement to map the local user ID to the DB2 server user ID and password.
For example: [View full width] [View full width] CREATE USER MAPPING FOR DB2ADMIN SERVER HOST390 OPTIONS (REMOTE_AUTHID 'db2admin', REMOTE_PASSWORD 'admin'); where: DB2ADMIN is the local user ID that you are mapping to a user ID defined at a DB2 family data source server. HOST390 is the name of the DB2 family data source server that you defined in the CREATE SERVER statement. 'db2admin' is the connect authorization user ID at the DB2 family data source server to which you are mapping DB2USER. This value is case sensitive unless you set the FOLD_ID server option to 'U' or 'L' in the CREATE SERVER statement. 'admin' is the password associated with 'db2admin'. This value is case sensitive unless you set the FOLD_PW server option to 'U' or 'L' in the CREATE SERVER statement. You can use the DB2 special register USER to map the authorization ID of the person issuing the CREATE USER MAPPING statement to the data source authorization ID specified in the REMOTE_AUTHID user option. The following is an example of the CREATE USER MAPPING statement, which includes the USER special register: CREATE USER MAPPING FOR USER SERVER HOST390 OPTIONS (REMOTE_AUTHID 'db2admin', REMOTE_PASSWORD 'admin'); Test the Connection to the Data Source Server Test the connection to the DB2 server to ensure that you can establish a connection, using the server definition and user mappings you defined. Open a pass-through session, and for DB2 for z/OS and OS/390, issue a SELECT statement against the DB2 system tables. For example: SET PASSTHRU HOST390; SELECT count(*) FROM sysibm.systables; SET PASSTHRU RESET; If the SELECT returns a count, your server definition and user mapping are set up properly. If the SELECT returns an error, you may have to: -
Check the remote server to make sure it is started. -
Check the listener on the remote server to make sure that it is configured for incoming connections. -
Check your user mapping to make sure that the settings for the remote_authid and remote_password options are valid for connections to the DB2 server. -
Check the DB2 catalog entries for node and database. -
Check your DB2 federated variables to make sure that they are correct for working with the remote DB2 server. This includes the system environment variables, db2dj.ini variables, and DB2 Profile Registry (db2set) DB2COMM variable. -
Check your server definition and possibly drop it and create it again. -
Check your user mapping and possibly alter it or create another, if necessary. Create the Nicknames for the Tables and Views The federated database relies on catalog statistics for nicknamed objects to optimize query processing. These statistics are gathered when you create a nickname for a data source object using the CREATE NICKNAME statement. The federated database verifies the presence of the object at the data source, then attempts to gather existing data source statistical data. Information useful to the optimizer is read from the data source catalogs and put into the global catalog on the federated server. Because some or all of the data source catalog information might be used by the optimizer, it is advisable to update statistics (using the data source command equivalent to RUNSTATS) at the data source before you create a nickname. Use the CREATE NICKNAME statement to assign a nickname to a view or table located at your DB2 family data source. You will use these nicknames, instead of the names of the data source objects, when you query the DB2 family data source. Nicknames can be up to 128 characters in length. The following example shows a CREATE NICKNAME statement: [View full width] [View full width] CREATE NICKNAME STAFF390 FOR HOST390.DBTTEST.STAFF; where: STAFF390 is a unique nickname used to identify the DB2 table or view. HOST390.DBTTEST.STAFF is a three-part identifier for the remote object. HOST390 is the name you assigned to the DB2 database server in the CREATE SERVER statement. DBTTEST is the name of the remote schema to which the table or view belongs. This value is case sensitive. STAFF is the name of the remote table or view that you want to access. NOTE The nickname 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.
Repeat this step for each DB2 table or view for which you want to create a nickname. When you create the nickname, the federated server will use the connection to query the data source catalog. This query tests your connection to the data source using the nickname. If the connection does not work, you will receive an error message. Improving Performance by Setting the DB2_DJ_COMM Environment Variable (UNIX) If you find that it takes an inordinate amount of time to access the DB2 data source server, you can improve the performance by setting the DB2_DJ_COMM environment variable to load the wrapper when the federated server initializes, rather than when you attempt to access the data source. db2set DB2_DJ_COMM= 'libdb2drda.a'  | 