Before you add Microsoft SQL Server data sources to a DB2 federated server, you need to install and configure the ODBC driver on the federated server. See the installation procedures in the documentation that comes with the ODBC driver for specific details on how install the ODBC driver.
To set up your federated server to access data stored in Microsoft SQL Server data sources, you need to:
This chapter discusses steps 3 and 4.
The instructions in this chapter apply to Windows NT and AIX platforms. The platform-specific differences are noted where they occur.
After you install the ODBC driver and DB2 Relational Connect, add Microsoft SQL Server data sources to your federated server using these steps:
These steps are explained in detail in the following sections.
Set data source environment variables by modifying the db2dj.ini file and issuing the db2set command. The db2dj.ini file contains configuration information to connect to Microsoft SQL Server data sources. 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:
ODBCINI=$HOME/.odbc.ini DJX_ODBC_LIBRARY_PATH=<path to the Merant driver>/lib DB2ENVLIST=LIBPATH
Issue the db2set command to update the DB2 profile registry with your changes. The syntax of db2set is dependent upon your database system structure:
db2set DB2_DJ_INI=<path to ini file>/db2dj.ini
db2set -g DB2_DJ_INI=<path to ini file>/db2dj.ini
db2set -i INSTANCEX 3 DB2_DJ_INI=$HOME/sqllib/cfg/node3.ini
where:
To set the path to the client library, issue these commands:
db2set DB2LIBPATH=<path to the Merant client library> db2set DB2ENVLIST=LIBPATH
The djxlink.sh shell script links the client libraries to the wrapper libraries. To run the shell script:
djxlink
If you find it takes an inordinate amount of time to access the Microsoft SQL Server data source, 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. Set the DB2_DJ_COMM environment variable to include the wrapper library that corresponds to the wrapper that you specified in Step 5. For example:
db2set DB2_DJ_COMM=djxmssql3.dll
db2set DB2_DJ_COMM=libmssql3.a
Ensure that there are no spaces on either side of the equal sign (=).
See the DB2 SQL Reference for more information about wrapper library names.
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. Recycle the DB2 instance by issuing the following commands:
db2stop db2start
DB2 Universal Database has two different protocols, called wrappers, that
you can use to access Microsoft SQL Server data sources. Wrappers are
the mechanism that federated servers use to communicate with and retrieve data
from data sources. The wrapper that you use depends on the platform on
which DB2 Universal Database is running. Use Table 3 as a guide to selecting the appropriate wrapper.
ODBC driver | Platform | Wrapper Name |
ODBC 3.0 (or higher) driver | Windows NT | DJXMSSQL3 |
MERANT DataDirect Connect ODBC 3.6 driver | AIX | MSSQLODBC3 |
Use the CREATE WRAPPER statement to specify the wrapper that will be used to access Microsoft SQL Server data sources. The following example shows a CREATE WRAPPER statement:
CREATE WRAPPER DJXMSSQL3
where DJXMSSQL3 is the default wrapper name used on a DB2 for Windows NT server (using the ODBC 3.0 driver). If you have a DB2 for AIX server, you would specify the MSSQLODBC3 wrapper name.
You can substitute the default wrapper name with a name that you choose. However, if you do so, you must include the LIBRARY parameter and the name of the wrapper library for your federated server platform in the CREATE WRAPPER statement. For example:
CREATE WRAPPER wrapper_name LIBRARY 'djxmssql3.dll'
where wrapper_name is the name that you want to give the wrapper, and 'djxmssql3.dll' is the library name.
CREATE WRAPPER wrapper_name LIBRARY 'libmssql3.a'
where wrapper_name is the name that you want to give the wrapper, and 'libdjxmssql.a' is the library name.
See the CREATE WRAPPER statement in the DB2 SQL Reference for more information about wrapper library names.
Use the CREATE SERVER statement to define each Microsoft SQL Server data source to which you want to connect. For example:
CREATE SERVER sqlserver TYPE MSSQLSERVER VERSION 7.0 WRAPPER djxmssql3 OPTIONS (NODE 'sqlnode', DBNAME 'database_name')
where:
Although the name of the node (System DSN name) is specified as an option in the CREATE SERVER statement, it is required for Microsoft SQL Server data sources. On Windows, obtain the DSN from the System DSN tab of the Windows ODBC Data Source Administrator tool. On AIX, obtain the DSN from the .odbc.ini file in the DB2 instance owners home directory.
See the DB2 SQL Reference for additional options that you can use with the CREATE WRAPPER statement.
Although the name of the database is specified as an option in the CREATE SERVER statement, it is required for Microsoft SQL Server data sources.
If a user ID or password at the federated server is different from a user ID or password at a Microsoft SQL Server data source, use the CREATE USER MAPPING statement to map the local user ID to the user ID and password defined at the Microsoft SQL Server data source; for example:
CREATE USER MAPPING FOR db2user SERVER server_name OPTIONS (REMOTE_AUTHID 'mssqluser', REMOTE_PASSWORD 'day2night')
where:
See the DB2 SQL Reference for additional options that you can use with the CREATE USER MAPPING statement.
Assign a nickname for each view or table located in your Microsoft SQL Server data source that you want to access. You will use these nicknames when you query the Microsoft SQL Server data source. Use the CREATE NICKNAME statement to assign a nickname. Nicknames are case sensitive. The following example shows a CREATE NICKNAME statement:
CREATE NICKNAME mssqlsales FOR server_name.salesdata.europe
where:
data_source_server_name.remote_schema_name.remote_table_name
Double quotes are recommended for the remote_schema_name and remote_table_name portions of the nickname.
When you create a nickname, DB2 attempts to access the data source catalog tables (Microsoft SQL Server refers to these as system tables). This tests the connection to the data source. If the connection fails, you receive an error message.
Repeat this step for all database tables and views for which you want to create nicknames.
For more information about the CREATE NICKNAME statement, see the DB2 SQL Reference. For more information about nicknames in general, and to verify data type mappings see the DB2 Administration Guide.
If you are experiencing problems when accessing the data source, you can obtain ODBC tracing information to analyze and resolve these problems. To ensure the ODBC tracing works properly, use the trace tool provided by the ODBC Data Source Administrator. Activating tracing impacts your system performance, therefore you should turn off tracing once you have resolved the problems.
Microsoft SQL Server supports many of the common National Language Support
(NLS) code page options that DB2 UDB supports. Data sources that are
using the same code set as DB2 require no translation. Table 3 lists
the code pages that are supported by both DB2 Universal Database and Microsoft
SQL Server.
Table 4. DB2 UDB and Microsoft SQL Server Code Page Options
Code page | Language supported |
---|---|
1252 | ISO character set |
850 | Multilingual |
437 | U.S. English |
874 | Thai |
932 | Japanese |
936 | Chinese (simplified) |
949 | Korean |
950 | Chinese (traditional) |
1250 | Central European |
1251 | Cyrillic |
1253 | Greek |
1254 | Turkish |
1255 | Hebrew |
1256 | Arabic |
When the DB2 federated server and the Microsoft SQL Server are running different National Language Support (NLS) code pages either your Microsoft SQL Server 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 Microsoft SQL Server documentation for more information.