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
Netezza connection
Moderators: chulett, rschirm, roy
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
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
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.
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.