How To
Summary
Db2 Federation supports querying IBM watsonx.data data source through JDBC driver. This page shows the configuration and usage.
Objective
NA
Environment
Db2 v11.5.9
IBM watsonx.data 1.0.1
IBM watsonx.data 1.0.1
Steps
1. Enable the Federation server and restart Db2.
$ db2 update dbm cfg using federated YES
$ db2stop force
$ db2start
2. Test the connection to the watsonx.data and confirm that the service is started correctly.
telnet <watsonx.data_ip> <port>
If the connection is successful, you receive the following output.
$ telnet watsonx1.fyre.ibm.com 9443
Trying 9.30.54.122...
Connected to watsonx1.fyre.ibm.com.
Escape character is '^]'.
If the connection fails, you receive an error, check the watsonx.data service status.
3. Prepare the watsonx.data TSL connection JKS truststore file.
Copy the JKS truststore xxx.jks file and put it into a folder that Db2 instance user can access it.
truststore '/home/db2inst1/fed_presto/truststore.jks', truststorepassword 'changeit'
For more information about truststore.jks, refer to Connecting to a Presto server of IBM watsonx.data.
4. Create server, user mapping, nickname, and query the nickname.
db2 => connect to testdb
Database Connection Information
Database server = DB2/LINUXX8664 11.5.9.0
SQL authorization ID = DB2INST1
Local database alias = TESTDB
db2 => create server server1 type jdbc version 0.282 options (driver_package '/home/db2inst1/sqllib/federation/jdbc/lib/presto-jdbc-0.282.jar', driver_class 'com.facebook.presto.jdbc.PrestoDriver', url 'jdbc:presto://watsonx1.fyre.ibm.com:8443', truststore '/home/db2inst1/fed_presto/truststore.jks', truststorepassword 'changeit',db2_same_codeset 'y', collating_sequence 'y', codepage '1208');
DB20000I The SQL command completed successfully.
db2 => create user mapping for public server server1 options (remote_authid 'ibm_user1',remote_password 'password1')
DB20000I The SQL command completed successfully.
db2 => create or replace nickname nk_func_test for server1."iceberg_data"."myschema"."func_test";
SQL1812W Length of column "col_varchar" was reduced from "2147483647" to
"32672 ". SQLSTATE=0169E
db2 => describe table nk_func_test
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
col_binary SYSIBM VARCHAR 32672 0 Yes
dbclb SYSIBM VARCHAR 32672 0 Yes
col_varchar SYSIBM VARCHAR 32672 0 Yes
dcf SYSIBM DOUBLE 8 0 Yes
bgi SYSIBM BIGINT 8 0 Yes
col_float SYSIBM REAL 4 0 Yes
dat SYSIBM DATE 4 0 Yes
tmp SYSIBM TIMESTAMP 10 6 Yes
clb SYSIBM VARCHAR 32672 0 Yes
blb SYSIBM VARCHAR 32672 0 Yes
cha SYSIBM VARCHAR 32672 0 Yes
vch SYSIBM VARCHAR 32672 0 Yes
gph SYSIBM VARCHAR 32672 0 Yes
vgp SYSIBM VARCHAR 32672 0 Yes
dcm SYSIBM DECIMAL 5 2 Yes
itg SYSIBM INTEGER 4 0 Yes
smi SYSIBM INTEGER 4 0 Yes
dob SYSIBM DOUBLE 8 0 Yes
rel SYSIBM REAL 4 0 Yes
cha_number SYSIBM VARCHAR 32672 0 Yes
vch_number SYSIBM VARCHAR 32672 0 Yes
smi_small SYSIBM INTEGER 4 0 Yes
itg_small SYSIBM INTEGER 4 0 Yes
dcm_small SYSIBM DECIMAL 5 2 Yes
rel_small SYSIBM REAL 4 0 Yes
itg_date SYSIBM INTEGER 4 0 Yes
dob_date SYSIBM REAL 4 0 Yes
cha_onechar SYSIBM VARCHAR 32672 0 Yes
vch_onechar SYSIBM VARCHAR 32672 0 Yes
dcm_datetime SYSIBM DECIMAL 5 2 Yes
cha_datetime SYSIBM VARCHAR 32672 0 Yes
vch_datetime SYSIBM VARCHAR 32672 0 Yes
tmpday SYSIBM DATE 4 0 Yes
tme_data_cha SYSIBM VARCHAR 32672 0 Yes
clb_number SYSIBM VARCHAR 32672 0 Yes
vch_trunc SYSIBM VARCHAR 32672 0 Yes
cha_trunc SYSIBM VARCHAR 32672 0 Yes
bgi_small SYSIBM BIGINT 8 0 Yes
gph_number SYSIBM VARCHAR 32672 0 Yes
booln SYSIBM BOOLEAN 1 0 Yes
gph_datetime SYSIBM VARCHAR 32672 0 Yes
tme SYSIBM TIMESTAMP 10 6 Yes
vgp_datetime SYSIBM VARCHAR 32672 0 Yes
vgp_onechar SYSIBM VARCHAR 32672 0 Yes
gph_onechar SYSIBM VARCHAR 32672 0 Yes
col_string SYSIBM VARCHAR 32672 0 Yes
46 record(s) selected.
db2 => select "smi", "itg", "dob", "rel", "dcf", "col_string" from nk_func_test
smi itg dob rel dcf col_string
----------- ----------- ------------------------ ------------------------ ------------------------ ---------------------------------
2 1 +9.90000000000000E-001 +9.90000E-001 +2.10000000000000E+000 This is developped by haijun shen
1 record(s) selected.
db2 => select count(*) from nk_func_test
1
-----------
1
1 record(s) selected.
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSFHEG","label":"DB2 Enterprise Server Edition"},"ARM Category":[{"code":"a8m500000008PkvAAE","label":"Connectivity-\u003EFederation"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.5.0"}]
Was this topic helpful?
Document Information
Modified date:
15 November 2023
UID
ibm17030444