?

Log in

No account? Create an account
Previous Entry Share Next Entry
Db2 Federation: Microsoft SQL Server (MSSQL)
zinal
A. Necessary MSSQL configuration:
1. Set up MSSQL networking
Use the documentation from Microsoft.
Record the configured TCP port (or obtain the necessary setting from MSSQL administrator).

2. Obtain or create username and password
Ideally you will need the account able to read both system and user tables tables.
Write access is not required, although it also does not create any problems (e.g. [sa] user works, although its privileges are far too high).

3. Obtain the proper database name
You should know which database contains the data you need.

B. Db2 server configuration under Linux:
1. Configure the ODBC entry for the MSSQL data source.
Use the DataDirect ODBC driver bundled with Db2 (FOsqls27.so).
Ensure that you know the proper host name (or IP address), port number and database name.
Ensure that the Db2 server has network access to this host and port (check with [telnet hostname portnum]).

[mssql1]
Driver=/opt/ibm/db2/V11.1/federation/odbc/lib/FOsqls27.so
Description=Microsoft SQL Server Driver
Database=pushkinn
Address=venda,1433


2. Configure the environment variables in the sqllib/conf/db2dj.ini file
Provide the path to the odbc.ini file.
Also provide the path to the ODBC driver manager library (libodbc.so), in this case - the DataDirect ODBC driver manager bundled with Db2.

ODBCINI=/home/db2inst1/odbc.ini
DJX_ODBC_LIBRARY_PATH=/opt/ibm/db2/V11.1/federation/odbc/lib


I have also duplicated this environment variables in the .bash_profile, although I really think that this is not required.

3. Symlink the ICU libraries from DataDirect drivers into the system library directory
For CentOS/RedHat 7.x this looks like the following:

ln -s /opt/ibm/db2/V11.1/federation/odbc/lib/libFOicu27.so /usr/lib64/
ln -s /opt/ibm/db2/V11.1/federation/odbc/lib/libFOicu28.so /usr/lib64/

Please note that this step is really required (actually, described in the official documentation).
For some reason putting the odbc/lib directory into LD_LIBRARY_PATH does not help here.

C. Db2 server configuration under Windows:
1. Configure the ODBC entry for the MSSQL data source.
Use Microsoft's ODBC Driver Manager configurator (odbcad32.exe).
Use the administrator privileges (aka Run As Administrator) and the 64-bit version of this tool (do not worry about "32" in the file name, it's there for historical reasons).
Configure the "System" type data source with the necessary server, port and database name.
The tool sometimes complains about insufficient privileges, but still does its job.
Driver type is normally "SQL Server Native Client 11.0".


D. Connecting to the MSSQL data source:
1. Enable federation support in the Db2 server

db2 update dbm cfg using federated yes
db2stop force
db2start


2. Create the wrapper and server definition

db2 connect to DBNAME
...
db2 -t
db2 => connect to DBNAME;
  Database Connection Information
  Database server        = DB2/LINUXX8664 11.1.2.2
  SQL authorization ID   = DB2INST1
  Local database alias   = DBNAME
db2 => CREATE WRAPPER MSSQLODBC3;
DB20000I  The SQL command completed successfully.
db2 => CREATE SERVER push TYPE MSSQLSERVER VERSION 2012 WRAPPER MSSQLODBC3
db2 (cont.) =>  OPTIONS (NODE 'mssql1', DBNAME 'pushkinn', 
db2 (cont.) =>  PUSHDOWN 'Y', DB2_MAXIMAL_PUSHDOWN 'Y');
DB20000I  The SQL command completed successfully.


3. Create the user mapping

db2 => create user mapping for db2inst1 server push
db2 (cont.) => options (REMOTE_AUTHID 'sa', REMOTE_PASSWORD 'P@$$w0rd');
DB20000I  The SQL command completed successfully.


3. Create nicknames for the remote tables

db2 => create nickname push.demo1 for push."pushkinn"."demo1";
DB20000I  The SQL command completed successfully.


4. Collect statistics for better query optimization

db2 => call sysproc.nnstat('PUSH', NULL, NULL, NULL, NULL, 1, 'nnstat.log', ?);
DB20000I  The SQL command completed successfully.


Tags: ,