ChannelDB2

Configure Federation Server ODBC Wrapper to Netezza

System and Software
AIX 5.3.0.0
InfoSphere Federation Server v9.7 fixpack 3
ODBC driver v4.5.4 provided by Netezza.
IBM branded Datadirect ODBC driver manager 6.0

Setup Steps

Configure connectivity from ODBC Driver to Netezza Server
1. Copy Netteza driver to IBM branded Datadirect ODBC driver manager library directory
2. Configure DSN(Netteza server information) in odbc.ini
3. Use example program in IBM branded Datadirect ODBC driver manager to test the connectivity from ODBC to Netezza Server

Configure Federation
1. Add the following variable to db2dj.ini
NZ_ODBC_INI_PATH =<directory which contains the odbc.ini file >
2. create Federated objects

CREATE WRAPPER ODBC LIBRARY 'libdb2rcodbc.a' OPTIONS (MODULE '/opt/oemclient/odbc60/64/lib/odbc.so', DB2_FENCED 'N')
create server ODBC_NZ type odbc version 3 wrapper ODBC options (node 'NZSQL', PUSHDOWN 'Y')
CREATE USER MAPPING FOR user SERVER ODBC_NZ OPTIONS(REMOTE_AUTHID 'admin', REMOTE_PASSWORD 'password')
create nickname NTZ_NICK for ODBC_NZ."ADMIN"."ADDRESSES"

Optimizing ODBC wrapper performance with the ODBC tuning utility (db2fedsvrcfg)
For example:
db2fedsvrcfg -s ODBC_NZ -dbname nzdb -dsn NZSQL -m /opt/oemclient/odbc60/64/lib/odbc.so -u admin -p password

This tool will generate a serials of alter server statement to tuning the ODBC wrapper server options.
Run the generated alter server statements.

Here are sample settings with my environment:

select substr(OPTION,1,40),substr(SETTING,1,12) from
syscat.serveroptions where servername = <server name> order by option"
1 2
---------------------------------------- ------------
DB2_CHAR_BLANKPADDED_COMPARISON Y
DB2_COLFUNC Y
DB2_COLFUNC_DISTINCT Y
DB2_COLFUNC_EXPR Y
DB2_COUNT_NON_DISTINCT Y
DB2_FUNCTION_IN_COUNT Y
DB2_GB_MORE_THAN_NON_AGGR Y
DB2_GROUP_BY Y
DB2_MAXIMAL_PUSHDOWN Y
DB2_MAX_GB_SIZE 32677
DB2_MAX_OB_SIZE 32677
DB2_MAX_SEL_SIZE 32677
DB2_NESTED_TAB_EXPR Y
DB2_OUTER_JOINS Y
DB2_UPD_SET_DEFAULT N
DB2_VARCHAR_BLANKPADDED_COMPARISON Y
PUSHDOWN Y
VARCHAR_NO_TRAILING_BLANKS Y


Trouble Shooting
db2 "select count(*) from NTZ_NICK"
1
-----------
SQL0802N Arithmetic overflow or other arithmetic exception occurred.
SQLSTATE=22003

Explanation:
The remote Netezza table has around 4.3 billion rows. The count(*) exceed the maximum DB2 integer.

Workaround:

Use COUNT_BIG() instead of count(). You need to create function mapping from DB2 COUNT_BIG to Netezza Count for all the column types which need to be counted.

For example:
CREATE FUNCTION MAPPING my_mapping0 FOR SYSIBM.COUNT_BIG() SERVER <server_name> OPTIONS (REMOTE_NAME 'COUNT(*)')
CREATE FUNCTION MAPPING my_mapping1 FOR SYSIBM.COUNT_BIG(SYSIBM.VARCHAR) SERVER <server_name> OPTIONS (REMOTE_NAME 'COUNT(:D :1P)')
CREATE FUNCTION MAPPING my_mapping2 FOR SYSIBM.COUNT_BIG(SYSIBM.CHAR) SERVER <server_name> OPTIONS (REMOTE_NAME 'COUNT(:D :1P)')
...

db2 "select count_big(*) from NTZ_NICK"
1
---------------------------------
4302305051.

Reference Documents
Using IBM Branded Datadirect ODBC driver:
http://www-01.ibm.com/support/docview.wss?rs=0&uid=swg21299634

db2fedsvrcfg tool:

Views: 770

Tags: COUNT, COUNT_BIG, Configure, DB2, Federation, Information, Integration, Netezza, ODBC, SQL0802N, More…Server, option, tuning, wrapper

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Comment by Fabrizio Napolitano on March 15, 2011 at 5:42am

Hi , very good article thank you.

 

Have you had any experiences in writing to Netezza through Federation Server?

is there a way to use ODBC array insert functionality? to speed up inserts?

 

thanks for any comment

Fabrizio

Try BLU Acceleration on Cloud

© 2014   Created by channeldb2.

Badges  |  Report an Issue  |  Terms of Service