Question & Answer
Question
How to Query Microsoft SQL Server by using Db2 Federation SQL Server native wrapper?
Answer
Before You Begin,
Enable Federation feature and restart Db2 to make it effective.
db2 update dbm cfg using federated YES
Note: Linux, unix Db2 check Step1, Step3, Step4, Windows Db2 check Step2, Step3, Step5.
Step 1: Prepare odbc.ini and db2dj.ini files in ~/sqllib/cfg, just for Db2 Linux, Unix platform.
1.1 Make sure the required ODBC drivers are installed correctly.
ls sqllib/federation/odbc/lib/FOsqls*.so
1.2 Make sure the SQL Server is accessible to Federation server.
$ telnet test1.ibm.com 1433
Trying 9.30.211.12...
Connected to test1.ibm.com.
Escape character is '^]'.
1.3 Edit an odbc.ini file and put it under sqllib/cfg.
$ cat odbc.ini
[ODBC]
InstallDir=/home/db2inst1/sqllib/federation/odbc
Trace=0
TraceFile=/home/db2inst1/odbctrace.out
TraceDll=/home/db2inst1/sqllib/federation/odbc/lib/FOtrc28.so
[mssql2017]
Driver=/home/db2inst1/sqllib/federation/odbc/lib/FOsqls28.so
HostName=test1.fyre.ibm.com
PortNumber=2433
1.4 Edit sqllib/cfg/db2dj.ini file.
$ cat db2dj.ini
DJX_ODBC_LIBRARY_PATH=/home/db2inst1/sqllib/federation/odbc/lib
DB2_FED_LIBPATH=/home/db2inst1/sqllib/federation/odbc/lib
ODBCINI=/home/db2inst1/sqllib/cfg/odbc.ini
1.5 Unset DB2LIBPATH in db2set registry.
db2set DB2LIBPATH=
1.6 Unset DB2LIBPATH, LD_LIBRARY_PATH(on Linux) and LIBPATH(on AIX) from environment variables.
unset DB2LIBPATH
unset LD_LIBRARY_PATH
unset LIBPATH
1.7 Restart Db2 to let above steps take effective.
db2stop
db2start
Step 2: Configuration ODBC DSN, just for Db2 Windows platform.
2.1 Create System DSN in ODBC Data Source Administrator (64-bit) for Microsoft SQL Server data source.
2.2 Test the connection to the SQL Server data source by ODBC Data Source Administrator Tools.
Step 3. Create wrapper, server, user mapping and nicknames.
3.1 create wrapper(optional)
--ALL
create wrapper MSSQLODBC3;
--Linux
create wrapper "ODBC_UNFENCED" LIBRARY 'libdb2mssql3.so' OPTIONS (DB2_FENCED 'N');
create wrapper "ODBC_FENCED" LIBRARY 'libdb2mssql3.so' OPTIONS (DB2_FENCED 'Y');
--AIX
create wrapper "ODBC_UNFENCED" LIBRARY 'libdb2mssql3.a' OPTIONS (DB2_FENCED 'N');
create wrapper "ODBC_FENCED" LIBRARY 'libdb2mssql3.a' OPTIONS (DB2_FENCED 'Y');
--Windows
create wrapper "ODBC_UNFENCED" LIBRARY 'db2mssql3.dll' OPTIONS (DB2_FENCED 'N');
create wrapper "ODBC_FENCED" LIBRARY 'db2mssql3.dll' OPTIONS (DB2_FENCED 'Y');
3.2 create server
--create a server base on an unfenced wrapper created in the above step.
create server mssql_server1 TYPE MSSQLSERVER VERSION 2017 wrapper "ODBC_UNFENCED" OPTIONS (NODE 'SQLSERVER_NODE1', DBNAME 'mssql_db1');
--if the wrapper is not specified in the create server statement, a default fenced wrapper named "MSSQLODBC3" will be created automatically for this server.
create server mssql_server1 TYPE MSSQLSERVER VERSION 2017 OPTIONS (NODE 'SQLSERVER_NODE1', DBNAME 'mssql_db1');
Note:
1) The server type must be "MSSQLSERVER".
2) NODE is a required OPTION in the create server statement. The value "SQLSERVER_NODE1" is the DSN(data source name) we defined in odbc.ini(on Linux, Unix) or ODBC Data Source Administrator(on Windows).
3.3 Create user mapping
create user mapping for user server mssql_server1 options (REMOTE_AUTHID 'USER1', REMOTE_PASSWORD 'PASSWORD1');
create user mapping for DB2INST2 server mssql_server1 options (REMOTE_AUTHID 'USER1', REMOTE_PASSWORD 'PASSWORD1');
3.4 Create nickname for a remote SQL Server table
--create nickname your_nickname for server_name.remote_schema_name.remote_table_name;
--note that the remote schema name and table name are case sensitive.
create nickname nk1 for mssql_server1."dbo"."alltype_test";
3.5 Run a query
select count(*) from nk1
Step4. Example for Db2 Linux Platform,
update dbm cfg using federated YES
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
db2stop force
DB20000I The DB2STOP command completed successfully.
db2start
DB20000I The DB2START command completed successfully.
connect to testdbu
Database Connection Information
Database server = DB2/LINUXX8664 11.5.7.0
SQL authorization ID = HAIJS
Local database alias = TESTDBU
drop wrapper wrapper1
DB20000I The SQL command completed successfully.
create wrapper wrapper1 LIBRARY 'libdb2mssql3.so' OPTIONS (DB2_FENCED 'Y')
DB20000I The SQL command completed successfully.
CREATE SERVER odbc_mssql TYPE MSSQLSERVER VERSION 2017 wrapper wrapper1 OPTIONS (NODE 'mssql2017', DBNAME 'mssql2017db', codepage '1252')
DB20000I The SQL command completed successfully.
create user mapping for user server odbc_mssql options (REMOTE_AUTHID 'J15USER2', REMOTE_PASSWORD 'xxxx')
DB20000I The SQL command completed successfully.
set passthru odbc_mssql
DB20000I The SQL command completed successfully.
drop table alltype_test
DB20000I The SQL command completed successfully.
create table alltype_test(col1_int integer, col2_smallint smallint, col3_double float, col4_char char(30), col5_varchar varchar(30), col6_date date, col7_time time, col8_timestamp datetime2, col9_decimal numeric(10, 5))
DB20000I The SQL command completed successfully.
insert into alltype_test values(11, 2, 3.1, 'mike', 'haijunshen', '2020-09-16','10:44:27', '2020-09-17 10:44:28', 12.39)
DB20000I The SQL command completed successfully.
insert into alltype_test values(21, 22, 23.1, 'rose', 'zhangsan', '2020-09-16','10:44:27', '2020-09-17 10:44:28', 12.39)
DB20000I The SQL command completed successfully.
set passthru reset
DB20000I The SQL command completed successfully.
create nickname nk_odbc_mssql for odbc_mssql."j15user2"."alltype_test"
DB20000I The SQL command completed successfully.
describe table nk_odbc_mssql
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
COL1_INT SYSIBM INTEGER 4 0 Yes
COL2_SMALLINT SYSIBM SMALLINT 2 0 Yes
COL3_DOUBLE SYSIBM DOUBLE 8 0 Yes
COL4_CHAR SYSIBM CHARACTER 30 0 Yes
COL5_VARCHAR SYSIBM VARCHAR 30 0 Yes
COL6_DATE SYSIBM DATE 4 0 Yes
COL7_TIME SYSIBM TIME 3 0 Yes
COL8_TIMESTAMP SYSIBM TIMESTAMP 10 6 Yes
COL9_DECIMAL SYSIBM DECIMAL 10 5 Yes
9 record(s) selected.
select col1_int, col4_char, col5_varchar from nk_odbc_mssql
COL1_INT COL4_CHAR COL5_VARCHAR
----------- ------------------------------ ------------------------------
11 mike haijunshen
21 rose zhangsan
2 record(s) selected.
Step5. Example for Db2 Windows Platform,
C:\Program Files\IBM\SQLLIB_04\BIN>db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 11.5.6.0
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => connect to sample
Database Connection Information
Database server = DB2/NT64 11.5.6.0
SQL authorization ID = ADMINIST...
Local database alias = SAMPLE
db2 => drop wrapper wrapper1
DB20000I The SQL command completed successfully.
db2 => create wrapper wrapper1 LIBRARY 'db2mssql3.dll' OPTIONS (DB2_FENCED 'Y')
DB20000I The SQL command completed successfully.
db2 => CREATE SERVER server1 TYPE MSSQLSERVER VERSION 2017 wrapper wrapper1 OPTIONS (NODE 'mssql2017db', DBNAME 'mssql2017db', codepage '1252');
DB20000I The SQL command completed successfully.
db2 => create user mapping for public server server1 options (REMOTE_AUTHID 'J15USER2', REMOTE_PASSWORD 'xxxx');
DB20000I The SQL command completed successfully.
db2 => set passthru server1
DB20000I The SQL command completed successfully.
db2 => drop table alltype_test
DB20000I The SQL command completed successfully.
db2 => create table alltype_test(col1_int integer, col2_smallint smallint, col3_double float, col4_char char(30), col5_varchar varchar(30), col6_date date, col7_time time, col8_timestamp datetime2, col9_decimal numeric(10, 5));
DB20000I The SQL command completed successfully.
db2 => insert into alltype_test values(11, 2, 3.1, 'mike', 'haijunshen', '2020-09-16','10:44:27', '2020-09-17 10:44:28', 12.39);
DB20000I The SQL command completed successfully.
db2 => insert into alltype_test values(21, 22, 23.1, 'rose', 'zhangsan', '2020-09-16','10:44:27', '2020-09-17 10:44:28', 12.39);
DB20000I The SQL command completed successfully.
db2 => set passthru reset;
DB20000I The SQL command completed successfully.
db2 => create nickname nk_table1 for server1."j15user2"."alltype_test"
DB20000I The SQL command completed successfully.
db2 => describe table nk_table1
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
COL1_INT SYSIBM INTEGER 4 0 Yes
COL2_SMALLINT SYSIBM SMALLINT 2 0 Yes
COL3_DOUBLE SYSIBM DOUBLE 8 0 Yes
COL4_CHAR SYSIBM CHARACTER 30 0 Yes
COL5_VARCHAR SYSIBM VARCHAR 30 0 Yes
COL6_DATE SYSIBM VARCHAR 20 0 Yes
COL7_TIME SYSIBM TIMESTAMP 10 6 Yes
COL8_TIMESTAMP SYSIBM TIMESTAMP 10 6 Yes
COL9_DECIMAL SYSIBM DECIMAL 10 5 Yes
9 record(s) selected.
db2 => select col1_int, col4_char, col5_varchar from nk_table1;
COL1_INT COL4_CHAR COL5_VARCHAR
----------- ------------------------------ ------------------------------
11 mike haijunshen
21 rose zhangsan
2 record(s) selected.
[{"Type":"SW","Line of Business":{"code":"","label":""},"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSCAVPX","label":"Federated Server"},"ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]
Was this topic helpful?
Document Information
Modified date:
09 March 2023
UID
ibm16442817