ODBC Connection Issue

pradkumar · Post by **pradkumar** » Tue Apr 01, 2008 1:06 pm

Hi,

Before posting i have searched the forum by using the key word DBMSTYPE and found useful information regarding ODBC connection.From that i was able to set up the connection but still iam getting the error.

Iam trying to set up two database connections through ODBC. One is source SQLServer 2005 with name as Pacts and target is Informix at another location with name as BostonTest.

The folowing entries are added to the .odbc.ini under DSENGINE and uvodbc.config under project directory.

[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=E3TUWG
Database=ISERIES1
DynamicSections=200
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=10.1.11.13
IsolationLevel=CURSOR_STABILITY
Location=ISERIES1
LogonID=#AWRMSTR
Password=secret123
Package=
PackageOwner=
TcpPort=446
WithHold=1

[DB2OS390]
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMdb220.sl
Description=DataDirect 5.00 DB2 Wire Protocol Driver
AddStringToCreateTable=
AlternateID=
Collection=DB2T
DynamicSections=100
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=MAINTEST
IsolationLevel=CURSOR_STABILITY
Location=lit
LogonID=MSGLI9
Password=
Package=lic001
PackageOwner=
TcpPort=5000
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=10.1.11.11
Service=dbengine1_tcp
Protocol=onsoctcp
EnableInsertCursors=0
GetDBListFromInformix=0
CursorBehavior=0
CancelDetectInterval=0
TrimBlankFromIndexName=1
ApplicationUsingThreads=1

[InformixAGBoston]
QEWSD=38638
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMinf20.sl
Description=DataDirect Informix
Database=ag_data_whse
LogonID=jguoz
Password=unified9a
ServerName=boston
HostName=boston
Service=boston
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=/home/dsadm/Ascential/DataStage/branded_odbc/lib/VMifcl20.sl
Description=DataDirect Informix Wire Protocol
Database=dss
LogonID=dsadm
Password=dsadm1
HostName=10.1.11.11
PortNumber=1500
ServerName=dbengine1_tcp
CancelDetectInterval=0
TrimBlankFromIndexName=1
ApplicationUsingThreads=1

[Informix_Test]
QEWSD=39298
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMifcl20.sl
Description=DataDirect Informix Wire Protocol
Database=dss
LogonID=dsadm
Password=dsadm1
HostName=10.1.11.11
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

[Pacts]
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMmsss20.sl
Description=DataDirect SQL Server Wire Protocol
Database=pacts
LogonID=pactsuser
Password=pactsuser
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

[Bostontest]
QEWSD=39298
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMifcl20.sl
Description=DataDirect Informix Wire Protocol
Database=promotion
LogonID=testpro
Password=testpro9
ServerName=bonny
HostName=164.52.240.96
Service=1598
Protocol=onsoctcp
EnableInsertCursors=0
GetDBListFromInformix=0
CursorBehavior=0
CancelDetectInterval=0
TrimBlankFromIndexName=1
ApplicationUsingThreads=1

[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

uvodbc.config file

<Pacts>
DBMSTYPE = ODBC

<BostonTest>
DBMSTYPE = ODBC

And also i did stop and start of the datastage server.

But when i try to import the table definition iam getting the following error for BostonTest( Informix) as

DSR.MetaGeta(GET.TABLES)(SQLConnect('Bostontest','testpro')): BCI Error:
SQLSTATE=S1000,CODE=-25555,[DataStage][SQL Client][ODBC][DataDirect][ODBC Informix driver][Informix]Server bonny is not listed as a dbserver name in sqlhosts.

and for SQL Server 2005 ( Pacts ) as

DSR.MetaGeta(GET.TABLES)(SQLConnect('Pacts','pactsuser')): BCI Error:
SQLSTATE=08001,CODE=11,[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][libssclient20]General network error. Check your network documentation.
SQLSTATE=01000,CODE=11,[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][libssclient20]ConnectionOpen (sqlserverhost,1433()).)).

Could any one tell am i doing wrong or missing something.what is the exact procedure to set up database.

Thanks

kollurianu · Post by **kollurianu** » Tue Apr 01, 2008 2:53 pm

Try using a different driver (that is shipped by datastage) for Informix...

pradkumar · Post by **pradkumar** » Tue Apr 01, 2008 3:12 pm

Hi,

Thanks for your reply.I even tried with these two drivers shipped by datastage.

Informix=DataDirect 5.0 Informix
Informix Wire Protocol=DataDirect 5.0 Informix Wire Protocol

the informix database is of version 9.x ...

Thanks

kollurianu · Post by **kollurianu** » Tue Apr 01, 2008 3:20 pm

hey did you try the drivers available under

/dss/Ascential/DataStage/branded_odbc/lib folder for informix and sqlsever...

WDWolf · Post by **WDWolf** » Tue Apr 01, 2008 3:31 pm

We connect to a number of informix, try using the /branded_odbc/lib/VMifcl22.so driver, will work for version 9 and 10

pradkumar · Post by **pradkumar** » Tue Apr 01, 2008 3:42 pm

Hi i guess i am using the same driver you mentioned. FYI

[Bostontest]
QEWSD=39298
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMifcl20.sl
Description=DataDirect Informix Wire Protocol
Database=promotion
LogonID=testpro
Password=testpro9
ServerName=bonny
HostName=164.52.240.96
Service=1598
Protocol=onsoctcp
EnableInsertCursors=0
GetDBListFromInformix=0
CursorBehavior=0
CancelDetectInterval=0
TrimBlankFromIndexName=1
ApplicationUsingThreads=1

What can be the problem?
Please verify if I am missing out on any step while creating and configuring the DSN.
Is there any other way to test the database?

Thanks

WDWolf · Post by **WDWolf** » Tue Apr 01, 2008 4:11 pm

I see you have VMifcl20 I mentiond we are using VMifcl22. they are not the same.

WDWolf · Post by **WDWolf** » Tue Apr 01, 2008 4:17 pm

and you did make and entry in the project level uvodbc.config? also I believe you will find that you need a space following it, I place one before and after, some old bug.

<Bostontest>
DBMSTYPE = ODBC

Then try importing metadata, simplest test of connection I know

pradkumar · Post by **pradkumar** » Tue Apr 01, 2008 4:32 pm

Hi ,

Thanks for your response.I have provided space before and after. Ok i will ask my adminstrator to check for the INFORMIX driver. But what is the error in case of SQLSERVER 2005 too... for that also i added entries in odbc.ini and uvodnc.config ( Project level)

Thanks

WDWolf · Post by **WDWolf** » Tue Apr 01, 2008 4:50 pm

sqlserver 2005 requires new driver not in the orginal set. call support and should be available for download.
you have:
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMmsss20.sl
Address=sqlserverhost,1433
you will need:
Driver=/dss/Ascential/DataStage/branded_odbc/lib/VMmsss22.so
we also had to build an alais on the sqlserver side and specify the addess as follows:
Address=WINPSQL024304\SQL024304
port number did not seem to work anymore, but we did not spend much time trying to determine why.

pradkumar · Post by **pradkumar** » Tue Apr 01, 2008 5:01 pm

Hi William,

Really Thanks for your response. I will contact the support provider .

Thanks