IBM Support

How to query Netezza server using Db2 Federation through JDBC driver?

Question & Answer


Question

How to query Netezza server using Db2 Federation through JDBC driver?

Cause

Customer wants to query Netezza server using Db2 Federation through JDBC driver.

Answer

To configure a federation to access Netezza 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 Netezza server.

Before you begin

Download the driver nzjdbc3.jar from Netezza vendor and put in a folder that could be accessed, for example, path $/INSTANCE_HOME/test_netezza

1. Enable Federation server and restart Db2.

# db2 update dbm cfg using federated YES

# db2stop force

# db2start

2. Test the connection to the Netezza and verify the service is started correctly.

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

$ telnet earlobe1.fyre.ibm.com 5480
Trying 9.30.219.171...
Connected to earlobe1.fyre.ibm.com.
Escape character is '^]'.

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

3. Create wrapper, server, user mapping, nickname, and query the nickname. 

# CONNECT TO TESTDB

# CREATE SERVER jdbc_netezza TYPE JDBC OPTIONS (DRIVER_CLASS 'org.netezza.Driver', DRIVER_PACKAGE '/home/haijs/buckets/fedjdbc/data/nzjdbc3.jar', URL  'jdbc:netezza://earlobe1.fyre.ibm.com:5480/testdb')

# CREATE USER MAPPING FOR PUBLIC SERVER jdbc_netezza OPTIONS (REMOTE_AUTHID 'AD',REMOTE_PASSWORD 'test_pass')

# CREATE NICKNAME nk_jdbc_netezza for jdbc_netezza.test_timestamp

# select * from nk1

C1          C2                         C3       C4
----------- -------------------------- -------- ----------
          1 1999-12-31-23.59.59.123457 23:59:59 12/31/1999
          2 1999-12-31-23.59.59.123456 23:59:59 12/31/1999
  2 record(s) selected.

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

UID

ibm16431131

Page Feedback