| Author |
Message
|
| GeneRK4 |
 Posted: Mon Feb 17, 2014 2:43 am Post subject: Database intermittent connectivity failures- SQL State '27'; |
 |
|
Master
Joined: 08 Jul 2013
Posts: 220
|
I am getting the below error in my logs for most of the times everyday.There are some intermittent Database connectivity failures.
I am not able to get the reason for these failures .
Feb 17 09:31:52 mbphub01 WebSphere Broker v8002[3857]: (MBPHUB01.XG_NEXPHARM_2301)[12]BIP2322E: Database error: SQL State '27'; Native Error Code '-3114'; Error Text '[unixODBC][IBM][ODBC 20101 driver][Oracle]11605'. : MBPHUB01.7f6d2e0a-4401-0000-0080-9de1b2196161: /build/slot1/S800_P/src/DataFlowEngine/ImbOdbc.cpp: 2437: ImbOdbcConnection::checkRcInner: :
Also,I want to highlight the results I am getting by executing the below command,
ComIbmDatabaseConnectionManager
uuid='ComIbmDatabaseConnectionManager'
userTraceLevel='none'
traceLevel='none'
userTraceFilter='none'
traceFilter='none'
statementCacheSize='40'
maxStatementAge='600'
maxConnectionAge='60'
DatabaseContext
threadNumber='4'
ODBC_Version_3
envHandle='139736425474272'
DatabaseContext
threadNumber='7'
ODBC_Version_3
envHandle='139733942449824'
DataSource
name='ORACLEDB'
timeoutEnabled='false'
coordinated='false'
state='2'
ManagedStatement
text='{ CALL IBSA.USP_SP_PROCESSMESSAGE ( ? , ? , ? ) }'
createTime='1392631942.866456000'
lastExecuteTime='1392631942.896496000'
DatabaseContext
threadNumber='8'
ODBC_Version_3
envHandle='139734412207936'
DataSource
name='ORACLEDB'
timeoutEnabled='false'
coordinated='false'
state='2'
ManagedStatement
text='{ CALL IBSA.USP_SP_PROCESSMESSAGE ( ? , ? , ? ) }'
createTime='1392631727.406501000'
lastExecuteTime='1392631730.204752000'
This DatabaseContext repeated till ThreadNumber:39.
From Infocenter ,I could understand that the ODBC connections in pool are managed internally by broker.
At Database side,they can accept maximum 2500 connections and the connection close set is 20 minutes at their ORACLE DB end.
I am opening only 50 connections maximum to ORACLE DB concurrently.
I am not sure where is this intermittent connectivity failure arising from.. |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Mon Feb 17, 2014 7:46 am Post subject: Re: Database intermittent connectivity failures- SQL State ' |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| GeneRK4 wrote: |
From Infocenter ,I could understand that the ODBC connections in pool are managed internally by broker.
At Database side,they can accept maximum 2500 connections and the connection close set is 20 minutes at their ORACLE DB end.
I am opening only 50 connections maximum to ORACLE DB concurrently.
I am not sure where is this intermittent connectivity failure arising from.. |
How do you know you are only opening 50 concurrent connections? Can you give proof of that? Preferably a DB measure... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Avinci |
| Posted: Sun Mar 02, 2014 2:40 am Post subject: Re: Database intermittent connectivity failures- SQL State ' |
|
|
Newbie
Joined: 02 Mar 2014
Posts: 2
|
Any solution on this yet?
I got the same error in live. The insert thread got locked in the database and was not released for 60 minutes, after 60 minutes I observed below error in user.log. Being single threaded, next insert was stuck for 12 hours in DB table before DBA had to kill a stale session created by first insert above;
then everything started working fine. Note only one table had this issue others were working fine i.e. inserts from other message flows.
Does someone know what this default thread/connection 60 minutes timeout is? The maxConnectionAge is just 60 sec so its not that. Moreover inbound is MQ Q and there was pile-up hence broker will not assume message flow to be idle & timeout at 60 sec.
Feb 21 11:15:30 app02 WebSphere Broker v8001[1658]: (APP02_PRI_EG)[6]BIP2628E: Exception condition detected on input node 'com.APP02.DB_INSERT.MQ Input'. : APP02.BK.COM.59db1aea-4301-0000-0080-e44eeb28e99e: /build/slot1/S800_P/src/DataFlowEngine/ImbCommonInputNode.cpp: 1970: ImbCommonInputNode::run: ComIbmMQInputNode: com/APP02/DB_INSERT_ACTIVITY_F1#FCMComposite_1_1
Feb 21 11:15:30 app02 WebSphere Broker v8001[1658]: (APP02_PRI_EG)[6]BIP2321E: Database error: ODBC return code '-1' using ODBC driver manager '/opt/ibm/IE02/2.0.0/lib/libodbcinterface.so'. : APP02.BK.COM.59db1aea-4301-0000-0080-e44eeb28e99e: /build/slot1/S800_P/src/DataFlowEngine/ImbOdbc.cpp: 2250: ImbOdbcConnection::checkRcInner: :
Feb 21 11:15:30 app02 WebSphere Broker v8001[1658]: (APP02_PRI_EG)[6]BIP2322E: Database error: SQL State '27'; Native Error Code '-3114'; Error Text '[unixODBC][IBM][ODBC 20101 driver][Oracle]11605'. : APP02.BK.COM.59db1aea-4301-0000-0080-e44eeb28e99e: /build/slot1/S800_P/src/DataFlowEngine/ImbOdbc.cpp: 2428: ImbOdbcConnection::checkRcInner: :
Feb 21 11:15:30 app02 WebSphere Broker v8001[1658]: (APP02_PRI_EG)[6]BIP2628E: Exception condition detected on input node 'com.APP02.DB_INSERT.MQ Input'. : APP02.BK.COM.59db1aea-4301-0000-0080-e44eeb28e99e: /build/slot1/S800_P/src/DataFlowEngine/ImbLocalTransactionManager.cpp: 204: ImbLocalTransactionManager::rollback: :
Feb 21 11:15:30 app02 WebSphere Broker v8001[1658]: (APP02_PRI_EG)[6]BIP2321E: Database error: ODBC return code '-1' using ODBC driver manager '/opt/ibm/IE02/2.0.0/lib/libodbcinterface.so'. : APP02.BK.COM.59db1aea-4301-0000-0080-e44eeb28e99e: /build/slot1/S800_P/src/DataFlowEngine/ImbOdbc.cpp: 2250: ImbOdbcConnection::checkRcInner: :
Feb 21 11:15:30 app02 WebSphere Broker v8001[1658]: (APP02_PRI_EG)[6]BIP2322E: Database error: SQL State '27'; Native Error Code '-3114'; Error Text '[unixODBC][IBM][ODBC 20101 driver][Oracle]11605'. : APP02.BK.COM.59db1aea-4301-0000-0080-e44eeb28e99e: /build/slot1/S800_P/src/DataFlowEngine/ImbOdbc.cpp: 2428: ImbOdbcConnection::checkRcInner: :
Any quick direction will be great. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sun Mar 02, 2014 3:41 am Post subject: Re: Database intermittent connectivity failures- SQL State ' |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| Avinci wrote: |
I got the same error in live. |
Raise a Sev 1 PMR
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Avinci |
| Posted: Sun Mar 02, 2014 4:37 am Post subject: Re: Database intermittent connectivity failures- SQL State ' |
|
|
Newbie
Joined: 02 Mar 2014
Posts: 2
|
Thanks. Yeh, raised with IBM. DBA's are trying to find out why first insert got stuck as we already made 6-7 million succesful inserts on that table that day. This is the question IBM anyways like to know.
One more information I forgot to mention, we have other 7 unix servers and separate broker on each & they are inserting into the same table/DB. After first thread in first server was stuck i.e. before the 60 minute timeout, all other servers got stuck for 12 hrs before the first stale session was killed. Looks the table got locked and no operation was possible on that and it was holding on to new connections from other servers too, weird 
I read some article stating network glitch can cause the connection to terminate at broker end and leave a stale session on DB.
Looks like an XA issue & no proper clean-up happened.
Will update once I have useful info. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Sun Mar 02, 2014 7:25 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
you might also want to consider a slightly different approach:
Set your output to queue (not DB) and have a J2EE server process via MDB from queue to DB.
This allows you to scale the DB operations independently from scaling the flow.
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Sun Mar 02, 2014 9:08 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| fjb_saper wrote: |
you might also want to consider a slightly different approach:
Set your output to queue (not DB) and have a J2EE server process via MDB from queue to DB.
This allows you to scale the DB operations independently from scaling the flow. |
But using the flow allows you to scale your DB operations independently from scaling anything running in some app server. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Sun Mar 02, 2014 8:35 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| mqjeff wrote: |
| fjb_saper wrote: |
you might also want to consider a slightly different approach:
Set your output to queue (not DB) and have a J2EE server process via MDB from queue to DB.
This allows you to scale the DB operations independently from scaling the flow. |
But using the flow allows you to scale your DB operations independently from scaling anything running in some app server. |
What about DB connection pool management, purge policies etc??
I would not advocate this as a general solution, but more as a work around especially when you are running into connection stability problems like the OP seems to be experiencing...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| GeneRK4 |
| Posted: Sun Mar 02, 2014 9:09 pm Post subject: |
|
|
Master
Joined: 08 Jul 2013
Posts: 220
|
We have completely solved this issue by below ways..
1)sqlstate=27 native error =-3114 does not provide much insight into the actual error.Hence we found that odbc.ini file does not map correct Installdir path.We first fixed that.
2)Then proper logs were thrown as the Database connection is unable to access the StoredProcedure.We found that this is due to some configurations tuning missing at Database end.Hence,DBA tuned idle_timeout and wait_timeout
3)We didnt tune maxconnectionAge and it is left as default 60s.This is going to close only the thread which has returned to pool and idle for 60s.
4)Database was trying to close the active connection and hence abend threads were creating.We adviced DBA not to configure the parameter which closed active thread.He thought of cleaning up the stale connection which caused production message loss, due to current flow design(main flow connecting to Database).
5)We redesigned the flow in such a way that the main flow will put the message in Queue.And the second flow will connect to Database and inserts data. |
|
| Back to top |
|
|
|
|
