How to Query values from Snowflake via ODBC connection in Federation Server?

Created by Jun Hui Liu on Tue, 12/29/2020 - 01:52

Published URL:

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

6394580

How To

Summary

When the user wants to query values from Snowflake via ODBC in Federation Server, follow the steps:
1. Download the Snowflake ODBC driver;
2. Install and configure ODBC driver in Federation Server;
3. Create ODBC Wrapper, Server, User Mapping, and Nickname, and then query values from Snowflake.

Objective

Query values from Snowflake via ODBC connection in Federation Server

Environment

Linux; Windows

Steps

1. Download ODBC driver from Snowflake official site with latest version.

For example, download the version snowflake_linux_x8664_odbc-2.22.3.tgz for related platform.

2. Install and configure ODBC driver with link here.

For example:

[db2inst1@sflake1 snowflake]$ tar -zxvf snowflake_linux_x8664_odbc-2.22.3.tgz 
snowflake_odbc/
snowflake_odbc/include/
snowflake_odbc/include/sf_odbc.h
snowflake_odbc/ErrorMessages/
snowflake_odbc/ErrorMessages/en-US/
snowflake_odbc/ErrorMessages/en-US/CLIDSIMessages.xml
snowflake_odbc/ErrorMessages/en-US/CSCommonMessages.xml
snowflake_odbc/ErrorMessages/en-US/ClientMessages.xml
snowflake_odbc/ErrorMessages/en-US/JNIDSIMessages.xml
snowflake_odbc/ErrorMessages/en-US/NetworkMessages.xml
snowflake_odbc/ErrorMessages/en-US/ODBCMessages.xml
snowflake_odbc/ErrorMessages/en-US/OLEDBMessages.xml
snowflake_odbc/ErrorMessages/en-US/QSMessages.xml
snowflake_odbc/ErrorMessages/en-US/SQLEngineMessages.xml
snowflake_odbc/ErrorMessages/en-US/ServerMessages.xml
snowflake_odbc/ErrorMessages/en-US/ULMessages.xml
snowflake_odbc/ErrorMessages/en-US/SFMessages.xml
snowflake_odbc/lib/
snowflake_odbc/lib/libSnowflake.so
snowflake_odbc/lib/cacert.pem
snowflake_odbc/conf/
snowflake_odbc/conf/odbc.ini
snowflake_odbc/conf/odbcinst.ini
snowflake_odbc/conf/iodbc.snowflake.ini
snowflake_odbc/conf/unixodbc.snowflake.ini
snowflake_odbc/iodbc_setup.sh
snowflake_odbc/unixodbc_setup.sh
snowflake_odbc/readme.txt

[db2inst1@sflake1 snowflake]$ ls
snowflake_linux_x8664_odbc-2.22.3.tgz  snowflake_odbc

[db2inst1@sflake1 snowflake]$ cd snowflake_odbc/
[db2inst1@sflake1 snowflake_odbc]$ ll -lrt
total 12
drwxr-xr-x 2 db2inst1 db2grp   23 Dec  9 20:02 include
drwxr-xr-x 3 db2inst1 db2grp   19 Dec  9 20:02 ErrorMessages
-rwxr-xr-x 1 db2inst1 db2grp 2196 Dec  9 20:03 unixodbc_setup.sh
-rw-r--r-- 1 db2inst1 db2grp  626 Dec  9 20:03 readme.txt
drwxr-xr-x 2 db2inst1 db2grp   47 Dec  9 20:03 lib
-rwxr-xr-x 1 db2inst1 db2grp 2197 Dec  9 20:03 iodbc_setup.sh
drwxr-xr-x 2 db2inst1 db2grp   99 Dec  9 20:03 conf

[db2inst1@sflake1 snowflake_odbc]$ ./unixodbc_setup.sh
Prerequisites:
- install unixODBC 
    - RedHat:  sudo yum install unixODBC
        - this package contains isql which can connect to Snowflake DB.
- adjust parameters in this file if needed
- make sure you are running this file from its local directory
- run this script as './unixodbc_setup.sh'

unset ODBCINI and ODBCINSTINI
grep: /etc/odbcinst.ini: No such file or directory
Adding driver info to /etc/odbcinst.ini...
./unixodbc_setup.sh: line 39: /etc/odbcinst.ini: Permission denied

[db2inst1@sflake1 snowflake_odbc]$ cd lib/
[db2inst1@sflake1 lib]$ pwd
/home/db2inst1/snowflake/snowflake_odbc/lib

[db2inst1@sflake1 lib]$ vi simba.snowflake.ini
#  NOTE:
#    Update CABundleFile and ODBCInstLib with correct paths in simba.snowflake.ini likes below
#       ODBCInstLib=/home/db2inst1/sqllib/federation/odbc/lib/libodbcinst.so
#       CABundleFile=/home/db2inst1/snowflake/snowflake_odbc/lib/cacert.pem


[db2inst1@sflake1 cfg]$ cd  /home/db2inst1/sqllib/cfg
[db2inst1@sflake1 cfg]$ vi odbc.ini
# NOTE:
#   Add snowflake connection  parameters to odbc.ini likes below.
#   Make sure the value of parameters is correct, such as DRIVER, Server and so on
-------------------------------
[ODBC]
InstallDir=/home/db2inst1/sqllib/federation/odbc

[snowflake]
Description=SnowflakeDB
DRIVER=/home/db2inst1/snowflake/snowflake_odbc/lib/libSnowflake.so
Locale=en-US
SERVER=jo01569.us-east-2.aws.snowflakecomputing.com
PORT=443
SSL=on
database=testdbu
Warehouse=COMPUTE_WH
-------------------------------

[db2inst1@sflake1 cfg]$ vi db2dj.ini
# NOTE:
#   Make sure "ODBCINI"  is set correctly  likes below.
-------------------------------
DJX_ODBC_LIBRARY_PATH=/home/db2inst1/sqllib/federation/odbc/lib:
DB2_FED_LIBPATH=/home/db2inst1/sqllib/federation/odbc/lib:/home/db2inst1/sqllib/federation/netezza/lib64:
ODBCINST=/home/db2inst1/sqllib/cfg/odbcinst.ini
NZ_ODBC_INI_PATH=/home/db2inst1/sqllib/cfg
ODBCINI=/home/db2inst1/sqllib/cfg/odbc.ini
-------------------------------

# Restart Db2 to enable the ODBC connection configuration
#   Please make sure that the Db2 is restarted in proper time
[db2inst1@sflake1 cfg]$ db2stop force
12/29/2020 04:57:18     0   0   SQL1032N  No start database manager command was issued.
dSQL1032N  No start database manager command was issued.  SQLSTATE=57019
[db2inst1@sflake1 cfg]$ db2start
12/29/2020 04:57:26     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst1@sflake1 cfg]$

3. Register ODBC wrapper

db2 => CREATE WRAPPER "ODBC_UNFENCED" LIBRARY 'libdb2rcodbc.so' OPTIONS (DB2_FENCED  'N'         ,MODULE '/home/db2inst1/sqllib/federation/odbc/lib/libodbc.so'   )
DB20000I  The SQL command completed successfully.

4. Create server and user mapping for Snowflake

db2 => create server snowflakeodbc TYPE PDA wrapper ODBC_UNFENCED OPTIONS   ( DB2_REQUESTS_IO_BLOCK_BUF  '512', DB2_SAME_STR_COMP_SEMANTICS  'Y'   ,NODE  'snowflake'   ,PUSHDOWN  'Y'   )
DB20000I  The SQL command completed successfully.

db2 => create user mapping for user server snowflakeodbc options(REMOTE_AUTHID 'user1', REMOTE_PASSWORD 'passwordXXXX')
DB20000I  The SQL command completed successfully.

5. Create nickname for table of Snowflake, and query values from nickname

db2 => create nickname nkint for snowflakeodbc.public.test_int
DB20000I  The SQL command completed successfully.

db2 => describe table nkint

                                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 nkint

COL                     
------------------------
  +1.23400000000000E+003
  +9.09000000000000E+003

  2 record(s) selected.

Note:

It needs to update the code page to have a try when hitting the failures in creating-nickname or querying values step.

db2 => alter server snowflakeodbc options(add codepage '1252')
DB20000I  The SQL command completed successfully.
db2 => drop nickname nkint
DB20000I  The SQL command completed successfully.
db2 => create nickname nkint for snowflakeodbc.public.test_int
DB20000I  The SQL command completed successfully.
db2 => select * from nkint

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":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"11.5.5"}]