WebSphere Message Broker integration with Netezza using ODBC/JDBC

What is Netezza:

At an extremely high level Netezza is a massively Parallel Process Database Appliance designed to process and analyze large amounts of data. Netezza’s core value is to keep the things simple and accelerating high performance analysis of data to help clients uncover insights into their business. One of the reasons of it’s performance is that it is deeply integrates hardware, software and storage to simplify and accelerate deployment to deliver business analytics value in few days. This data warehouse appliance requires nearly no to minimal tuning and administration – e.g., no storage administration, less database/system administration – further reducing the cost of deploying and total cost of ownership.

IBM Netezza appliances are now part of IBM PureSystems – expert integrated systems with built in expertise, integration by design and a simplified user experience. Part of the PureData family, the Netezza appliance is now known as the PureData System for Analytics. It has the same key design tenets of simplicity, speed, scalability and analytics power that was fundamental to Netezza appliances. With simple deployment, out-of-the-box optimization, no tuning and minimal on-going maintenance, the IBM PureData System for Analytics has the industry’s fastest time-to-value and lowest total-cost-of-ownership.

What is Message Broker:

IBM WebSphere® Message Broker is an Enterprise Service Bus (ESB) built for universal connectivity and transformation in heterogeneous IT environments. WebSphere Message Broker distributes information and data generated by business events in real time to people, applications, and devices throughout your extended enterprise and beyond.

IBM Integration Bus represents a significant evolution of the WebSphere Message Broker technology base, and includes new features such as policy-based workload management, business rules, and integration with Business Process Management (BPM) and Microsoft .NET. It also incorporates WebSphere Enterprise Service Bus (ESB) use cases, and WebSphere ESB capabilities will be folded into IBM Integration Bus over time, with conversion tools for initial use cases built-in from day one.

Below part of the article shows the step by step procedure to integrate IBM Netezza appliance with IBM WebSphere Message Broker (now it is called as Integration Bus v9) using ODBC (Open DataBase Connectivity) and JDBC (Java DataBase Connectivity) mechanisms.

ODBC Setup:

Windows:

1. Download the Agility workbench to connect to Netezza appliance. (Please find below link)
2. Install the Netezza ODBC drivers for Windows from IBM Fix Central. (Please find below link)
3. Create Data Source Name to connect to Netezza appliance.
4. Create the simple message flow in Message Broker using compute node and configure DSN.
5. Restart the Broker for the environment variables that were created during Netezza ODBC Driver installation to reflect.

Linux:

1. Download Netezza ODBC drivers for Linux from IBM Fix Central. (Please find below link)
2. Install Netezza ODBC client on Linux.
3. Edit the odbc.ini with Netezza connection info. Below one is sample.

                    [IDH_IDS_D1]

                   Driver=${LD_LIBRARY_PATH}/libnzodbc.so

                   Description=NetezzaSQL ODBC

                   Servername=<hostIp>

                   Port=5480

                   Database=<database>

                   Username=<userId>

                   Password=<password>

                   ReadOnly=false

                   ShowSystemTables=false

                   LegacySQLTables=false

                   LoginTimeout=0

                  QueryTimeout=0

                  DateFormat=1

                  NumericAsChar=false

                  SQLBitOneZero=false

                  StripCRLF=false

4. Edit odbcinst.ini with Netezza connection information

                  [NetezzaSQL]

                  Driver           = ${LD_LIBRARY_PATH}/libnzodbc.so

                  Setup            = ${LD_LIBRARY_PATH}/libnzodbc.so

                  APILevel         = 1

                  ConnectFunctions = YYN

                  Description      = Netezza ODBC driver

                  DriverODBCVer    = 03.51

                  DebugLogging     = false

                  LogPath          = /tmp

                  UnicodeTranslationOption = utf8

                  CharacterTranslationOption = all

                  PreFetch         = 256

                  Socket           = 16384

5. Add environment variables as below in bash profile.

                  export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/tmp/netezza/client/lib64

                  export JAVA_HOME=/opt/ibm/java-x86_64-60

                  export PATH=${PATH}:/tmp/netezza/client/bin64

                  export NZ_ODBC_INI_PATH=/opt/ibm/mqsi/8.0.0.1/ODBC/V6.0

6. Create the simple message flow in Message Broker using compute node and configure DSN.
7. Restart the Broker for the environment variables to reflect.

JDBC Setup:

Windows/Linux: (Both environment follow the same procedure steps)

1. Download the Netezza JDBC jar file from IBM Fix Central. (Please find below references)
2. Place the Netezza JDBC jar file in the server and give execute access.
3. Create the mqsi configurable service.

                  mqsideleteconfigurableservice BRK8 -c JDBCProviders -o NZ_D1

                  mqsicreateconfigurableservice BRK8 -c JDBCProviders -o NZ_D1 -n connectionUrlFormat,databaseName,databaseType,description,jarsURL,jdbcProviderXASupport,portNumber,serverName,type4DatasourceClassName,type4DriverClassName -v “jdbc:netezza://[serverName]:[portNumber]/[databaseName];user=[user];password=[password];,<database>,default_Database_Type,Netezza Database,/tmp/netezza/jdbc,false,5480,<hostIp>,org.netezza.datasource.NzDatasource,org.netezza.Driver”

                  *Please note  the dataSourceClassName and Driver class name as below,

                  type4DatasourceClassName=’org.netezza.datasource.NzDatasource’

                  type4DriverClassName=’org.netezza.Driver’

                  mqsichangeproperties BRK8 -c JDBCProviders -o NZ_D1 -n securityIdentity -v NzSecId

                  mqsireportproperties BRK8 -c JDBCProviders -o NZ_D1 -r

                  JDBCProviders

                  NZ_D1

                  connectionUrlFormat=’jdbc:netezza://[serverName]:[portNumber]/[databaseName];user=[user];password=[password];’

                  connectionUrlFormatAttr1=”

                  connectionUrlFormatAttr2=”

                  connectionUrlFormatAttr3=”

                  connectionUrlFormatAttr4=”

                  connectionUrlFormatAttr5=”

                  databaseName=<database>

                  databaseSchemaNames=’useProvidedSchemaNames’

                  databaseType=’default_Database_Type’

                  databaseVersion=’default_Database_Version’

                  description=’Netezza Database’

                  environmentParms=’default_none’

                  jarsURL=’/tmp/netezza/jdbc’

                  jdbcProviderXASupport=’false’

                  maxConnectionPoolSize=’0′

                  portNumber=’5480′

                  securityIdentity=’NzSecId’

                  serverName=<hostIp>

                  type4DatasourceClassName=’org.netezza.datasource.NzDatasource’

                  type4DriverClassName=’org.netezza.Driver’

4. Create and execute mqsetdbparams to set the user id and password to connect to Netezza.

                  mqsisetdbparms BRK8 -n jdbc::NzSecId -u <username> -p <password>

5. Create the simple message flow in Message Broker using Java Compute node and configure the DSN.
6. Restart the broker application/message flow.

Attachments:

Message Broker Project Files:

Netezza Integration

References:

http://www-933.ibm.com/support/fixcentral/

Type “IBM Netezza NPS Software and Clients” and Select the respective version.

http://www-933.ibm.com/support/fixcentral/swg/selectFixes?parent=ibm~Information%2BManagement&product=ibm/Information+Management/Netezza+NPS+Software+and+Clients&release=NPS_7.0.3&platform=All&function=all

http://www-01.ibm.com/support/docview.wss?uid=swg21397846

http://www.aginity.com/workbench/netezza-download-link/

http://www.bpmnw.com/bpm/assets/File/Netezza_odbc_jdbc_guide.pdf

http://www-01.ibm.com/software/data/netezza/

http://www-03.ibm.com/software/products/en/ibm-integration-bus