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:
- Download the Agility workbench to connect to Netezza appliance. (Please find below link)
- Install the Netezza ODBC drivers for Windows from IBM Fix Central. (Please find below link)
- Create Data Source Name to connect to Netezza appliance.
- Create the simple message flow in Message Broker using compute node and configure DSN.
- Restart the Broker for the environment variables that were created during Netezza ODBC Driver installation to reflect.
Linux:
- Download Netezza ODBC drivers for Linux from IBM Fix Central. (Please find below link)
- Install Netezza ODBC client on Linux.
- 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
- 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
- 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
- Create the simple message flow in Message Broker using compute node and configure DSN.
- Restart the Broker for the environment variables to reflect.
JDBC Setup:
Windows/Linux: (Both environment follow the same procedure steps)
- Download the Netezza JDBC jar file from IBM Fix Central. (Please find below references)
- Place the Netezza JDBC jar file in the server and give execute access.
- 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’
- Create and execute mqsetdbparams to set the user id and password to connect to Netezza.
mqsisetdbparms BRK8 -n jdbc::NzSecId -u <username> -p <password>
- Create the simple message flow in Message Broker using Java Compute node and configure the DSN.
- Restart the broker application/message flow.
Attachments:
Message Broker Project Files:
References:
http://www-933.ibm.com/support/fixcentral/
Type “IBM Netezza NPS Software and Clients” and Select the respective version.
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
