How to query SAP HANA data source by using Federation Server through JDBC driver

Created by Hai Jun Shen on Mon, 12/09/2019 - 03:42
Published URL:
https://www.ibm.com/support/pages/node/1126647
1126647

Question & Answer


Question

How to query SAP HANA data source by using Federation Server through JDBC driver?

Cause

Customers want to use Federation server connect to SAP HANA through JDBC driver.

Answer

To configure a federation to access SAP HANA 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 SAP HANA data source.

Before you begin

Download the HANA JDBC driver(ngdbc.jar), which is installed as part of the  HANA client installation from the SAP official site. you can refer to the tutorial Connect to SAP HANA using JDBC driver.

1. Enable Federation server and restart Db2.

$ db2 update dbm cfg using federated YES
$ db2stop force
$ db2start
2. Test the connection to the SAP HANA data source and verify the service is started correctly.

telnet <SAP_HANA_ip> <port>
If the connection is successful, you receive the following similar output from the command.

$ telnet 9.112.250.11 39015

Trying 9.112.250.11...
Connected to 9.112.250.11.

If the connection fails, you will receive an error, check the HANA service status.

3. 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 jdbc1 type hana wrapper "wrapper1" options (driver_class  'com.sap.db.jdbc.driver' ,driver_package '/home/db2inst1/fed_hana/ngdbc.jar', url  'jdbc:sap://9.112.250.11:39015?reconnect=true' ) 

$ create user mapping for public server jdbc1 options (remote_authid  'test1' ,remote_password 'pass1')

$ create nickname nk1 for jdbc1."SYSTEM".tb1

$ select * from nk1

col_1

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

1970-11-23-12.12.12.000000

1999-12-31-12.12.12.000000
 
 2 record(s) selected.

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PkvAAE","label":"Federation"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
17 March 2022

UID

ibm11126647