How To
Summary
When user wants to query the data from Salesforce via JDBC in Db2 Federation Server, please follow the steps:
1. Get security token of the account in Salesforce;
2. Check bundled JDBC driver for Salesforce in Federation server;
3. Create JDBC Wrapper, Server, User Mapping, and Nickname, and then query the data from Salesforce.
Objective
Query the data from Salesforce via JDBC connection in Db2 Federation Server
Environment
Linux;AIX
Steps
When user wants to query the data from Salesforce, it uses bundled JDBC driver in Db2 Federation Server.
1. Get the security token of Salesforce instance.
Reset security token link is here to get a new one if you don't have one.
2. Check bundled JDBC driver in Federation.
[db2inst1@perth1 ~]$ ls -l ~/sqllib/federation/jdbc/lib/FOsforce.jar
-rwxrwxrwx 1 bin bin 19061088 Mar 6 2020 /home/db2inst1/sqllib/federation/jdbc/lib/FOsforce.jar
db2 => create wrapper jdbc
DB20000I The SQL command completed successfully.
4. Create Server and User mapping for Salesforce
-- It needs to set attribute "ConfigOptions={CustomSuffix=strip}" in "url" property to avoid many design issues in latest bundled driver.
db2 => CREATE SERVER SALESFORCE wrapper JDBC options (DRIVER_PACKAGE '/home/db2inst1/sqllib/federation/jdbc/lib/FOsforce.jar', driver_class 'com.ibm.fluidquery.jdbc.sforce.SForceDriver', url 'jdbc:ibm:sforce://login.salesforce.com;SecurityToken=SecurityTokenValuesXXXXXX;CreateMap=forceNew;ConfigOptions={CustomSuffix=strip}', PUSHDOWN 'Y', db2_maximal_pushdown 'Y')
DB20000I The SQL command completed successfully.
db2 => create user mapping for user server SALESFORCE options ( REMOTE_AUTHID 'federation@XXX.com', REMOTE_PASSWORD 'password')
DB20000I The SQL command completed successfully.
5. Create nickname for table of Salesforce, and query values from nickname
db2 => create nickname nk_Articles for salesforce.ARTICLES
DB20000I The SQL command completed successfully.
db2 => describe table nk_Articles
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID SYSIBM VARCHAR 18 0 No
OWNERID SYSIBM VARCHAR 18 0 No
ISDELETED SYSIBM BOOLEAN 2 0 No
NAME SYSIBM VARCHAR 80 0 Yes
CREATEDDATE SYSIBM TIMESTAMP 10 6 No
CREATEDBYID SYSIBM VARCHAR 18 0 No
LASTMODIFIEDDATE SYSIBM TIMESTAMP 10 6 No
LASTMODIFIEDBYID SYSIBM VARCHAR 18 0 No
SYSTEMMODSTAMP SYSIBM TIMESTAMP 10 6 No
LASTACTIVITYDATE SYSIBM DATE 4 0 Yes
C1 SYSIBM INTEGER 4 0 Yes
C2 SYSIBM VARCHAR 20 0 Yes
C3 SYSIBM DECIMAL 12 5 Yes
13 record(s) selected.
db2 => select c1,c2,c3 from nk_Articles where c2 = 'half paper express'
C1 C2 C3
----------- -------------------- --------------
12 half paper express 89.90000
Troubleshooting of nickname creating failure issues:
a> The metadata of remote objects is not updated locally which results in creating nickname failure
The solution is to refresh map in "set passthru" mode.
-- The OUTSIDE object is created by other application for Salesforce,
-- and the metadata is not updated in Federation currently.
db2 => CREATE NICKNAME NK_OUTSIDE FOR SALESFORCE.OUTSIDE
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "OUTSIDE" is an undefined name.
db2 => set passthru salesforce
DB20000I The SQL command completed successfully.
-- Refresh metadata of objects from Salesforce to Federation Server locally
db2 => refresh map
DB20000I The SQL command completed successfully.
db2 => set passthru reset
DB20000I The SQL command completed successfully.
-- recreate nickname for object OUTSIDE
db2 => CREATE NICKNAME NK_OUTSIDE FOR SALESFORCE.OUTSIDE
DB20000I The SQL command completed successfully.
db2 => describe table NK_OUTSIDE
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID SYSIBM VARCHAR 18 0 No
OWNERID SYSIBM VARCHAR 18 0 No
ISDELETED SYSIBM BOOLEAN 2 0 No
NAME SYSIBM VARCHAR 80 0 Yes
CREATEDDATE SYSIBM TIMESTAMP 10 6 No
CREATEDBYID SYSIBM VARCHAR 18 0 No
LASTMODIFIEDDATE SYSIBM TIMESTAMP 10 6 No
LASTMODIFIEDBYID SYSIBM VARCHAR 18 0 No
SYSTEMMODSTAMP SYSIBM TIMESTAMP 10 6 No
LASTACTIVITYDATE SYSIBM DATE 4 0 Yes
C1 SYSIBM DECIMAL 17 5 Yes
11 record(s) selected.
db2 =>
b> The latest bundled driver makes customized object and its customized column names with suffix "__C" by default which results in nickname creation failure.
The solution is to set attribute "ConfigOptions={CustomSuffix=strip}" in "url" property.
db2 => CREATE SERVER SALESFORCE_C wrapper JDBC options (DRIVER_PACKAGE '/home/db2inst1/sqllib/federation/jdbc/lib/FOsforce.jar', driver_class 'com.ibm.fluidquery.jdbc.sforce.SForceDriver', url 'jdbc:ibm:sforce://login.salesforce.com;SecurityToken=SecurityTokenXXXXXXXXX;CreateMap=forceNew;', JDBC_LOG 'Y', PUSHDOWN 'Y', db2_maximal_pushdown 'Y')
DB20000I The SQL command completed successfully
db2 => create user mapping for user server SALESFORCE_C options ( REMOTE_AUTHID 'federation@XXX.com', REMOTE_PASSWORD 'passwdXXX')
DB20000I The SQL command completed successfully.
db2 => CREATE NICKNAME NK_ARTICLES_C FOR SALESFORCE_C.ARTICLES
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "ARTICLES" is an undefined name.
-- update the object name with suffix "__C"
db2 => create nickname NK_ARTICLES_C FOR SALESFORCE_C.ARTICLES__C
DB20000I The SQL command completed successfully.
-- All the customized column names are appended with "__C" by default
db2 => describe table NK_ARTICLES_C
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID SYSIBM VARCHAR 18 0 No
OWNERID SYSIBM VARCHAR 18 0 No
ISDELETED SYSIBM BOOLEAN 2 0 No
NAME SYSIBM VARCHAR 80 0 Yes
CREATEDDATE SYSIBM TIMESTAMP 10 6 No
CREATEDBYID SYSIBM VARCHAR 18 0 No
LASTMODIFIEDDATE SYSIBM TIMESTAMP 10 6 No
LASTMODIFIEDBYID SYSIBM VARCHAR 18 0 No
SYSTEMMODSTAMP SYSIBM TIMESTAMP 10 6 No
LASTACTIVITYDATE SYSIBM DATE 4 0 Yes
C1__C SYSIBM INTEGER 4 0 Yes
C2__C SYSIBM VARCHAR 20 0 Yes
C3__C SYSIBM DECIMAL 12 5 Yes
13 record(s) selected.
db2 => drop server SALESFORCE_C
DB20000I The SQL command completed successfully.
-- add attribute "ConfigOptions={CustomSuffix=strip}" in url property to avoid "__C" appending issue.
db2 => CREATE SERVER SALESFORCE_C wrapper JDBC options (DRIVER_PACKAGE '/home/db2inst1/sqllib/federation/jdbc/lib/FOsforce.jar', driver_class 'com.ibm.fluidquery.jdbc.sforce.SForceDriver', url 'jdbc:ibm:sforce://login.salesforce.com;SecurityToken=SecurityTokenXXXXXX;CreateMap=forceNew;ConfigOptions={CustomSuffix=strip}', JDBC_LOG 'Y', PUSHDOWN 'Y', db2_maximal_pushdown 'Y')
DB20000I The SQL command completed successfully.
db2 => create user mapping for user server SALESFORCE_C options ( REMOTE_AUTHID 'federation@XXX.com', REMOTE_PASSWORD 'passwdXXX')
DB20000I The SQL command completed successfully.
db2 => CREATE NICKNAME NK_ARTICLES_C FOR SALESFORCE_C.ARTICLES
DB20000I The SQL command completed successfully.
db2 => describe table NK_ARTICLES_C
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID SYSIBM VARCHAR 18 0 No
OWNERID SYSIBM VARCHAR 18 0 No
ISDELETED SYSIBM BOOLEAN 2 0 No
NAME SYSIBM VARCHAR 80 0 Yes
CREATEDDATE SYSIBM TIMESTAMP 10 6 No
CREATEDBYID SYSIBM VARCHAR 18 0 No
LASTMODIFIEDDATE SYSIBM TIMESTAMP 10 6 No
LASTMODIFIEDBYID SYSIBM VARCHAR 18 0 No
SYSTEMMODSTAMP SYSIBM TIMESTAMP 10 6 No
LASTACTIVITYDATE SYSIBM DATE 4 0 Yes
C1 SYSIBM INTEGER 4 0 Yes
C2 SYSIBM VARCHAR 20 0 Yes
C3 SYSIBM DECIMAL 12 5 Yes
13 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
ibm16411191