How to query the data from Amazon Redshift via JDBC connection in Db2 Federation Server?

Created by Jun Hui Liu on Mon, 02/01/2021 - 02:13

Published URL:

https://www.ibm.com/support/pages/node/6410428

6410428

How To

Summary

When user wants to query the data from Amazon Redshift via JDBC in Db2 Federation Server, please follow the steps:
1. Check the bundled JDBC driver;
2. Create JDBC Wrapper, Server, User Mapping, and Nickname;
3. Query the data from Amazon Redshift.

Objective

Query the data from Amazon Redshift via JDBC connection in Db2 Federation Server

Environment

Linux;AIX

Steps

When user wants to query the data from Amazon Redshift, it uses bundled JDBC connection driver in Db2 Federation Server.

1. Check the bundled JDBC driver in Federation.

$ ll /home/db2inst1/sqllib/federation/jdbc/lib/FOredshift.jar
-rwxrwxrwx 1 bin bin 1885888 Apr 10  2020 /home/db2inst1/sqllib/federation/jdbc/lib/FOredshift.jar

2. Registering JDBC wrapper

db2 => create wrapper jdbc
DB20000I  The SQL command completed successfully.

3. Create Server and User mapping for Amazon Redshift

db2 => create server REDSHIFT wrapper jdbc  options(DRIVER_PACKAGE '/home/db2inst1/sqllib/federation/jdbc/lib/FOredshift.jar', driver_class 'com.ibm.fluidquery.jdbc.redshift.RedshiftDriver', url 'jdbc:ibm:redshift://redshift-cluster-xxxx.xxxxxx.us-east-2.redshift.amazonaws.com:5439;DatabaseName=dev', JDBC_LOG 'Y', PUSHDOWN 'Y', db2_maximal_pushdown 'Y', db2_same_codeset 'Y')
DB20000I  The SQL command completed successfully.

db2 => create user mapping for db2inst1 server REDSHIFT options(remote_authid 'awsuser', remote_password 'passwordXXX') 
DB20000I  The SQL command completed successfully.

4. Create nickname for table of Amazon Redshift, and query the data from nickname

-- Please have a look the troubleshooting when hitting the connection issue.
--    https://docs.aws.amazon.com/redshift/latest/mgmt/troubleshooting-connections.html

db2 => create nickname nk_sales for REDSHIFT."public"."sales"
DB20000I  The SQL command completed successfully.
db2 => describe table nk_sales

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
id                              SYSIBM    INTEGER                      4     0 Yes   
name                            SYSIBM    VARCHAR                     20     0 Yes   
dept                            SYSIBM    VARCHAR                     20     0 Yes   
dt                              SYSIBM    DATE                         4     0 Yes   
revenue                         SYSIBM    BIGINT                       8     0 Yes   

  5 record(s) selected.

db2 => select * from nk_sales where "name" = 'federation'

id          name                 dept                 dt         revenue             
----------- -------------------- -------------------- ---------- --------------------
          1 federation           data&ai              01/01/2020            100000000

  1 record(s) selected.

Document Location

Worldwide

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"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":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"}],"Version":"11.5.0"}]