WebSphere Message Broker integration with Netezza using ODBC and JDBC

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 for its performance is that it deeply integrates hardware, software and storage to simplify and accelerate deployment to deliver business analytics value in a 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 the simple deployment, out-of-the-box optimization, no tuning and minimal on-going maintenance, the IBM PureData System for Analytics have 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 Data Base Connectivity) and JDBC ( Java Data Base C onnectivity) 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 a sample.

Driver=${LD_LIBRARY_PATH}/libnzodbc.so Description=NetezzaSQL ODBC Servername=<hostIp> ReadOnly=false ShowSystemTables=false LegacySQLTables=false LoginTimeout=0 QueryTimeout=0 DateFormat=1 NumericAsChar=false SQLBitOneZero=false StripCRLF=false

[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

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

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

JDBC Setup:

Windows/Linux: (Both environments 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’

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

1. Create the simple message flow in Message Broker using Java Compute node and configure the DSN.
2. 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

Originally published at https://blogs.perficient.com on May 17, 2014.