DataStage 7.5 ODBC stage error

fkana · Post by **fkana** » Tue Jan 04, 2005 4:28 am

Hi there,

We have just upgraded from DS6 to DS7.5 and are experiencing the following problem:

I am getting the following errors when using an ODBC stage in DataStage 7.5 trying to connect to IQ:
Error calling subroutine: DSD.Browse (Action=3); check DataStage is set up correctly in project PH2_DWH_PROD
Error calling subroutine: DSD.Browse (Action=2); check DataStage is set up correctly in project PH2_DWH_PROD
(The connection is broken (81002))

When using the same table and SQL query with a SybaseOC stage everything works fine.

What I also picked up was that when I use a table in an ODBC stage with a generated query irrespective of the amount of columns I select, it works perfectly. As soon as I use an user-defined SQL query(same table as above) in an ODBC stage I once again get the above errors.

I can import and export all tables in my database. I have used different usernames but still to no avail.

TIA.

Faeez

Luk · Post by **Luk** » Tue Jan 04, 2005 7:59 am

I have simillar problem with ODBC connection to MySQL - I have simmillar error. I have posted it here earlier.
This is abnormal termination of DS, segmentation fault.
Unfortunetelly I heavn't solved it yet.
I think that is a problem with shared libraries to ODBC manager in Unix (libodbc.so) - but this is only my assumption.
Try to test your ODBC connection in DS shell (./DataStage/DSEngine/bin/dssh) - command CONNECT DNS , where DNS is name of your ODBC source.
If you know sth new about this error please write it.

Regards
LUK

fkana · Post by **fkana** » Tue Jan 04, 2005 8:22 am

Hi LUK

I have tried it using dssh and it connects fine. Some times it works in DS and some times it does not.

Thanx.

Faeez

Luk · Post by **Luk** » Tue Jan 04, 2005 9:02 am

Try to connect in dssh and write SQL querry with bad syntax. In my case connection in dssh works perfect until I write SQL querry with bad syntax. Then I get abnormal termination error! I have noticed that when using DS designer and for example impot table definition I've got error like yours when there is SQL error during executing DSR_IMPORT routine.

Luk · Post by **Luk** » Tue Jan 04, 2005 9:11 am

one more question!
Do you have path to branded_odbc manager (libodbc.so) in your LD_LIBRARY_PATH in dsenv. It is important that no other path to other odbc managers precedes this path.
or try to use odbc manager wchich you get with sybase and put path to it into LD_LIBRARY_PATH before path to branded odbc

fkana · Post by **fkana** » Tue Jan 04, 2005 11:55 pm

This is what my dsenv looks like. It worked fine in DS6.

#######################################################################
#
# DataStage Server Runtime
#
# Module dsenv
#
# (c) Copyright 2000 Informix Corporation. - All Rights Reserved
# This is unpublished proprietary source code of Informix Corporation.
# The copyright notice above does not evidence any actual or intended
# publication of such source code.
#
########################################################################
#
# Maintenence log - insert most recent change descriptions at top
#
# Date.... GTAR# WHO Description.................................
# 06/13/02 8344 LAG Updated for 5.2.1
# 02/18/02 37054 AMW Include $HOME in UniRPC's config.
# 07/06/01 34525 LAG Porting to 5.1
# 05/01/00 27112 LAG Initial creation
#
########################################################################
#
# dsenv
#
# sets up environmental variables for running DataStage
#
########################################################################
dshome=`cat /.dshome`

# ALL PLATFORMS SECTION

ODBCINI=/export/home/dsadm/Ascential/DataStage/DSEngine/.odbc.ini; export ODBCINI
HOME=${HOME:-/}; export HOME
PATH=$PATH:$dshome/bin:/usr/local/bin:/usr/local; export PATH
#UDTHOME=$dshome/../ud41; export UDTHOME
#UDTBIN=$dshome/../ud41/bin; export UDTBIN
ODBCHOME=/export/home/dsadm/Ascential/DataStage/DSEngine/; export ODBCHOME

# PLATFORM SPECIFIC SECTION

LD_LIBRARY_PATH=/usr/lib/lwp:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
LD_LIBRARY_PATH=/.dslibs:$dshome/lib:$LD_LIBRARY_PATH:/usr/local/lib:/export/home/sybase/ASIQ-12_5/lib32; export LD_LIBRARY_PATH

### Sybase ASIQ ###
SYBASE=/export/home/sybase
export SYBASE
ASDIR=$SYBASE/ASIQ-12_5
export ASDIR
SYBASE_OCS=OCS-12_5
export SYBASE_OCS
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ASDIR/usrlib:$SYBASE/$SYBASE_OCS/lib:$ASDIR/lib32
export LD_LIBRARY_PATH
LD_LIBRARY_PATH_64=$LD_LIBRARY_PATH_64:$ASDIR/lib:$ASDIR/usrlib:$SYBASE/$SYBASE_OCS/lib
export LD_LIBRARY_PATH_64
PATH=$PATH:$ASDIR/bin:$SYBASE/$SYBASE_OCS/bin
export PATH

### Ingres Advantage ###
II_SYSTEM=/export/home
export II_SYSTEM
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/export/home/ingres/lib
export LD_LIBRARY_PATH
ODBCSYSINI=/export/home/dsadm/Ascential/DataStage/branded_odbc
export ODBCSYSINI

Thanx.

Faeez

Luk · Post by **Luk** » Fri Jan 07, 2005 5:41 am

Still I don't know what the problem solution is but , when I have installed and started to use iODBC manager instead of unixODBC everything works fine!!