IBM Support

How to query Apache Derby using Db2 Federation through JDBC driver?

Question & Answer


Question

How to query Apache Derby using Db2 Federation through JDBC driver?

Cause

Customer wants to query Apache Derby using Db2 Federation through JDBC driver.

Answer

To configure a federation to access Apache Derby 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 nicknames for tables in remote Teradata server.
 
Before you begin
 
Download the JDBC driver from Apache Derby site, put this .jar file DERBYCLIENT.jar in your local server,  the path should be accessed by db2 instance user.
 
1. Enable Federation server and restart Db2.
 
$ db2 update dbm cfg using federated YES
$ db2stop force
$ db2start
 
2. Test the connection to the Apache Derby and verify the service is started correctly.
 
telnet <derby_ip> <port>
$ telnet 9.30.141.57 1527
Trying 9.30.141.57...
Connected to 9.30.141.57.
Escape character is '^]'.

If the connection is successful, you receive the following similar output from the command.
If the connection fails, you will receive an error, please check the Derby service status.
 
3. Create wrapper, server, user mapping, nickname, and query the nickname.
 
$ connect to testdb
$ CREATE SERVER jdbc_derby type jdbc version 5 options (DRIVER_PACKAGE '/home/db2inst1/data/DERBYCLIENT.jar', driver_class 'org.apache.derby.jdbc.ClientDriver', url 'jdbc:derby://9.30.141.57:1527/testdbu;create=true');
$ CREATE USER MAPPING FOR PUBLIC SERVER jdbc_derby OPTIONS (REMOTE_AUTHID 'qp',REMOTE_PASSWORD 'passw0rd')
$ CREATE NICKNAME n1 for jdbc_derby.test_table
$ select * from n1
NAME       ID
---------- ------------------------
haijun       +1.00000000000000E+001
  1 record(s) selected.
# db2 update dbm cfg using fed

[{"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:
08 April 2021

UID

ibm16440441

Page Feedback