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
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"}]
Was this topic helpful?
Document Information
Modified date:
26 February 2021
UID
ibm16410428