How to query Microsoft SQL Server data source by using Federation Server through JDBC Driver

To configure a federation to access Microsoft 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 wrapper, server, user mapping and nickname about the tables on remote Apache Hive data source.

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. Create wrapper, server, user mapping, nickname, and query the nickname. 

# connect to testdb

# create wrapper "wrapper1" library 'libdb2rcjdbc.so' options(db2_fenced 'y')

# CREATE SERVER SERVER1 type mssql version 2016 wrapper JDBC options (DRIVER_PACKAGE '/home/dbinst1/sqllib/federation/jdbc/lib/FOsqlserver.jar', DRIVER_CLASS  'com.ibm.fluidquery.jdbc.sqlserver.SQLServerDriver' , JDBC_LOG  'Y' , URL  'jdbc:ibm:sqlserver://test.ftest.ibm.com:4433;DatabaseName=mssql2016db1;FetchTWFSasTime=true')

# create user mapping for user server SERVER1 options ( REMOTE_AUTHID 'USER1', REMOTE_PASSWORD 'password1')

# create nickname nk1 for  "SERVER1"."USER1"."tab11";

# select * from nk1

col_1

--------------------------

1

2

  2 record(s) selected.