Before you add Sybase data sources to a federated server, you need to install and configure the Sybase Open Client software on the DB2 federated server. See the installation procedures in the documentation that comes with Sybase database software for specific details on how to install the Open Client software. As part of the installation, make sure that you include the Sybase catalog stored procedures are installed on the Sybase server and the Sybase Open Client libraries are installed on the DB2 federated server.
After configuring the connection from the client software to the Sybase server, test the connection using one of the Sybase tools. Use the isql tool for UNIX and the SQL Advantage tool for Windows.
To set up your federated server to access data stored on Sybase data sources, you need to:
This chapter discusses steps 2 and 3.
The instructions in this chapter apply to Windows NT, AIX, and the Solaris Operating Environment. The platform-specific differences are noted where they occur.
To add a Sybase 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 Sybase 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:
SYBASE="<sybase home directory>"
where <sybase home directory> is the directory where the Sybase client is installed.
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=$HOME/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=$HOME/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=$HOME/sqllib/cfg/node3.ini
where:
To enable access to Sybase data sources, the DB2 federated server must be link-edited to the client libraries. The link-edit process creates a wrapper for each data source with which the federated server will communicate. When you run the djxlink script you create the wrapper library. To issue the djxlink script type:
djxlink
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:
NET STOP instance_name NET START instance_name
db2stop db2start
To create and set up an interfaces file, you must create the file and make the file accessible.
Windows NT typically names this file sql.ini. Rename the file you just created from sql.ini to interfaces to name the file universally across all platforms. If you choose not to rename sql.ini to interfaces you must use the IFILE parameter or the CONNECTSTRING option that is explained in step 8.
On AIX and Solaris systems this file is named <instance home>/sqllib/interfaces.
ln -s -f /home/sybase/interfaces /home/db2djinst1/sqllib
Use the CREATE WRAPPER statement to specify the wrapper that will be used to access Sybase data sources. Wrappers are mechanisms that federated servers use to communicate with and retrieve data from data sources. DB2 includes two wrappers for Sybase, CTLIB and DBLIB. The following example shows a CREATE WRAPPER statement:
CREATE WRAPPER CTLIB
where CTLIB is the default wrapper name used with Sybase Open Client software. The CTLIB wrapper can be used on Windows NT, AIX, and Solaris servers.
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 SQL Reference for more information about wrapper library names.
To improve performance when the Sybase data source is accessed, set the DB2_DJ_COMM environment variable. 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; for example:
db2set DB2_DJ_COMM='libctlib.a'
db2set DB2_DJ_COMM='libctlib.so'
Ensure that there are no spaces on either side of the equal sign (=).
Refer to the DB2 SQL Reference for more information about wrapper library names. Refer to the Administration Guide for information about the DB2_DJ_COMM environment variable.
Use the CREATE SERVER statement to define each Sybase server whose data sources you want to access; for example:
CREATE SERVER SYBSERVER TYPE SYBASE VERSION 12.0 WRAPPER CTLIB OPTIONS (NODE 'sybnode', DBNAME'sybdb')
where:
Although the name of the node is specified as an option, it is required for Sybase data sources. See the DB2 SQL Reference for information on additional options.
You can use the IGNORE_UDT server option with CTLIB and DBLIB protocols to specify whether the federated server should determine the built-in type that underlies a UDT without strong typing. This server option applies only to data sources accessed through the CTLIB and DBLIB protocols. Valid values are:
When DB2 creates nicknames, it looks for and catalogs information about the objects (tables, views, stored procedures) that the nicknames point to. As it looks for the information, it might find that some objects have data types that it doesn't recognize (that is, data types that don't map to counterparts at the federated database). Such unrecognizable types can include:
When the federated server finds data types that it does not recognize, it returns the error message, SQL3324N. However, it can make an exception to this practice. For data sources accessible through the CTLIB or DBLIB protocols, you can set the IGNORE_UDT server option so that when the federated database encounters an unrecognizable UDT without strong typing, the federated database determines what the UDT's underlying built-in type is. Then, if the federated database recognizes this built-in type, the federated database returns information about the built-in type to the catalog. To have the federated database determine the underlying built-in types of UDTs that do not have strong typing, set IGNORE_UDT to 'Y'.
Specify the timeout thresholds, the path and name of the interfaces file, and the packet size of the interfaces file. Sybase Open Client uses timeout thresholds to interrupt queries and responses that run for too long a period of time. You can set these thresholds in DB2 by using the CONNECTSTRING option of the CREATE SERVER OPTION DDL statement. Use the CONNECTSTRING option to specify:
.-;-------------------------------.
V |
>>---+-----------------------------+-+-------------------------><
+-TIMEOUT-- = --seconds-------+
+-LOGIN_TIMEOUT-- = --seconds-+
+-IFILE-- = --"string"--------+
+-PACKET_SIZE-- = --bytes-----+
'-;---------------------------'
Examples:
On Windows NT servers, to set the timeout value to 60 seconds and the interfaces file to C:\etc\interfaces, use:
CREATE SERVER OPTION connectstring FOR SERVER sybase1 SETTING 'TIMEOUT=60;LOGIN_TIMEOUT=5;IFILE="C:\etc\interfaces"'
On AIX and Solaris servers, set the timeout value to 60 seconds and the interfaces file to/etc/interfaces, use:
CREATE SERVER OPTION connectstring FOR SERVER sybase1 SETTING 'TIMEOUT=60;PACKET_SIZE=4096;IFILE="/etc/interfaces"'
If a user ID or password on the federated server is different from a user ID or password on a Sybase data source, use the CREATE USER MAPPING statement to map the local user ID to the user ID and password defined at the Sybase data source; for example:
CREATE USER MAPPING FOR DB2USER SERVER SYBSERVER OPTIONS ( REMOTE_AUTHID 'sybuser', REMOTE_PASSWORD 'day2night')
where:
See the DB2 SQL Reference for more information on additional options.
Assign a nickname for each view or table located at your Sybase data source. You will use these nicknames when you query the Sybase data source. Sybase nicknames are case sensitive. Enclose both the schema and table names in double quotation marks ("). The following example shows a CREATE NICKNAME statement:
CREATE NICKNAME SYBSALES FOR SYBSERVER."salesdata"."europe"
where:
data_source_name."remote_schema_name"."remote_table_name"
Repeat this step for each table or view to which you want create nicknames. 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.
This step is necessary only when the DB2 federated server and the Sybase
server are running different code pages. Data sources that are using
the same code set as DB2 require no translation. The following table
provides equivalent Sybase options for common National Language Support (NLS)
code pages. Either your Sybase data sources must be configured to
correspond to these equivalents, or the client code must be able to detect the
mismatch and flag it as an error or map the data by using its own
semantics. If no conversion table can be found from the source code
page to the target code page, DB2 issues an error message. Refer to
your Sybase documentation for more information.
Table 2. Sybase Code Page Options
| Code page | Equivalent Sybase option |
|---|---|
| 850 | cp850 |
| 897 | sjis |
| 819 | iso_1 |
| 912 | iso_2 |
| 1089 | iso_6 |
| 813 | iso_7 |
| 916 | iso_8 |
| 920 | iso_9 |