Steps to configure SQL Server federation from zLinux. (Or any kind of *nix)

These steps are for DB2 9.5 but they should work as it is for DB2 9.7 or later with proper install of IBM branded ODBC drivers.

1. Install DB2
2. Install IBM Branded DataDirect ODBC Drivers.
3. Install Relational connectors

or Install InfoSphere DB2 (The InfoSphere DB2 is a limited edition with relational connectors to connect to other relational databases such as Oracle, SQL Server, Sybase etc.). But, you have to install IBM branded DataDirect ODBC drivers before installing InfoSphere DB2. (InfoSphere DB2 is used to be known as IBM Webspehere Federation Server.)

Install IBM branded DataDirect ODBC Drivers

For DB2 9.5, download IBM branded DataDirect ODBC drivers for zLinux from this link:

You can download IBM branded DataDirect ODBC drivers for other *nix for DB2 9.5 and higher from this link.

After unzipping contents of the DataDirect ODBC files, run ./odbcsetupbin to setup SQL Server ODBC drivers in your zLinux. Specify the directory where you are installing DataDirect ODBC drivers.

The GUI will install ODBC drivers at the location you specified. But, there are some manual steps that one must complete for a successful connection to SQL Server before installing DB2 relational connectors.

In your .bashrc of DB2 instance user, add following entries.

export ODBCINI=$HOME/odbc.ini
export DJX_ODBC_LIBRARY_PATH=/opt/ibm/WSII/odbc/lib
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DJX_ODBC_LIBRARY_PATH
export LANG=en_US

Adjust DJX_ODBC_LIBRARY_PATH to the path where you installed your ODBC drivers. Logout and login again for new entries to take effect or source your profile again as shown below.


$ source .bashrc

Create odbc.ini in the home directory of your DB2 instance user and add entries as per information for your SQL Server database.

[ODBC Data Sources]
mssqlserver=Microsoft SQL Server

[ODBC]
Trace=0
TraceDll=/opt/ibm/WSII/odbc/lib/odbctrc.so
InstallDir=/usr/opt/ibm/WSII/odbc
quotedID=yes

[sqlserverdb]
Driver=/opt/ibm/WSII/odbc/lib/VMmsss23.so
Description=Microsoft SQL Server Driver for S390
Address=xxx.xxx.xxx.xxx,portnumber
Database=DBNAME
QuotedId=No

We need to make sure that the shared library for SQL Servers are setup properly before we attempt to connect to SQL Server using DataDirect provided sample programs.

The SQL Server ODBC drivers shared library for DB2 9.5 are as follows:

DataDirect Version 4.2:

VMmsss22.so
libVMicu22.so

DataDirect Version 4.3:

VMmsss23.so
libVMicu23.so

Go to the home directory of the DB2 Instance user and type command ldd /opt/ibm/WSII/odbc/lib/VMmsss23.so. You might see the output similar to the one shown below:

$ ldd VMmsss23.so
libpthread.so.0 => /lib64/libpthread.so.0 
librt.so.1 => /lib64/librt.so.1 
libVMicu23.so => /opt/ibm/WSII/odbc/lib/libVMicu23.so
libodbcinst.so => /opt/ibm/WSII/odbc/lib/libodbcinst.so
libnsl.so.1 => /lib64/libnsl.so.1
libdl.so.2 => /lib64/libdl.so.2
libc.so.6 => /lib64/libc.so.6
libstdc++.so.5 => /usr/lib64/libstdc++.so.5
libm.so.6 => /lib64/libm.so.6
libgcc_s.so.1 => /lib64/libgcc_s.so.1
/lib/ld64.so.1 (0x000002aaaa000)

You might notice that libVMicu23.so and libodbcinst.so are not resolved in your output of the ldd command. Fix these errors by creating proper symbolic links before you go to the next step. The ldd command should be able to resolve all dependencies before you attempt to go to the next step.

Change directory to /usr/lib and type these commands:

$ cd /usr/lib
$ ln -s /opt/ibm/WSII/odbc/lib/libodbcinst.so libodbcinst.so
$ ln -s /opt/ibm/WSII/odbc/lib/libVMicu23.so libVMicu23.so

Test connection to your SQL Server:

Change directory to /opt/ibm/WSII/odbc/example and run ./example program.

Specify the data source name that you specified in your $HOME/odbc.ini file . Specify user id and password to connect to SQL Server. If connection succeeds, you are good to go to the next step. If ./example is not able to make a connection to the SQL server, fix this problem first before you attempt to install relational connectors in DB2 to access SQL Server.

Install InfoSphere DB2 or DB2 and Relational Connectors

Please note: You should do this step only when you are able to test connection to your SQL Server using IBM branded DataDirect ODBC drivers. If previous step fails, you will have no luck connecting to SQL Server from DB2 federation.

Install DB2 either using IBM InfoSphere DB2 or install DB2 relational connectors in your existing DB2 instance where you like to do federation from other data sources like SQL Server, Oracle etc.

Follow the GUI install for InfoSphere DB2 or relational connectors on an existing DB2 instance and complete the installation. On the Select features to Install screen, select SQL Server data source support only. If you want to select Oracle as an additional data source, you will have to install Oracle client first before it can install support for Oracle. This is important.

Note: If your installation fails, check if you have g++ installed on your zLinux. You can check which g++ to see if g++ is available or not. If not, install g++ first on your zLinux. If you are using SUSE Linux, use YAST to install g++. On Redhat, you can use yum to install g++.

After GUI install in over, complete these steps to make sure that we are using right choices for the SQL Server. The GUI install also builds library libdb2mssql3F.so and this might fail if GUI can not find g++ command.

Go to your ~/sqllib/lib64 directory and you should see main library for SQL Server libdb2mssql3.so. The other 2 additional shared library files are libdb2mssql3F.so and libdb2mssql3U.so.

Go to the $HOME/sqllib/cfg directory. Check contents of the db2dj.ini file and you can make changes in this file if you were not sure of specified information during the GUI install.

$ cat db2dj.ini
DJX_ODBC_LIBRARY_PATH=/opt/ibm/WSII/odbc/lib
ODBCINI=/home/db2inst1/odbc.ini

Make sure that DJX_ODBC_LIBRARY_PATH and ODBCINI matches with the entries that you specified in your .bashrc file. You might wonder why there is a need to have entries at 2 different places. Please remember that DB22 process to connect to SQL Server will not read the environment variables set through your login profile file and db2dj.ini is the special file that will provide this information to the relational wrapper library.

If you make changes to the db2dj.ini file, please stop and start the instance.

$ db2stop force
$ db2start

Now, you are ready to create SQL Server wrapper etc. The easiest way to create these without making mistakes is to use the DB2 Control Center GUI tool. You can save commands in a file for each step so that you can use the script in future when you need to configure it again on other servers. This is the best approach to do it right.

1. Make sure that you have DBM CFG param FEDERATED=YES.

$ db2 get dbm cfg | grep -i federate
 Federated Database System Support           (FEDERATED) = YES

2. Launch db2cc either on your server or from your client machine. Catalog DB2 system, instance and database.

A. Right click on Federated Database Objects and click on Create Wrapper.
B. Select Data Source as SQL Server and it should automatically select library libdb2mssql3.so. Type name of the wrapper as per your choice and click on Show SQL and copy the command.
C. When you click on OK, it might ask you to specify LD_LIBRARY_PATH and SHLIB path. We have already specified our LD_LIBRARY_PATH in our .bashrc file so you can ignore this safely. Click Cancel.

D. Right click on Server Definition and click Create.

E. Specify any Name for your SQL Server and the Version of your SQL Server database.

F. Go to the Settings tab and specify DBNAME and NODE. The DBNAME is the name of your SQL Server database and NODE is the name of the data source entry that you created in your $HOME/odbc.ini file. The node name must match with the DSN in your odbc.ini file and DBNAME must match with the SQL Server database name. This is important. Click on Show SQL and save the command for the future use.

G. Right click on User Mappings and click Create. You will map your SQL Server user id and password here. Select your DB2 user id and click on the Settings tab. Specify remote user id and password for your SQL Server database. Click on Show SQL and save the command for the future use.

H. Right click on Create Nicknames and click on Create. Click on Discover. You should be able to see SQL Server tables for the schema that you selected. Click on Show SQL and save the commands for the future use.

You have now nick names for the SQL Server tables that you selected.