How To
Summary
When the user wants to query values from Snowflake through JDBC in Db2 Federation Server, follow the steps:
1. Download the Snowflake JDBC driver;
2. Create JDBC Wrapper, Server, User Mapping, and Nickname, and then query values from Snowflake.
Objective
Query values from Snowflake through JDBC connection in Db2 Federation Server
Environment
AIX; Linux; Windows
Steps
When the user wants to query values from Snowflake, we can use JDBC connection in Db2 Federation Server.
1. Download JDBC driver from Snowflake official site with latest version.
For example, download the version snowflake-jdbc-3.12.8.jar
db2 => create wrapper jdbc
DB20000I The SQL command completed successfully.
3. Create Server and User mapping for Snowflake
db2 => create server snowflake_eastern wrapper jdbc options(driver_package '/home/db2inst1/workspace/snowflake/snowflake-jdbc-3.12.8.jar', driver_class 'net.snowflake.client.jdbc.SnowflakeDriver', url 'jdbc:snowflake://jo01569.us-east-2.aws.snowflakecomputing.com?db=testdbu&warehouse=COMPUTE_WH&schema=public')
DB20000I The SQL command completed successfully.
db2 => create user mapping for db2inst1 server snowflake_eastern options(remote_authid 'user1', remote_password 'passwordXXXX.')
DB20000I The SQL command completed successfully.
If the Federation Server is installed in IBM AIX OS, it needs to update the result set format in connection session according to troubleshooting .
db2 => set passthru snowflake_eastern
DB20000I The SQL command completed successfully.
db2 => ALTER SESSION SET JDBC_QUERY_RESULT_FORMAT='JSON'
DB20000I The SQL command completed successfully.
db2 => set passthru reset
DB20000I The SQL command completed successfully.
4. Create nickname for table of Snowflake, and query values from nickname
db2 => create nickname nk_int for snowflake_eastern.public.test_int
DB20000I The SQL command completed successfully.
db2 => describe table nk_int
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
COL SYSIBM DOUBLE 8 0 Yes
1 record(s) selected.
db2 => select * from nk_int
COL
------------------------
+1.23400000000000E+003
+9.09000000000000E+003
2 record(s) selected.
db2 =>
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"},{"code":"PF033","label":"Windows"}],"Version":"11.5.5"}]
Was this topic helpful?
Document Information
Modified date:
28 October 2022
UID
ibm16394566