How to query Greenplum data source using Federation Server through JDBC driver

To configure a federation to access Greenplum data source through JDBC wrapper, you need to provide information about the data sources and objects that you want to access. Then create wrapper, server, user mapping and nickname about the tables on remote Greenplum data source.

Before you begin.

Check the driver FOgreenplum.jar in the path $/INSTANCE_HOME/sqllib/federation/jdbc/lib.

1. Enable Federation server and restart Db2.

# db2 update dbm cfg using federated YES

# db2stop force

# db2start

2. Test the connection to the Greenplum data source and verify the service is started correctly.

telnet <greenplum_ip>  <port>
If the connection is successful, you receive the following similar output from the command.

$ telnet test1.fyre.ibm.com 5432
Trying 9.xxx.xxx.137...
Connected to test1.fyre.ibm.com.
Escape character is '^]'.

If the connection fails, you will receive an error, please check the greenplum service status.

3. Create wrapper, server, user mapping, nickname, and query the nickname. 

connect to testdbu

   Database Connection Information

 Database server        = DB2/LINUXX8664 12.1.0.0
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTDBU

create server server1 type jdbc version 6 OPTIONS(DRIVER_CLASS 'com.ibm.fluidquery.jdbc.greenplum.GreenplumDriver', URL  'jdbc:ibm:greenplum://test1.fyre.ibm.com:5432;DatabaseName=template1', DRIVER_PACKAGE '/home/db2inst1/sqllib/federation/jdbc/lib/FOgreenplum.jar')
DB20000I  The SQL command completed successfully.

CREATE USER MAPPING FOR user SERVER server1 OPTIONS (REMOTE_AUTHID 'gpadmin', REMOTE_PASSWORD 'pivotal')
DB20000I  The SQL command completed successfully.

set passthru server1
DB20000I  The SQL command completed successfully.

drop table alltype_test
DB20000I  The SQL command completed successfully.

create table alltype_test(col1_int integer, col2_smallint smallint, col3_double double precision, col4_char char(30), col5_varchar varchar(30), col6_date date, col7_time time, col8_timestamp timestamp, col9_decimal decimal(10,5), col10_char char(1), col11_char varchar(1), col12_graphic char(30), col13_vargraphic varchar(30), col14_char char(30), col15_varchar varchar(30), col16_date date, col17_timestamp timestamp)
DB20000I  The SQL command completed successfully.

insert into alltype_test values(1, 2, 3.1, 'hello4', 'hello world5', '2022-02-14','2022-09-17 10:44:27', '2022-09-17 10:44:28', 12.39, '1', '2','2022-09-17 10:44:27','2022-09-17 10:44:27','2022-09-17 10:44:27','2022-09-17 10:44:27','1022-02-14','1022-09-17 10:44:27')
DB20000I  The SQL command completed successfully.

set passthru reset
DB20000I  The SQL command completed successfully.

create or replace nickname nk_greenplum for server1."alltype_test"
DB20000I  The SQL command completed successfully.

describe table nk_greenplum

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
col1_int                        SYSIBM    INTEGER                      4     0 Yes
col2_smallint                   SYSIBM    SMALLINT                     2     0 Yes
col3_double                     SYSIBM    DOUBLE                       8     0 Yes
col4_char                       SYSIBM    CHARACTER                   30     0 Yes
col5_varchar                    SYSIBM    VARCHAR                     30     0 Yes
col6_date                       SYSIBM    DATE                         4     0 Yes
col7_time                       SYSIBM    TIME                         3     0 Yes
col8_timestamp                  SYSIBM    TIMESTAMP                   10     6 Yes
col9_decimal                    SYSIBM    DECIMAL                     10     5 Yes
col10_char                      SYSIBM    CHARACTER                    1     0 Yes
col11_char                      SYSIBM    VARCHAR                      1     0 Yes
col12_graphic                   SYSIBM    CHARACTER                   30     0 Yes
col13_vargraphic                SYSIBM    VARCHAR                     30     0 Yes
col14_char                      SYSIBM    CHARACTER                   30     0 Yes
col15_varchar                   SYSIBM    VARCHAR                     30     0 Yes
col16_date                      SYSIBM    DATE                         4     0 Yes
col17_timestamp                 SYSIBM    TIMESTAMP                   10     6 Yes

  17 record(s) selected.


select "col1_int", "col4_char" from nk_greenplum

col1_int    col4_char
----------- ------------------------------
          1 hello4

  1 record(s) selected.