How to Access Data on Greenplum Using Db2 Federation Server

Answer

The Db2 user can use Db2 Federation Server to access data on Greenplum via ODBC driver as following.

Description
You can use federation capability to access data from Greenplum through ODBC driver.

Examples

1) Create server and create user mapping

create server SERVER1 type greenplum authorization greenplumuser password password1 OPTIONS(host 'X.X.X.X',port '5432',dbname 'template1',CODEPAGE '1252', password 'Y',  pushdown 'Y')
DB20000I  The SQL command completed successfully.

create user mapping for user server server1 options(REMOTE_AUTHID 'MSSQLUSER1',REMOTE_PASSWORD 'password1')
DB20000I  The SQL command completed successfully.

2) Set passthru to create table (If the table and data already exists on remote data source please ignore this step)

set passthru SERVER1
DB20000I  The SQL command completed successfully.

create TABLE testodbcfull( col_decimal DECIMAL(30,8), col_varchar VARCHAR(10), col_char CHAR(10), col_int INT, col_smallint SMALLINT, col_bigint BIGINT, col_date DATE, col_real real, col_float FLOAT )
DB20000I  The SQL command completed successfully.

insert into testodbcfull values(1344.78,'vchar','ch',100,100,13213.,'2013-06-07',1545.00,1545.00)
DB20000I  The SQL command completed successfully.

set passthru reset
DB20000I  The SQL command completed successfully.

3) Create nickname and verify data

create nickname nk1 for "SERVER1"."testodbcfull"
DB20000I  The SQL command completed successfully.

insert into nk1 values(1344.78,'vchar','ch',100,100,13213.,'2013-06-07',1545.00,1545.00)
DB20000I  The SQL command completed successfully.

select * from nk1

COL_DECIMAL                      COL_VARCHAR COL_CHAR   COL_INT     COL_SMALLINT COL_BIGINT           COL_DATE   COL_REAL                 COL_FLOAT
-------------------------------- ----------- ---------- ----------- ------------ -------------------- ---------- ------------------------ ------------------------
                   1344.78000000 vchar       ch                 100          100                13213 06/07/2013            +1.54500E+003   +1.54500000000000E+003
                   1344.78000000 vchar       ch                 100          100                13213 06/07/2013            +1.54500E+003   +1.54500000000000E+003

  2 record(s) selected.

describe table nk1

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
COL_DECIMAL                     SYSIBM    DECIMAL                     30     8 Yes
COL_VARCHAR                     SYSIBM    VARCHAR                     10     0 Yes
COL_CHAR                        SYSIBM    CHARACTER                   10     0 Yes
COL_INT                         SYSIBM    INTEGER                      4     0 Yes
COL_SMALLINT                    SYSIBM    SMALLINT                     2     0 Yes
COL_BIGINT                      SYSIBM    BIGINT                       8     0 Yes
COL_DATE                        SYSIBM    DATE                         4     0 Yes
COL_REAL                        SYSIBM    REAL                         4     0 Yes
COL_FLOAT                       SYSIBM    DOUBLE                       8     0 Yes

  9 record(s) selected.

Default forward data type mappings for Greenplum through ODBC

Please refer to link :  https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.data.fluidquery.doc/topics/r0070432.html

Example of data type "TIMESTAMP with time zone"  mapping usage

Because ODBC driver will map TIMESTAMP with time zone to SQL_VARCHAR,  so in federation server it will map to varchar by default, if you want to map it to TIMESTAMP in federation server,  you will need to add attribute "FetchTSWTZasTimestamp=1" to server option "CUSTOMIZED_ODBC_ATTRIBUTES".

Question 1 : How to add an attribute to server option "CUSTOMIZED_ODBC_ATTRIBUTES"?

Answer:  Check server option "CUSTOMIZED_ODBC_ATTRIBUTES" value first, the command is as below:

select SETTING from syscat.serveroptions where OPTION='CUSTOMIZED_ODBC_ATTRIBUTES'

If the query result is empty, use this command to add the attribute:

alter server SERVER1 OPTIONS(add CUSTOMIZED_ODBC_ATTRIBUTES ';FetchTSWTZasTimestamp=1')

If the query result is NOT empty, suppose the orginally existing values is "$ORIGINAL_CUSTOMIZED_ODBC_ATTRIBUTES", use this command:

alter server SERVER1 OPTIONS(set CUSTOMIZED_ODBC_ATTRIBUTES '$ORIGINAL_CUSTOMIZED_ODBC_ATTRIBUTES;FetchTSWTZasTimestamp=1')

Question 2: How to delete an attribute for server option "CUSTOMIZED_ODBC_ATTRIBUTES"?

Answer: Check server option "CUSTOMIZED_ODBC_ATTRIBUTES" value first, if the value is ';DataTypeCompatibility=0', use this command to delete:

alter server SERVER1 OPTIONS(drop CUSTOMIZED_ODBC_ATTRIBUTES ';FetchTSWTZasTimestamp=1')

If the value includes other attributes, such as "$ORIGINAL_CUSTOMIZED_ODBC_ATTRIBUTES", we need to keep other attributes, so please use this command:

alter server SERVER1 OPTIONS(set CUSTOMIZED_ODBC_ATTRIBUTES '$ORIGINAL_CUSTOMIZED_ODBC_ATTRIBUTES')