| Author |
Message
|
| aruncg |
 Posted: Mon Feb 24, 2014 8:16 am Post subject: WMB V8.0.0.2 - [Oracle] Connection dead error - urgent |
 |
|
Novice
Joined: 08 Mar 2013
Posts: 22
|
Hi,
Our WMB version has recently been updated from version V8.0.0.1 to V8.0.0.2. The message flows were also modified based on the project requirement. Now we are facing an issue - we are getting [Oracle] Connection Dead error if the flow is idle for some time.
In windows development workstations this happens for the first message after the broker being idle for some time (around half an hour). When i debug it gives proper error message and from next message onwards it starts working. But in Unix test env (where we have configured multiple instances of the flow) , the broker seem to get hung around the DB call - and I have to restart the broker each time to resume the processing. The DB connection is made from subflows. Can you please help to get a workaround/solution for this issue? We are connecting to Oracle (11g) DB using DataDirect Oracle WireProtocol (64 bit). |
|
| Back to top |
|
 |
| Tibor |
| Posted: Tue Feb 25, 2014 12:53 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
I would recommend switching ODBC tracing on and analyzing the result.
Just a question: did you check network connections when the error occured, e.g. with netstat? |
|
| Back to top |
|
|
| aruncg |
| Posted: Tue Feb 25, 2014 3:17 am Post subject: |
|
|
Novice
Joined: 08 Mar 2013
Posts: 22
|
we had enabled odbc trace and the following error was there in the ODBC trace
Exit:[SQL_ERROR]
DIAG [08S01] [IBM][ODBC Oracle Wire Protocol driver'[Oracle]Connection Dead.
DIAG [HY000] [IBM][ODBC Oracle Wire Protocol driver'[Oracle]Network Operation Timed Out.
But the network was working fine - I even tried mqsicvp and there was no error. What i thought is even if the connection is dead, driver will handle it internally and will try to reconnect.
Also in unix the broker was not picking any messages after this issue - i had to manually restart the broker. |
|
| Back to top |
|
|
| Tibor |
| Posted: Tue Feb 25, 2014 4:08 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
|
| Back to top |
|
|
| aruncg |
| Posted: Wed Feb 26, 2014 2:45 am Post subject: |
|
|
Novice
Joined: 08 Mar 2013
Posts: 22
|
I am not getting any clue on why this is happening.I have added parameter "WorkArounds=536870912" in the odbc file , but still am facing some issue - but now I am getting [unixODBC][ibm][ODBC Oracle wire protocol driver][Oracle]Network operationTimedout error for the first message and that too is happening after some time (i believe driver/broker is waiting till default timeout before throwing this error).
And i got this link from internet http://knowledgebase.progress.com/articles/Article/000029398 which doesnt help much..... |
|
| Back to top |
|
|
| Tibor |
| Posted: Wed Feb 26, 2014 3:36 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
|
| Back to top |
|
|
| aruncg |
| Posted: Thu Feb 27, 2014 1:21 am Post subject: |
|
|
Novice
Joined: 08 Mar 2013
Posts: 22
|
firewall in b/w the WMB server and the Oracle server removing any idle connection - In this scenario my assumption was that WMB or the driver will retry and establish a new connection and this operation will be transparent to the application - Is my understanding correct?
And how to contact the DataDirect Driver team - do we have any forums for this ?
THis issue is killing me as I need to provide the solution now.... 
Note 1 : the odbcv7.ini sample file is not packed in WMB V8.0.0.2 and is available in V8.0.0.3 as a fix. |
|
| Back to top |
|
|
| aruncg |
| Posted: Thu Feb 27, 2014 1:54 am Post subject: |
|
|
Novice
Joined: 08 Mar 2013
Posts: 22
|
| Quote: |
| firewall in b/w the WMB server and the Oracle server removing any idle connection - |
Please note that this statement in previous post is my assumption. |
|
| Back to top |
|
|
| Tibor |
| Posted: Thu Feb 27, 2014 2:01 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
| aruncg wrote: |
| And how to contact the DataDirect Driver team - do we have any forums for this ? |
You can address the IBM / WMB support team with your problem, because this ODBC driver is a supported component. |
|
| Back to top |
|
|
| ghoshly |
| Posted: Tue Mar 04, 2014 3:23 am Post subject: What to check using netstat |
|
|
Partisan
Joined: 10 Jan 2008
Posts: 325
|
Hi Tibor,
Would you please guide in detail, what parameters to check from netstat command in situation of Database exception?
We are working with WMB 8.0.0.2 and upgraded to use V7 driver instead of default V6 ODBC driver. We are facing the mentioned below error occasionally without any other proper hint.
| Quote: |
| Caught exception and rethrowing ||Root SQL exception||/opt/ibm/IE02/2.0.0/lib/libodbcinterface.so ||Child SQL exception||[unixODBC][IBM][ODBC 20101 driver][20101]ORA-01017: invalid username/password; logon denied |
We need to manually run the mqsisetdbparms command again with the same user id and password and restart the broker to resume operation. |
|
| Back to top |
|
|
| Tibor |
| Posted: Tue Mar 04, 2014 4:10 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
ghoshly,
Could you post the result of command mqsicvp BRKNAME -n ODBCNAME ? |
|
| Back to top |
|
|
| aruncg |
| Posted: Tue Mar 04, 2014 4:14 am Post subject: |
|
|
Novice
Joined: 08 Mar 2013
Posts: 22
|
@ ghoshly,
If we are updating the driver, then i don't think there is any need to setup DB params again - it is for a particular broker that we set the DB params (unless some has changed the password or is using wrong DSN).
Note : Actually I am facing a totally different issue. |
|
| Back to top |
|
|
| ghoshly |
| Posted: Tue Mar 04, 2014 6:12 am Post subject: Response |
|
|
Partisan
Joined: 10 Jan 2008
Posts: 325
|
I understand it can be different issue. I am not saying that it is definitely causing because of driver update. Currently we reverted back to V6 driver to check and confirm. We need to identify the reason behind the issue.
| Quote: |
tghosh@sdxbf-m23n6$ netstat -a | grep 1551
tghosh@sdxbf-m23n6$ mqsicvp MBICE01 -n ICEDB
BIP8270I: Connected to Datasource 'ICEDB' as user 'ESB_ICE_CONNECT'. The datasource platform is 'Oracle', version '11.02.0000 Oracle 11.2.0.3.0'.
===========================
databaseProviderVersion = 11.02.0000 Oracle 11.2.0.3.0
driverVersion = 06.00.0277 (B0199, U0092)
driverOdbcVersion = 03.52
driverManagerVersion = 03.52.0002.0002
driverManagerOdbcVersion = 03.52
databaseProviderName = Oracle
datasourceServerName = sdxbf-orastd2.na.sdxcorp.net
databaseName = N/A
odbcDatasourceName = ICEDB
driverName = UKora24.so
supportsStoredProcedures = Yes
procedureTerm = 20105
accessibleTables = Yes
accessibleProcedures = Yes
identifierQuote = "
specialCharacters = None
describeParameter = Yes
schemaTerm = 20102
tableTerm = 20106
sqlSubqueries = 31
activeEnvironments = 0
maxDriverConnections = 0
maxCatalogNameLength = 128
maxColumnNameLength = 30
maxSchemaNameLength = 30
maxStatementLength = 0
maxTableNameLength = 30
supportsDecimalType = Yes
supportsDateType = No
supportsTimeType = No
supportsTimeStampType = No
supportsIntervalType = No
supportsAbsFunction = Yes
supportsAcosFunction = No
supportsAsinFunction = No
supportsAtanFunction = No
supportsAtan2Function = No
supportsCeilingFunction = Yes
supportsCosFunction = Yes
supportsCotFunction = No
supportsDegreesFunction = No
supportsExpFunction = Yes
supportsFloorFunction = Yes
supportsLogFunction = Yes
supportsLog10Function = Yes
supportsModFunction = Yes
supportsPiFunction = No
supportsPowerFunction = Yes
supportsRadiansFunction = No
supportsRandFunction = No
supportsRoundFunction = Yes
supportsSignFunction = Yes
supportsSinFunction = Yes
supportsSqrtFunction = Yes
supportsTanFunction = Yes
supportsTruncateFunction = Yes
supportsConcatFunction = Yes
supportsInsertFunction = Yes
supportsLcaseFunction = Yes
supportsLeftFunction = Yes
supportsLengthFunction = Yes
supportsLTrimFunction = Yes
supportsPositionFunction = No
supportsRepeatFunction = Yes
supportsReplaceFunction = Yes
supportsRightFunction = Yes
supportsRTrimFunction = Yes
supportsSpaceFunction = Yes
supportsSubstringFunction = Yes
supportsUcaseFunction = Yes
supportsExtractFunction = No
supportsCaseExpression = No
supportsCastFunction = No
supportsCoalesceFunction = No
supportsNullIfFunction = No
supportsConvertFunction = Yes
supportsSumFunction = Yes
supportsMaxFunction = Yes
supportsMinFunction = Yes
supportsCountFunction = Yes
supportsBetweenPredicate = Yes
supportsExistsPredicate = Yes
supportsInPredicate = Yes
supportsLikePredicate = Yes
supportsNullPredicate = Yes
supportsNotNullPredicate = Yes
supportsLikeEscapeClause = Yes
supportsClobType = No
supportsBlobType = No
charDatatypeName = CHAR
varCharDatatypeName = VARCHAR2
longVarCharDatatypeName = CLOB
clobDatatypeName = N/A
timeStampDatatypeName = N/A
binaryDatatypeName = RAW
varBinaryDatatypeName = RAW
longVarBinaryDatatypeName = BLOB
blobDatatypeName = N/A
intDatatypeName = NUMBER
doubleDatatypeName = BINARY_DOUBLE
varCharMaxLength = 0
longVarCharMaxLength = 0
clobMaxLength = 0
varBinaryMaxLength = 0
longVarBinaryMaxLength = 0
blobMaxLength = 0
timeStampMaxLength = 0
identifierCase = Upper
escapeCharacter = \
longVarCharDatatype = -1
clobDatatype = 0
longVarBinaryDatatype = -4
blobDatatype = 0
BIP8273I: The following datatypes and functions are not natively supported by datasource 'ICEDB' using this ODBC driver: Unsupported datatypes: 'DATE, TIME, TIMESTAMP, INTERVAL, CLOB, BLOB' Unsupported functions: 'ACOS, ASIN, ATAN, ATAN2, COT, DEGREES, PI, RADIANS, RAND, POSITION, EXTRACT, CASE, CAST, COALESCE, NULLIF'
Examine the specific datatypes and functions not supported natively by this datasource using this ODBC driver.
When using these datatypes and functions within ESQL, the associated data processing is done within WebSphere Message Broker rather than being processed by the database provider.
Note that "functions" within this message can refer to functions or predicates.
BIP8071I: Successful command completion.
tghosh@sdxbf-m23n6$
|
|
|
| Back to top |
|
|
| ghoshly |
| Posted: Tue Mar 04, 2014 6:20 am Post subject: No change in password. |
|
|
Partisan
Joined: 10 Jan 2008
Posts: 325
|
| I just want to confirm, that in my case, there is no change in oracle user / password in recent times and we are not facing the issue all the time, rather ocassionally. Once we get the error, we need to run mqsisetdbparms again. |
|
| Back to top |
|
|
| Tibor |
| Posted: Wed Mar 05, 2014 1:00 am Post subject: Re: What to check using netstat |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
| ghoshly wrote: |
| Caught exception and rethrowing ||Root SQL exception||/opt/ibm/IE02/2.0.0/lib/libodbcinterface.so ||Child SQL exception||[unixODBC][IBM][ODBC 20101 driver][20101]ORA-01017: invalid username/password; logon denied |
If it happens only accidentally, seems very strange for me. There should be an error message in the database logs at the same time. I'm sure you can find an explanation for this behavior. |
|
| Back to top |
|
|
|
|
