IBM Support

How to query Microsoft SQL Server using Db2 Federation through JDBC driver?

Question & Answer


Question

How to query Microsoft SQL Server using Db2 Federation through JDBC driver?

Cause

Customer wants to query Microsoft SQL Server using Db2 Federation through JDBC driver.

Answer

To configure a federation to access SQL Server data source through JDBC wrapper, you must provide the federation with information about the data sources and objects that you want to access. Then create server, user mapping and nickname for the table in remote SQL Server.
Before you begin
Check the driver FOsqlserver.jar in the path $/INSTANCE_HOME/sqllib/federation/jdbc/lib.
1. Enable Federation server and restart Db2. 
# db2 update dbm cfg using federated YES
# db2stop force
# db2start
2. Test the connection to the SQL Server and verify the service is started correctly.
telnet <SQLServer_ip> <port>
If the connection is successful, you receive the following similar output from the command.
$ telnet test1.fyre.ibm.com 2433
Trying 9.30.244.101...
Connected to test1.fyre.ibm.com.
Escape character is '^]'.

If the connection fails, you will receive an error, please check the SQL Server service status.
3. Create wrapper, server, user mapping, nickname, and query the nickname.
(1) For Db2 on Linux or Unix platform
  
# connect to testdb

# create server server_mssql type jdbc version 2017 options (DRIVER_PACKAGE '/home/haijun/sqllib/federation/jdbc/lib/FOsqlserver.jar',DRIVER_CLASS 'com.ibm.fluidquery.jdbc.sqlserver.SQLServerDriver', URL 'jdbc:ibm:sqlserver://test1.fyre.ibm.com:2433;DatabaseName=mssql2017db;FetchTWFSasTime=true')

# create user mapping for user server server_mssql options ( REMOTE_AUTHID 'user1', REMOTE_PASSWORD 'password1')

# create nickname nk1 for server_mssql.user1.testdate

# select * from nk1

C1          C2         C3       C4
----------- ---------- -------- --------------------------
          1 01/01/2000 00:30:28 2000-01-01-00.00.00.000000
          2 12/31/2010 23:59:59 2010-12-31-23.59.59.003333
          3 -          12:30:29 2010-12-31-23.59.59.003333
          4 12/31/2010 -        2000-01-01-00.00.00.000000
          2 01/01/2000 00:30:28 -

  5 record(s) selected.
(2) For Db2 on windows platform
db2 => connect to sample

   Database Connection Information

 Database server        = DB2/NT64 11.5.6.0
 SQL authorization ID   = ADMINIST...
 Local database alias   = SAMPLE

db2 => create server server1 type jdbc version 2017 options (DRIVER_PACKAGE 'C:\Program Files\IBM\SQLLIB_04\federation\jdbc\lib\FOsqlserver.jar', DRIVER_CLASS 'com.ibm.fluidquery.jdbc.sqlserver.SQLServerDriver', URL 'jdbc:ibm:sqlserver://test1.fyre.ibm.com:2433;DatabaseName=mssql2017db;FetchTWFSasTime=true')

db2 =>
db2 => create user mapping for user server server1 options ( REMOTE_AUTHID 'user1', REMOTE_PASSWORD 'password1')

db2 => set passthru server1

db2 => drop table test1

db2 => create table test1(col1 int, col2 varchar(20))

db2 => insert into test1 values(1, 'haijunshen')

db2 => set passthru reset

db2 => create nickname nk_test1 for "SERVER1"."USER1"."test1"

db2 => select * from nk_test1

col1        col2
----------- --------------------
          1 haijunshen

  1 record(s) selected.

db2 =>

[{"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)"}]

Document Information

Modified date:
09 January 2023

UID

ibm16440435

Page Feedback