Netezza connection

DaleK · Post by **DaleK** » Fri Dec 11, 2009 2:59 pm

Hello All,

I have a Team in my shop that is bringing in netezza for a POC.
They have questioned me as to if we can connect DataStage to it.
I told them that netezza should provide drivers for us and then we should have little issue using the ODBC stage to connect and interact with netezza.

Has anyone done this with Datastage server ed. 7.5.1 on unix?
Did netezza provide you with the drivers?
Any challenges doing this?

Thanks in advance for you help
DaleK

DaleK · Post by **DaleK** » Thu Jan 07, 2010 11:32 am

Hello everyone,

Okay so we have Netezza in house for the Proof of concept.
The gave me a set of drivers and I unpacked them into temp directory.
/dstmp/Netezza

I added this entry to the .odbc.ini file

[NZSQL]
Driver=/dstmp/Netezza/lib/libnzodbc.so
Description=NetezzaSQL ODBC
Servername=NP5
Port=5480
Database=landsend
Username=
Password=
ReadOnly=false
ShowSystemTables=false
LegacySQLTables=false
LoginTimeout=0
QueryTimeout=0
DateFormat=1
NumericAsChar=false
SQLBitOneZero=false
StripCRLF=false
securityLevel=preferredUnSecured
caCertFile=

I added this information to the dsenv file

######## Add Netezza path information ########################
Netezza_Home=/dstmp/Netezza/lib
NZ_ODBC_INI_PATH=/dstmp/Netezza/lib/bin
PATH=$PATH:$NZ_ODBC_INI_PATH
export PATH NZ_ODBC_INI_PATH
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$Netezza_Home
export LD_LIBRARY_PATH

then in the projects uvodbc.config file added this entry

<NZSQL>
DBMSTYPE = ODBC

I executed ./dsenv and I see the new path

When I go into the datastage project and try to import something I get this error

DSR.MetaGeta(GET.TABLES)(SQLConnect('NZSQL','dstage')): BCI Error:
SQLSTATE=HY000,CODE=33,[DataStage][SQL Client][ODBC]Server and/or port attributes are empty

I can connect from my DataStage server using command line NZSQL from the driver lib.

when I make a change to .odbc.ini do I need to restart Datastage?

thanks
Dale

chulett · Post by **chulett** » Thu Jan 07, 2010 9:25 pm

Before you ask, I don't know anything about Netezza but for your question about restarting DataStage for ODBC changes, the answer is 'no' - that's not necessary.

DaleK · Post by **DaleK** » Fri Jan 08, 2010 9:15 am

Thanks Craig. that is what I thought but wanted to make sure.

Has anyone installed different ODBC drivers for datastage.
I placed the drivers in a temp directory since this is just a POC project.
Once I unpack them do I need to do anything else as part of the install process.

Thanks
Dale

DaleK · Post by **DaleK** » Fri Jan 08, 2010 1:52 pm

Okay I got this one working. many trials and errors, plus the correct authotity on the Database is good too :-)

changed the odbc conncetion to the same name as the database
<landsend>
DBMSTYPE = ODBC

Plus I had the .odbc.ini in dsengine directory and renamed that to odbc.ini
plus set the password and user id to a valid user id and password so when I ran the Netezza odbc command sql exe (nzodbcsql) it would log in without a problem.

chulett · Post by **chulett** » Fri Jan 08, 2010 3:07 pm

Very nice. Thanks for posting that.

nkreddy · Post by **nkreddy** » Thu Jan 21, 2010 11:04 am

Dale,

I am glad you got the Netezza connection working....Would you please tell us the steps you took to get this working...This way we can save the many trials and errors that you faced during the process.

Thank you,
NK