Before you add Informix data sources to a DB2 federated server, you need to install and configure the Informix Client SDK software on the federated server. See the installation procedures in the documentation that comes with Informix database software for specific details on how to install the Client SDK software. As part of the installation, make sure that you include the Informix Client SDK libraries.
To set up your federated server to access data stored on Informix data sources, you need to:
This chapter discusses step 3.
The instructions in this chapter apply to AIX, Solaris Operating Environment, and HP-UX operating systems. Specific operating system differences are noted where they occur.
To add a Informix data source to a federated server, you need to:
These steps are explained in detail in this section.
Set data source environment variables by modifying the db2dj.ini file and issuing the db2set command. The db2dj.ini file contains configuration information about the Informix client software installed on your federated server. The db2set command updates the DB2 profile registry with your settings.
In a partitioned database system, you can use a single db2dj.ini file for all nodes in a particular instance, or you can use a unique db2dj.ini file for one or more nodes in a particular instance. A nonpartitioned database system can have only one db2dj.ini file per instance.
To set the environment variables:
Set the INFORMIXDIR environment variable to the path for the directory where the Informix Client SDK software is installed; for example:
|INFORMIXDIR=/informix/csdk
This variable identifies the name of the default Informix server.
|INFORMIXSERVER=inf93 |
Note: Although the Informix wrapper does not use the value of this variable, the Informix client requires that this variable be set. The wrapper uses the value of the node server option, which specifies the Informix database server that you want to access.
If you are using the default path for the Informix sqlhosts file ($INFORMIXDIR/etc/sqlhosts), you do not need to set this variable. However, if you are using a path for the Informix sqlhosts file other than the default, then you need to set this variable to the full path name of the Informix sqlhosts file. For example:
|INFORMIXSQLHOSTS=/informix/csdk/etc/my_sqlhosts |
PATH=$INFORMIXDIR/bin:$PATH export PATH INFORMIXDIR=<informix_client_path> export INFORMIXDIR
where informix_client_path is the path on the federated server for the directory where the Informix client is installed. Use double quotes (") around the path if a name in the path contains a blank.
. .profile
If you are using the db2dj.ini file in a nonpartitioned database system, or if you want the db2dj.ini file to apply to the current node only, issue:
|db2set DB2_DJ_INI=<path to sqllib>/sqllib/cfg/db2dj.ini
If you are using the db2dj.ini file in a partitioned database system, and you want the values in the db2dj.ini file to apply to all nodes within this instance, issue:
|db2set -g DB2_DJ_INI=<path to sqllib>/sqllib/cfg/db2dj.ini
If you are using the db2dj.ini file in a partitioned database system, and you want the values in the db2dj.ini file to apply to a specific node, issue:
db2set -i INSTANCEX 3 DB2_DJ_INI=sqllib/cfg/node3.ini
where:
To enable access to Informix data sources, the DB2 federated server must be link-edited to the client libraries. The link-edit process creates a wrapper library for each data source with which the federated server will communicate. When you run the djxlinkInformix script you create the Informix wrapper library. To issue the djxlinkInformix script, type:
djxlinkInformix
|The djxlinkInformix script only creates the Informix wrapper |library. There is another script, the djxlink script that attempts to |create a wrapper library for every data source that DB2 Universal Database |supports (Oracle, Microsoft SQL Server, etc.). If you only have |the client software for some of the data sources installed, you will receive |an error message for each of the missing data sources when you issue the |djxlink script.
|You need UNIX Systems Administrator (root) authorization to run the |djxlinkInformix and djxlink scripts.
|The djxlinkInformix and djxlink scripts write detailed error and warning |messages to a specific file, depending on the operating system. For |example, on AIX, the djxlinkInformix script writes to |/usr/lpp/db2_07_01/lib/djxlinkInformix.out and the djxlink |script writes to /usr/lpp/db2_07_01/lib/djxlink.out.
|The djxlinkInformix and djxlink scripts create the wrapper library in a |specific directory, depending on the operating system. For example, on |AIX, the libinformix.a wrapper library is created in the |/usr/lpp/db2_07_01/lib directory.
|Check the permissions on the libinformix.a wrapper library after it |is created to make sure that it can be read and executed by DB2 instance |owners. If the DB2 instance owners are not in the System group, then |permissions on the libinformix.a wrapper library will need to be |-rwxr-xr-x root system...libinformix.a. |
To ensure that the environment variables are set in the program, recycle the DB2 instance. When you recycle the instance, you refresh the DB2 instance to accept the changes that you made.
Issue the following commands to recycle the DB2 instance:
db2stop db2start
|The file specifies the location of each Informix database server and the |type of connection (protocol) for the database server. There are |several ways to create this file. You can copy it from another system |that has Informix Connect or Informix Client SDK connected to an Informix |server. You can also configure the Informix Client SDK on the DB2 |server to connect to an Informix server, which creates the sqlhosts |file.
|After the sqlhosts file is copied or created, the DB2 instance owner should |use Informix dbaccess (if it is on the DB2 server) to connect to and query the |Informix server. This will establish that the Informix Client SDK is |able to connect to the Informix server before you try to configure DB2 |Relational Connect to work with the Informix Client SDK.
|For more information on setting up this file, refer to the Informix manual Administrators Guide for Informix Dynamic Server.
| Warning: |
|---|
|
If you do not define the Informix database server name in the sqlhosts file, then when you perform an operation that requires connecting to the Informix database server, you will receive an error. |
Use the CREATE WRAPPER statement to specify the wrapper that will be used to access Informix data sources. Wrappers are mechanisms that federated servers use to communicate with and retrieve data from data sources. The following example shows a CREATE WRAPPER statement:
CREATE WRAPPER informix
where informix is the wrapper_name; informix is the default wrapper name used with Informix Client SDK software.
You can substitute the default wrapper name with a name that you choose. However, if you do so, you must also include the LIBRARY parameter and the name of the wrapper library for your federated server in the CREATE WRAPPER statement. See the CREATE WRAPPER statement in the DB2 SOL Reference for more information about wrapper library names.
The wrapper library names for Informix are:
To improve performance when the Informix data source is accessed, set the DB2_DJ_COMM environment variable on the Federated server. This variable determines whether a wrapper is loaded when the Federated server initializes. Set the DB2_DJ_COMM environment variable to include the wrapper library that corresponds to the wrapper that you specified in the previous step. If you are using the Korn shell or Bourne shell command line interfaces, use these export commands:
DB2_DJ_COMM='libinformix.a' export DB2_DJ_COMM
DB2_DJ_COMM='libinformix.so' export DB2_DJ_COMM
DB2_DJ_COMM='libinformix.sl' export DB2_DJ_COMM
Ensure that there are no spaces on either side of the equal sign (=).
If you are using the C shell command line interface, set the environment variables using these commands:
setenv DB2_DJ_COMM 'libinformix.a' (DB2 for AIX servers) setenv DB2_DJ_COMM 'libinformix.so' (DB2 for Solaris Operating Environment servers) setenv DB2_DJ_COMM 'libinformix.sl' (DB2 for HP--UX servers)
Refer to the DB2 SQL Reference for more information about wrapper library names and the DB2_DJ_COMM environment variable.
Use the CREATE SERVER statement to define each Informix server whose data sources you want to access. The syntax for this statement is:
CREATE SERVER server_name TYPE server_type VERSION server_version WRAPPER wrapper_name OPTIONS (NODE 'node_name', DBNAME 'database_name')
where:
The following is an example of the CREATE SERVER statement:
CREATE SERVER asia TYPE informix VERSION 9 WRAPPER informix OPTIONS (NODE 'abc', DBNAME 'sales')
The FOLD_ID and FOLD_PW server options affect whether the wrapper folds the user ID and password to uppercase or lowercase before sending them to Informix. An example of the CREATE SERVER statement with the FOLD_ID and FOLD_PW server options is:
CREATE SERVER asia TYPE informix VERSION 9 WRAPPER informix OPTIONS (NODE 'abc', DBNAME 'sales', FOLD_ID 'U', FOLD_PW 'U')
If a user ID or password on the DB2 Federated server is different from a user ID or password on an Informix data source, use the CREATE USER MAPPING statement to map the local user ID to the user ID and password defined at the Informix data source; for example:
CREATE USER MAPPING FOR local_userid SERVER server_name OPTIONS (REMOTE_AUTHID 'remode_userid', REMOTE_PASSWORD 'remote_password')
where:
The following is an example of the CREATE USER MAPPING statement:
CREATE USER MAPPING FOR robert SERVER asia OPTIONS (REMOTE_AUTHID 'bob', REMOTE_PASSWORD 'day2night')
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 asia OPTIONS (REMOTE_AUTHID 'bob', REMOTE_PASSWORD 'day2night')
See the DB2 SQL Reference for more information on additional options.
Assign a nickname for each table, view, or Informix synonym located at your Informix data source. Nicknames can be 128 characters in length. You will use these nicknames when you query the Informix data source. DB2 will fold the server, schema, and table names to uppercase unless you enclose them in double quotation marks ("). The following example shows a CREATE NICKNAME statement:
CREATE NICKNAME nickname FOR server_name."remote_schema_name"."remote_table_name"
where:
The following is an example of the CREATE NICKNAME statement:
CREATE NICKNAME salesjapan FOR asia."salesdata"."japan"
Repeat this step for each table or view to which you want create a nickname. When you create the nickname, DB2 will use the connection to query the data source catalog. This query tests your connection to the data source. If the connection does not work, you receive an error message.
See the DB2 SQL Reference for more information about the CREATE NICKNAME statement. For more information about nicknames in general and to verify data type mappings, see the DB2 Administration Guide.