How to add Netezza ODBC datasource in IBM Unica Campaign?

This tutorial is about adding Netezza (a data warehouse appliance) ODBC based data source in IBM Unica Campaign. So that you can import tables from data warehouse to Unica and perform operations.

We will install Netezza client on the machine where we are running IBM Unica Campaign. And will configure ODBC on this machine.

Installing Netezza client

1. Extract nz-aixclient-v6.0.5.p7.tar somewhare in your directory
2. Run unpack script and install Netezza client on /usr/local/nz

Installing ODBC Data Direct

This application will help to test our connectivity with Netezza using above installed client. For IBM Unica Campaign, we will add odbc.so file in our LIBPATH.
1. Extract datadirect.package.tar to /opt/
2. It will be extracted as /opt/odbc64v51

ODBC.ini file

If it does not exist in your envoirnment, create it.
1. Create a file with name odbc.ini
2. Add following entry of your Netezza server

[NZSQL]
Driver                = /usr/local/nz/lib64/libnzodbc.so
Description           = NetezzaSQL ODBC
Servername            =
Port                  = 5480
Database              =
Username              =
Password              =
ReadOnly              = false
ShowSystemTables      = false
LegacySQLTables       = false
LoginTimeout          = 0
QueryTimeout          = 0
DateFormat            = 1
NumericAsChar         = false
SQLBitOneZero         = false
StripCRLF             = false
CancelAsFreeSmt       = false
CommLog               = false
Ksqo                  = false
Lie                   = false
Optimizer             = false
Parse                 = false
PreFetch              = 256
Protocol              = 7.0
Socket                = 16384
DateFormat            = 1
TranslationDLL        =
TranslationName       =
TranslationOption     =

3. You will add this file name with path in ODBC environment variable in next step
4. Make sure you have libnzodbc.so in /usr/local/nz/lib64 (if you don’t have, get it from here)

Setting environment variables

To run Netezza client you have to set following environment variables. I have set these variables in /etc/profile because I want them for root user.

ODBCINI=/u01/CONFIG/odbc.ini
NZ_ODBC_INI_PATH=/u01/CONFIG
NETEZZA=/usr/local/nz
LIBPATH=$NETEZZA/lib64:$NETEZZA/lib
PATH=$PATH:$NETEZZA/bin64:$NETEZZA/bin
LANG=en_US

export ODBCINI
export NETEZZA
export NZ_ODBC_INI_PATH
export PATH
export LANG

Testing connectivity with Netezza

1. Navigate to /opt/odbc64v51/example
2. Run example script
3. It will ask data source, username and password
4. Give those values to verify if you have successfully setup every thing

Important: Must read Netezza ODBC JDBC Configuration Guide for more understanding.

Add Netezza environment variables in setenv.sh for Unica listener

1. Add following environment variables in setenv.sh present in /u01/IBM/Unica/Campaign/bin

NETEZZA=/usr/local/nz
export NETEZZA
NZ_ODBC_INI_PATH=/u01/CONFIG
export NZ_ODBC_INI_PATH
ODBCINI=/u01/CONFIG/odbc.ini
export ODBCINI

2. Update LIBPATH in setenv.sh and it will look like this:

LIBPATH=$CAMPAIGN_HOME/bin:/usr/lib:$ORACLE_HOME/lib:$NETEZZA/lib64:$NETEZZA/lib:/opt/odbc64v51/lib

3. Commit your changes and restart Unica listener

Import Netezza data source template in IBM Unica Campaign

Since you have set up the Netezza client. Now you have to add it as a data source in IBM Unica Campaign. For that first, you have to import Netezza data source template in IBM Unica Campaign.
1. Navigate to /u01/IBM/Unica/Platform/tools/bin
2. Run following command

./configTool.sh -i -p &amp;quot;Affinium|Campaign|partitions|partition1|dataSources&amp;quot; -f /u01/IBM/Unica/Campaign/conf/NetezzaTemplate.xml

Add Netezza data source in IBM Unica Campaign

1. Open IBM Unica Platform and navigate to Settings > Configuration.
2. Navigate to data source in Campaign > partitions > partion1 > dataSources > NetezzaTemplate

3. Configure following properties
4. Now you are good to import tables from Netezza data warehouse