Question & Answer
Question
How to query Teradata database using Db2 Federation through JDBC driver?
Cause
Customer wants to query Teradata database using Db2 Federation through JDBC driver.
Answer
To configure a federation to access Teradata 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 Teradata site , put these two .jar files terajdbc4.jar, tdgssconfig.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 Teradata and verify the service is started correctly.
telnet <teradata_ip> <port>
$ telnet 9.30.104.103 1025
Trying 9.30.104.103...
Connected to 9.30.104.103.
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 Teradata service status.
Trying 9.30.104.103...
Connected to 9.30.104.103.
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 Teradata service status.
3. Create wrapper, server, user mapping, nickname, and query the nickname.
$ connect to testdb
$ CREATE SERVER jdbc_teradata type jdbc version 16 options (DRIVER_PACKAGE '/home/hotellnx113/haijun/fed_teradata/TeraJDBC/terajdbc4.jar:/home/hotellnx113/haijun/fed_teradata/TeraJDBC/tdgssconfig.jar:', driver_class 'com.teradata.jdbc.TeraDriver', url 'jdbc:teradata://9.30.104.103/USER=j15user1,PASSWORD=j15user1,DBS_PORT=1025')
$ CREATE USER MAPPING FOR PUBLIC SERVER jdbc_teradata OPTIONS (REMOTE_AUTHID 'J15USER1',REMOTE_PASSWORD 'J15USER1')
$ CREATE NICKNAME n1 for jdbc_teradata.test_table
$ select * from n1
NAME ID
---------- ------------------------
haijun +1.00000000000000E+001
---------- ------------------------
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)"}]
Was this topic helpful?
Document Information
Modified date:
08 April 2021
UID
ibm16440439