Error connecting to NZ using ODBC

[vkumar]

Hello...

I am trying to configure ODBC to read a table on Netezza Database. Actually I am not even able to connect to the Database....

Executing the "example" script in branded_odbc results in this error. I am able to connect to Netezza database from AIX.


[turks: isadmin8 :/opt/IBM/InformationServer/Server/branded_odbc/example] example
example DataDirect Technologies, Inc. ODBC Example Application.

Enter the data source name : NZDEVSTG

Enter the user name : nz_usr

Enter the password : nz_pwd
Illegal instruction (core dumped)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
and when I tried to use DS_CONNECT here is the error


>DS_CONNECT NZDEVSTG
Enter username for connecting to 'NZDEVSTG' DBMS [isadmin8]: nz_usr
Enter password for stgdev:nz_pwd
Abnormal termination of DataStage.
Fault type is 4. Layer type is BASIC run machine.
Fault occurred in BASIC program DS_CONNECT at address adc.
Illegal instruction (core dumped)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Steps I performed after installing the NZ odbc driver and Data Direct :

Step-1: Updated odbc.ini

[NZDEVSTG]
Driver = /usr/nps/kit/lib/libnzsqlodbc3_64bit.so
Description = Netezza CDM Dev Database
Servername = dinar
Port = 5480
Database = cmdev01
Username = nz_usr
Password = nz_pwd
ReadOnly = false
ShowSystemTables = true
LegacySQLTables = false
LoginTimeout = 0
QueryTimeout = 0
DateFormat = 1
NumericAsChar = false
SQLBitOneZero = false
StripCRLF = false

Step-2: Updated uvodbc.config in both Project and DSEngine directories.

<NZDEVSTG>
DBMSTYPE = ODBC


Step-3: Updated dsenv PATH and LIBPATH


#!/bin/sh
####################################################################
#
# dsenv - DataStage environment file
...
...

LIBPATH=`dirname $DSHOME`/branded_odbc/lib:`dirname $DSHOME`/DSComponents/lib:`dirname $DSHOME`/DSComponents/bin:$DSHOME/lib:$DSHOME/uvdlls:`dirname $
DSHOME`/PXEngine/lib:$ASBHOME/apps/jre/bin:$ASBHOME/apps/jre/bin/classic:$ASBHOME/lib/cpp:$ASBHOME/apps/proxy/cpp/aix-all-ppc_64:$ORACLE_HOME/lib/


..
NZ_HOME="/usr/nps/kit/"
export NZ_HOME
...
...
export PATH=$PATH:$ORACLE_HOME/bin:$NZ_HOME/bin
export LIBPATH=$LIBPATH:$ORACLE_HOME/lib:$NZ_HOME/lib64

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`~
Here is the NZ library...

[turks: isadmin8 :/usr/nps/kit/lib] ls -ltr
total 97848
-r--r--r-- 1 nz nz 422 Jul 29 2008 odbcinst.ini.sample
-r--r--r-- 1 nz nz 710 Jul 29 2008 odbc.ini.sample
-r--r--r-- 1 nz nz 5876 Jul 29 2008 ODBC_README
-rwxr-xr-x 1 nz nz 851251 Jul 29 2008 libnzodbc.a
-rwxr-xr-x 1 nz nz 16120717 Jul 29 2008 libnzodbcT_I_U16.so
-rwxr-xr-x 1 nz nz 16118290 Jul 29 2008 libnzodbcT_I_U8.so
-rwxr-xr-x 1 nz nz 227250 Jul 29 2008 libnzodbcT_L9_U16.so
-rwxr-xr-x 1 nz nz 16119961 Jul 29 2008 libnzodbcT_I_U32.so
-rwxr-xr-x 1 nz nz 224487 Jul 29 2008 libnzodbcT_L9_U8.so
-rwxr-xr-x 1 nz nz 227346 Jul 29 2008 libnzodbcT_L9_U32.so
-rwxr-xr-x 1 nz nz 173847 Jul 29 2008 nzjdbc3.jar
lrwxrwxrwx 1 nz nz 20 Jan 26 2009 libnzsqlodbc3_64bit.so -> ../lib64/libnzodbc.a
lrwxrwxrwx 1 nz nz 11 Jan 26 2009 libnzsqlodbc3.so -> libnzodbc.a
[turks: isadmin8 :/usr/nps/kit/lib]



Pls advise...

Thnx

[chulett]

DataStage is 32bit so I would assume you'd need to use 32bit NZ drivers with it.

[vkumar]

Hello ...

DS8.1 is 64bit and the drivers are expected to be 64bit. It was a dsenv issue.

I had to make sure the PATH and LIBPATH are set correctly and the Oracle and NZ directories are pointed correctly (xxbit) and also placed correctly in the PATH variable.



Here is the env

USER=isadmin8
LOGNAME=isadmin8
LOGIN=isadmin8
HOME=/home/isadmin8
PATH=/opt/IBM/InformationServer/Server/PXEngine/etc:.:/Projects/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/usr/java14/jre/bin:/usr/java14/bin:/usr/java131/jre/bin:/usr/java131/bin:/opt/IBM/InformationServer/Server/PXEngine/bin:/opt/IBM/InformationServer/Server/DSEngine/bin:/opt/postware:/opt/postware_7.80c/postware/acelib:/opt/postware_7.80c/postware/adm:/usr/nps/kit/bin/
.....
LOCPATH=/usr/lib/nls/loc
LC__FASTMSG=true
ODMDIR=/etc/objrepos
BOE_REGISTRYHOME=/var/opt
HOSTTYPE=rs6000
VENDOR=ibm
OSTYPE=aix
MACHTYPE=rs6000
SHLVL=1
PWD=/opt/IBM/InformationServer/Server/DSEngine
GROUP=dstage
...
APT_ORCHHOME=/opt/IBM/InformationServer/Server/PXEngine
DSHOME=/opt/IBM/InformationServer/Server/DSEngine
PW_PATH=/opt/postware:/opt/postware_7.80c/postware/acelib:/opt/postware_7.80c/postware/adm
APT_CONFIG_FILE=/opt/IBM/InformationServer/Server/Configurations/default.apt
APT_PM_SCORE_DIR=/Projects/tmp
NZBIN=/usr/nps/kit/bin
NZDATA=/etl01/cdm/cdm01/load/data
NZ_ODBC_INI_PATH=/opt/IBM/InformationServer/Server/DSEngine
ODBCINI=/opt/IBM/InformationServer/Server/DSEngine/.odbc.ini
NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat
LIBPATH=/opt/IBM/InformationServer/Server/Projects/test5/buildop:/opt/IBM/InformationServer/Server/DSComponents/lib:/opt/IBM/InformationServer/Server/DSComponents/bin:/opt/IBM/InformationServer/Server/DSParallel:/opt/IBM/InformationServer/Server/PXEngine/user_lib:/opt/IBM/InformationServer/Server/PXEngine/lib:/opt/IBM/InformationServer/Server/Projects/test5/RT_BP1.O:/opt/IBM/InformationServer/Server/branded_odbc/lib:/opt/IBM/InformationServer/Server/DSEngine/lib:/opt/IBM/InformationServer/Server/DSEngine/uvdlls:/opt/IBM/InformationServer/ASBNode/apps/jre/bin:/opt/IBM/InformationServer/ASBNode/apps/jre/bin/classic:/opt/IBM/InformationServer/ASBNode/lib/cpp:/opt/IBM/InformationServer/ASBNode/apps/proxy/cpp/aix-all-ppc_64:/usr/oracle/product/10.2.0/db/:/opt/IBM/InformationServer/ASBNode/apps/jre/bin/j9vm:/usr/lib:/lib:/opt/IBM/InformationServer/Server/PXEngine/lib:/usr/nps/kit/lib/:.


Thanx
SvK

[vkumar]

Finally it works....

Resolution Steps:

1) Update dsenv - Update the NZ and Oracle paths to point to 64bit version libraries.
2) Update odbc.ini - Use the 64bit driver
3) Restart Datastage Server.


Thanx
SvK

[chulett]

Thanks. There are so few 64bit versions of DataStage I tend to forget they even exist.

[ray.wurlod]

They're getting there. WAS 6.0.2.127 is 64-bit and is the preferred WAS for DataStage version 8.1.

[dspxguy]

WAS 6.0.2.127 is 64-bit ..

6.0.2 fix Pack 27

[ray.wurlod]

Yeah, that.