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