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