Details about how to combine SQL Server data in DB2

BKJIA exclusive Article] Steps for joining SQL Server in DB2:

1. Install DB2

2. Install the IBM DataDirect ODBC driver

3. Install the link connector

Or install InfoSphere DB2InfoSphere DB2 is a restricted DB2 version, which uses a link connector to connect to other relational databases, such as SQL Server, Oracle, Sybase, etc.), but before installing InfoSphere DB2, you must install the IBM DataDirect ODBC driver InfoSphere DB2, which is generally considered IBM Webspehere Federation Server ).

Install the IBM DataDirect ODBC driver

For DB2 9.5, the ODBC driver from DataDirect.

You can download the IBM DataDirect ODBC driver from ftp://ftp.software.ibm.com/software/db2ii/downloads/odbc_driver/to another IBM DataDirect ODBC driver of postgresql for DB2 9.5 or later.

After extracting the content of the DataDirect ODBC file, run./odbcsetupbin on your zLinux to install the SQL Server ODBC driver. You must specify the installation location of the DataDirect ODBC driver.

GUI will install the DataDirect ODBC driver at the specified location. However, before installing the DB2 link connector, You need to perform many manual operations to correctly configure the connection to the SQL Server.

Add the following content to the. bashrc file of your DB2 instance User:

 
 

  
  
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 the location where the DJX_ODBC_LIBRARY_PATH driver is installed for you. After modification, log out and log on again to make the modification take effect. You can also execute the following source command to make the modification take effect immediately.

 
 

  
  
$ source .bashrc 
 
 

Create an odbc. ini file in the Home directory of the DB2 instance user and add the following content to the file:

 
 

  
  
[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 
 
 

Before using the sample program provided by DataDirect to connect to SQL Server, you must ensure that the SQL Server shared library is correctly installed. The SQL Server ODBC driver shared library for DB2 9.5 is as follows:

 
 

  
  
DataDirect 4.2  
  
  
VMmsss22.so  
  
  
libVMicu22.so  
  
  
DataDirect 4.3  
  
  
VMmsss23.so  
  
  
libVMicu23.so 
 
 

Enter the Home Directory of the DB2 instance user and enter the ldd/opt/ibm/wⅱ/odbc/lib/VMmsss23.so command. The output information similar to the following is displayed:

 
 

  
  
$ 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 may encounter libVMicu23.so and libodbcinst. so not appearing in the output. If so, you need to create a symbolic connection first, and the ldd command should be able to solve all the dependency problems.

Enter/usr/lib and enter the following command:

 
 

  
  
$ 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 
 
 

SQL Server connection test

Go to the/opt/ibm/WSII/odbc/example directory and run./example.

Specify your $ HOME/odbc. the data source name used in the INI file, the user ID and password used to connect to SQL Server. If the connection is successful, you can start the next step. /example cannot connect to SQL Server. You must fix these problems before installing the link connector in DB2 to connect to SQL Server.

Install InfoSphere DB2 or DB2 link connector

Note: If the previous step fails, connecting to SQL Server from DB2 will never succeed. Never be lucky.

Install IBM InfoSphere DB2 or the DB2 relational connector on an existing DB2 instance on the DB2 instance that you want to associate with other data sources, such as SQL Server or Oracle.

Follow the GUI installer Wizard to complete the installation. On the "Select installation function" screen, select only SQL Server data source support. If you want to use Oracle as an additional data source, you must first install the Oracle client.

NOTE: If your installation fails, check whether g ++ is installed on your zLinux instance. You can use the which g ++ system command to check whether g ++ is installed. If not, install g ++ on zLinux first. If you are using SUSE Linux, you can use YaST to install g ++. on Red Hat, you can use yum to install g ++.

After the GUI is installed, the libdb2mssql3F. so library is automatically created. If the g ++ command is not found in the GUI, the Library Creation will fail.

Go ~ The/sqllib/lib64 directory shows the main library libdb2mssql3. so to connect to SQL Server. The other two shared library files are libdb2mssql3F. so and libdb2mssql3U. so.

Go to the $ HOME/sqllib/cfg directory and check the contents of the db2dj. ini file. If some configuration is improper during GUI installation, you can modify it manually.

 
 

  
  
$ 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 match with you in. the project specified in the bashrc file exactly matches. You may wonder why the same setting appears in two places, because the DB22 process does not read the environment variable setting information when connecting to the SQL Server, instead, we use db2dj. INI file. If you modify this file, you need to restart the database instance.

 
 

  
  
$ db2stop force 
  
  
$ db2start 
 
 

Now you can create the SQL Server package. The simplest and least error-prone method is to use the DB2 Control Center GUI tool. You can save each step of the command to a file, in this way, you can directly call it as needed.

1. First, make sure that the dbm cfg parameter is set to FEDERATED = YES.

 
 

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

2. Start db2cc on the client or server to catalog the DB2 system, instance, and database name.

A) Right-click the "associated database object" and choose "create package" from the context menu ".

B) Select SQL Server as the data source. It will automatically select libdb2mssql3. so library, enter the name of the package, and click "show SQL" to copy the SQL command.

C) When you click "OK", you may be required to specify the path LD_LIBRARY_PATH and SHLIB. the path LD_LIBRARY_PATH is specified in the bashrc file, so you can safely ignore it and click "cancel ".

D) Right-click "server definition" and choose "CREATE" from the shortcut menu ".

E) specify a name for SQL Server and the version of the SQL Server database.

F) Select the "Settings" tab and specify DBNAME and NODE. DBNAME is the name of the SQL Server database, and NODE is the name of the data source, that is, in $ HOME/odbc. the name of the data source created in the INI file. The node name must be the same as odbc. the DSN name defined in the INI file matches. DBNAME must match the SQL Server database name. Click "show SQL" to save the command for future use.

G) Right-click "create alias", select "CREATE" from the right-click menu, and click "show". You should be able to see the SQL Server table for your selected solution, click "show SQL" and save the command for future use.

Now that you have an alias for the SQL Server table, you can use the table in SQL Server in DB2 just like using your own table.

Original article: Federation of SQL Server Data in DB2

By Vikram Khatri