Error viewing data using ODBC for Informix.

I_Server_Whale · Post by **I_Server_Whale** » Mon Jul 30, 2007 2:40 pm

Hi All,

We have been trying to install both ODBC stage and Informix CLI plug-in.

We are able to import the metadata using "ODBC Table Definitions", but not able to view data. The following is the error message:

Code: Select all

Informix_Test..ODBC_0.DSLink2: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:SELECT dss:informix.d_time.time_id, dss:informix.d_time.cal_date, dss:informix.d_time.day_name FROM d_time 
SQLSTATE=S1000, DBMS.CODE=-256
[DataStage][SQL Client][ODBC][DataDirect][ODBC Informix Wire Protocol driver][Informix]Transaction not available.

Also, the "Get SQL Info" button works and retrieves the following schema delimiters.

Code: Select all

:.

And when tried to import meta-data through "Plugin Metadata Definitons". We get the following error:

Code: Select all

Unable to initialize plug-in:

The following are the "dsenv" and ".odbc.ini" files:

dsenv

Code: Select all

#!/bin/sh
####################################################################
#
# dsenv - DataStage environment file
#
#       Copyright (c) 1997 - 2004 Ascential Software Corporation. All Rights Reserved
#       This is unpublished proprietary source code of Ascential Software Corporation
#       The copyright notice above does not evidence any actual or
#       intended publication of such source code.
#
# This script is sourced by the DataStage dsrpcd daemon to establish
# proper environment settings for DataStage client connections.
#
# This script may also be sourced by bourne shells to establish
# proper environment settings for local DataStage use.
#
####################################################################

# PLATFORM SPECIFIC SECTION

set +u

if [ -z "$DSHOME" ] && [ -f "/.dshome" ]
then
        DSHOME=`cat /.dshome`
        export DSHOME
fi

if [ -z "$DSHOME" ]
then
        DSHOME=/dss/Ascential/DataStage/DSEngine; export DSHOME
fi

if [ -z "$APT_ORCHHOME" ]
then
        APT_ORCHHOME=; export APT_ORCHHOME
fi

if [ -z "$UDTHOME" ]
then
        UDTHOME=/dss/Ascential/DataStage/ud41; export UDTHOME
        UDTBIN=/dss/Ascential/DataStage/ud41/bin; export UDTBIN
fi

if [ -n "$DSHOME" ] && [ -d "$DSHOME" ]
then
        ODBCINI=$DSHOME/.odbc.ini; export ODBCINI
        HOME=${HOME:-/}; export HOME

        #LANG="<langdef>";export LANG
        #LC_ALL="<langdef>";export LC_ALL
        #LC_CTYPE="<langdef>";export LC_CTYPE
        #LC_COLLATE="<langdef>";export LC_COLLATE
        #LC_MONETARY="<langdef>";export LC_MONETARY
        #LC_NUMERIC="<langdef>";export LC_NUMERIC
        #LC_TIME="<langdef>";export LC_TIME
        #LC_MESSAGES="<langdef>"; export LC_MESSAGES

        #LD_PRELOAD must be unset on HP-UX 11.00
        if [ 1111 -le `uname -r | cut -f2 -d.``uname -r | cut -f3 -d.` ]
        then
                #SHLIB_PATH=`dirname $DSHOME`/branded_odbc/lib:$DSHOME/lib:$DSHOME/uvdlls:$DSHOME/java/jre/lib/PA_RISC:$DSHOME/java/jre/lib/PA_RISC/hotspot:$SHLIB_PATH
                SHLIB_PATH=`dirname $DSHOME`/branded_odbc/lib:$DSHOME/lib:$DSHOME/uvdlls:$DSHOME/java/jre/lib/PA_RISC2.0:$DSHOME/java/jre/lib/PA_RISC2.0/hotspot:$SHLIB_PATH
                export SHLIB_PATH
                #LD_PRELOAD=$DSHOME/java/jre/lib/PA_RISC/hotspot/libjvm.sl
                LD_PRELOAD=$DSHOME/java/jre/lib/PA_RISC2.0/hotspot/libjvm.sl
                export LD_PRELOAD
        else
                SHLIB_PATH=`dirname $DSHOME`/branded_odbc/lib:$DSHOME/lib:$DSHOME/uvdlls:$DSHOME/java/jre/lib/PA_RISC:$DSHOME/java/jre/lib/PA_RISC/hotspot:$SHLIB_PATH
                export SHLIB_PATH
        fi
fi

#Informix Environment Variables
INFORMIXSERVER=dbengine1_tcp; export INFORMIXSERVER
ONCONFIG=onconfig_1; export ONCONFIG
INFORMIXDIR=/usr/informix; export INFORMIXDIR
INFORMIXBIN=/usr/informix/bin; export INFORIXBIN
INFORMIXC=/usr/bin/c89; export INFORMIXC
THREADLIB=POSIX; export THREADLIB
HOME=/dss/Ascential/DataStage/DSEngine/; export HOME

SHLIB_PATH=$SHLIB_PATH:$INFORMIXDIR/lib:$INFORMIXDIR/bin:$INFORMIXDIR/lib/esql:$INFORMIXDIR/etc:$INFORMIXDIR/lib/cli; export SHLIB_PATH

and .odbc.ini

Code: Select all


[ODBC Data Sources]
DB2 Wire Protocol=DataDirect 5.0 DB2 Wire Protocol Driver
dBase=DataDirect 5.0 dBaseFile(*.dbf)
Informix=DataDirect 5.0 Informix
Informix Wire Protocol=DataDirect 5.0 Informix Wire Protocol
Oracle=DataDirect 5.0 Oracle
Oracle Wire Protocol=DataDirect 5.0 Oracle Wire Protocol
SQLServer Wire Protocol=DataDirect 5.0 SQL Server Wire Protocol
Sybase Wire Protocol=DataDirect 5.0 Sybase Wire Protocol
Text=DataDirect 5.0 TextFile(*.*)

[DB2 Wire Protocol]
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMdb220.sl
Description=DataDirect 5.00 DB2 Wire Protocol Driver
AddStringToCreateTable=
AlternateID=
Collection=OS/390 and AS/400 (Remove for DB2 UDB)
Database=DB2 UDB (Remove for OS/390 and AS/400)
DynamicSections=100
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=DB2 server host
IsolationLevel=CURSOR_STABILITY
Location=OS/390 and AS/400 (Remove for DB2 UDB)
LogonID=
Password=
Package=DB2 package name
PackageOwner=
TcpPort=DB2 server port
WithHold=1

[DB2AS400]
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMdb220.sl
Description=DataDirect 5.00 DB2 Wire Protocol Driver
AddStringToCreateTable=
AlternateID=
Collection=
Database=
DynamicSections=100
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=ISERIES1
IsolationLevel=CURSOR_STABILITY
Location=
LogonID=#AWRMSTR
Password=qn48smg45i
Package=
PackageOwner=
TcpPort=446
WithHold=1

[dBase]
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMdbf20.sl
Description=DataDirect 5.00 dBaseFile(*.dbf)
ApplicationUsingThreads=1
CacheSize=4
CreateType=dBASE5
Database=/scratch/DataDirect_5.0/odbc/demo
IntlSort=0
Locking=RECORD
UseLongNames=0
UseLongQualifiers=
DataFileExtension=DBF
ExtensionCase=UPPER
FileOpenCache=0
LockCompatibility=dBASE

[Informix]
QEWSD=38638
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMinf20.sl
Description=DataDirect Informix
Database=dss
LogonID=dsadm
Password=dsadm1
ServerName=dbengine1_tcp
HostName=uwghp01
Service=dbengine1_tcp
Protocol=onsoctcp
EnableInsertCursors=0
GetDBListFromInformix=0
CursorBehavior=0
CancelDetectInterval=0
TrimBlankFromIndexName=1
ApplicationUsingThreads=1

[Informix_Template]
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMinf20.sl
Description=DataDirect Informix
Database=db
LogonID=uid
Password=pwd
ServerName=informixserver
HostName=informixhost
Service=online
Protocol=onsoctcp
EnableInsertCursors=0
GetDBListFromInformix=0
CursorBehavior=0
CancelDetectInterval=0
TrimBlankFromIndexName=1
ApplicationUsingThreads=1

[Informix Wire Protocol]
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMifcl20.sl
Description=DataDirect Informix Wire Protocol
Database=db
LogonID=uid
Password=pwd
HostName=informixhost
PortNumber=1500
ServerName=informixserver
CancelDetectInterval=0
TrimBlankFromIndexName=1
ApplicationUsingThreads=1

[Informix_Test]
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMifcl20.sl
Description=DataDirect Informix Wire Protocol
Database=dss
LogonID=dsadm
Password=dsadm1
HostName=uwghp01
PortNumber=1531
ServerName=dbengine1_tcp
CancelDetectInterval=0
TrimBlankFromIndexName=1
ApplicationUsingThreads=1

[Oracle]
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMor820.sl
Description=DataDirect 5.00 Oracle
ApplicationUsingThreads=1
ArraySize=60000
CatalogIncludesSynonyms=1
CatalogOptions=0
DefaultLongDataBuffLen=1024
DescribeAtPrepare=0
EnableDescribeParam=0
EnableNcharSupport=0
EnableScrollableCursors=1
EnableStaticCursorsForLongData=0
EnableTimestampWithTimeZone=0
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=
OptimizeLongPerformance=0
Password=
ProcedureRetResults=0
ServerName=Oracle host
UseCurrentSchema=1

[Oracle Wire Protocol]
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMora20.sl
Description=DataDirect Oracle Wire Protocol
ApplicationUsingThreads=1
ArraySize=60000
CachedCursorLimit=32
CachedDescLimit=0
CatalogIncludesSynonyms=1
CatalogOptions=0
DefaultLongDataBuffLen=1024
DescribeAtPrepare=0
EnableDescribeParam=0
EnableNcharSupport=0
EnableScrollableCursors=1
EnableStaticCursorsForLongData=0
EnableTimestampWithTimeZone=0
HostName=Oracle server
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=
Password=
PortNumber=Oracle server port
ProcedureRetResults=0
SID=Oracle SID
UseCurrentSchema=1

[SQLServer]
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMmsss20.sl
Description=DataDirect SQL Server Wire Protocol
Database=db
LogonID=uid
Password=pwd
Address=sqlserverhost,1433
QuotedId=No
AnsiNPW=No

[Sybase Wire Protocol]
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMase20.sl
Description=DataDirect Sybase Wire Protocol
ApplicationName=
ApplicationUsingThreads=1
ArraySize=50
Charset=
CursorCacheSize=1
Database=db
DefaultLongDataBuffLen=1024
EnableDescribeParam=0
EnableQuotedIdentifiers=0
InitializationString=
Language=
LogonID=
NetworkAddress=Sybase host,Sybase server port
OptimizePrepare=1
PacketSize=0
Password=
RaiseErrorPositionBehavior=0
SelectMethod=0
WorkStationID=

[Text]
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMtxt20.sl
Description=DataDirect 5.00 TextFile(*.*)
AllowUpdateAndDelete=0
ApplicationUsingThreads=1
CacheSize=4
CenturyBoundary=20
Database=/scratch/DataDirect_5.0/odbc/demo
DataFileExtension=TXT
DecimalSymbol=.
Delimiter=
FileOpenCache=0
FirstLineNames=0
IntlSort=0
ScanRows=25
TableType=Comma
UndefinedTable=GUESS

[ODBC]
IANAAppCodePage=4
InstallDir=/dss/Ascential/DataStage/branded_odbc
Trace=0
TraceDll=/dss/Ascential/DataStage/branded_odbc/lib/odbctrac.sl

TraceFile=odbctrace.out
UseCursorLib=0

Any suggestions on where I'm going wrong? Is there anything else that needs to be configured? Really appreciate your help.

Many thanks,
Whale.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 30, 2007 3:45 pm

"Transaction not available" is an alert message indicating that your Informix database is a NOLOGGING database - because there is no transaction logging then you can't do transactions. However, this should not prevent a SELECT from occurring. There is, I believe, an environment variable that can suppress generation of this message (but I can't recall what it is - perhaps you could search the forum).

The other thing is that Informix is particularly finicky about version level compatibility between database and ODBC driver. Again I don't have details readily to hand, but you probably need to involve the vendor/support provider.

I_Server_Whale · Post by **I_Server_Whale** » Mon Jul 30, 2007 3:53 pm

Many thanks, Ray.

Will contact support and will post the solution.

Whale.

I_Server_Whale · Post by **I_Server_Whale** » Thu Aug 02, 2007 2:48 pm

Ok. I installed a patch given by support and now it works fine. The ecase no is: 115461.

Whale.

ray.wurlod · Post by **ray.wurlod** » Thu Aug 02, 2007 3:09 pm

Do you happen to know what's in the patch? In particular is it a different version of the ODBC driver?

I_Server_Whale · Post by **I_Server_Whale** » Thu Aug 02, 2007 3:48 pm

Ray,

I don't think its a different version of the driver. Actually, the SHLIB_PATH variable was incorrect. And after that was fixed. The job runs only if the transaction level is "Auto Commit". And view data wasn't working.

So, with the patch, I'm able to view the data using ODBC stage. But it still runs only in the "Auto Commit" mode. It fails in the other modes.

This is a known issue and the related case number is 73761.

Whale.