ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker Supportintermittent Connection dead issues

Post new topicReply to topic
intermittent Connection dead issues View previous topic :: View next topic
Author Message
akill
PostPosted: Mon Dec 02, 2019 2:29 am Post subject: intermittent Connection dead issues Reply with quote

Novice

Joined: 26 Jun 2017
Posts: 16

Hi all,

We are getting intermittent Connection dead issues while connecting to oracle DB.

Below are the inputs from concern teams:

Notwork Team : Firewall terminates the idle connection for every 1 hr.And they are not recommending to increase the this value.
DB Team : They don't terminate any connections

Below are my flows:
1."HTTPIn-->Compute(DB Calls)-->HTTPReply-->MQOutput"
2.MQInput-->Compute(DB Calls)--HTTPRequest-->Compute(DB calls)

Message to the second flow will come from first flow using MQ Nodes.and i am interacting withe same DB in both the flows.


In my case , If we leave the flow idle and hit after 60 mins , the first hit coming after 60 mins is failing(The very first DB call in the second flow is getting failed with connection dead . and it is taking ~15 mins to give that error). Below is the Log.

Quote:
2019-11-25 12:21:16.939078 4707 UserTrace BIP12080I: Preparing a database statement for ''DSN''.
2019-11-25 12:21:16.939432 4707 UserTrace BIP12081I: Prepared a database statement for ''DSN''.
2019-11-25 12:36:44.994948 4707 UserTrace BIP12074I: Executing a database statement for ''DSN''.
2019-11-25 12:36:44.996716 4707 UserTrace BIP12077W: Failed to execute a database statement for ''DSN''.
2019-11-25 12:36:45.000900 4707 UserTrace BIP2231E: Error detected whilst processing a message in node 'com.integrations.OPENCUST.CREATE'.
2019-11-25 12:36:45.000936 4707 RecoverableException BIP2230E: Error detected whilst processing a message in node 'com.integrations.OPENCUST.IdentifyCustomer'.
2019-11-25 12:36:45.000952 4707 RecoverableException BIP2488E: ('com.integrations.IdentifyCustomer_Compute.Main', '12.4') Error detected whilst executing the SQL statement ''PROPAGATE TO TERMINAL 'out1' FINALIZE DEFAULT DELETE DEFAULT;''.
2019-11-25 12:36:45.000964 4707 RecoverableException BIP2230E: Error detected whilst processing a message in node 'com.integrations.OPENCUST.RequestMapping'.
2019-11-25 12:36:45.000992 4707 RecoverableException BIP2488E: ('com.integrations.IdentifyCustomer_Compute.Main', '6.3') Error detected whilst executing the SQL statement ''UPDATE Database.DETAILS AS AOCD SET YN = 'I' WHERE AOCD.DETAILS.AOCD_ID = Environment.variable.REFNO AND AOCD.DETAILS.CUST_PRIORITY = Environment.variable.custPriority;''.
2019-11-25 12:36:45.001012 4707 DatabaseException BIP2321E: Database error: ODBC return code '-1' using ODBC driver manager ''/opt/IBM/iib-10.0.0.7/ie02/lib/libodbcinterface.so''.
2019-11-25 12:36:45.001024 4707 DatabaseException BIP2322E: Database error: SQL State ''08S01''; Native Error Code '-4'; Error Text ''[IBM][ODBC Oracle Wire Protocol driver][Oracle]Connection Dead.''.



Have two questions:
1.What causing the issue
2.how come we are getting connection issue in the second flow why not in the first flow as we are interacting the same DB.
3.Why is it taking 15 mins to throw the error ?

FYI we're using IIB 10.0.0.7.

Thanks in advance.
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Mon Dec 02, 2019 3:34 am Post subject: Re: intermittent Connection dead issues Reply with quote

Partisan

Joined: 17 Jul 2017
Posts: 331
Location: UK

hi...

akill wrote:

Have two questions:
1.What causing the issue

As your network team suggested it looks like some sort of firewall/OS level setting on the Database server to kill off idle connections older than 60 minutes. By default Broker will release the idle connections after a minute, investgate why it is still keeping the connection open even after an hour....may be it is customized per integration node/server at your side,

https://www.ibm.com/support/knowledgecenter/SSMKHH_10.0.0/com.ibm.etools.mft.doc/ac00406_.htm

akill wrote:

2.how come we are getting connection issue in the second flow why not in the first flow as we are interacting the same DB.

Are both flows running on the same integration server? if not the integration servers might be using different maxConnectionAge settings

akill wrote:

3.Why is it taking 15 mins to throw the error ?

this might be to do with how the DSN is configured, check the odbc stanza for this DSN to see whether it has got any retry attempts configured (eg:- retry after a minute & 15 attempts which means it will return the control back to the flow only after 15 minutes)
Back to top
View user's profile Send private message
akill
PostPosted: Mon Dec 02, 2019 4:04 am Post subject: intermittent Connection dead issues Reply with quote

Novice

Joined: 26 Jun 2017
Posts: 16

Hi,

Thanks for the quick response.

Quote:
As your network team suggested it looks like some sort of firewall/OS level setting on the Database server to kill off idle connections older than 60 minutes. By default Broker will release the idle connections after a minute, investgate why it is still keeping the connection open even after an hour....may be it is customized per integration node/server at your side,

https://www.ibm.com/support/knowledgecenter/SSMKHH_10.0.0/com.ibm.etools.mft.doc/ac00406_.htm


Have already gone through the above documentation and checked my Integration server configurations for maxconnectionage , the default value has been configured which is 60 secs . Also i haven't even enabled connect before flow starts option on the compute node.Below is some part of the reportproperties result.

ComIbmDatabaseConnectionManager
uuid='ComIbmDatabaseConnectionManager'
userTraceLevel='none'
traceLevel='none'
userTraceFilter='debugTrace'
traceFilter='none'
statementCacheSize='40'
maxStatementAge='600'
maxConnectionAge='60'
useDefaultSchemaForStoredProcedures='true'
enableODBCTraceBridge='true'

Quote:
Are both flows running on the same integration server? if not the integration servers might be using different maxConnectionAge settings


Both the message flows belong to the same application and obviously deployed on same Integration server

Quote:
this might be to do with how the DSN is configured, check the odbc stanza for this DSN to see whether it has got any retry attempts configured (eg:- retry after a minute & 15 attempts which means it will return the control back to the flow only after 15 minutes)


Below is my stanza (Ignore server details) and there is no such configuration to retry for 15 mins.

[DSN]

Driver=/opt/IBM/iib-10.0.0.7/server/ODBC/drivers/lib/UKora95.so
Description=DataDirect 7.0 ODBC Oracle Wire Protocol
HostName=00.00.00.00
PortNumber=0000
ServiceName=SERV
CatalogOptions=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1
LoginTimeout=0
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Mon Dec 02, 2019 7:06 am Post subject: Reply with quote

Partisan

Joined: 17 Jul 2017
Posts: 331
Location: UK

hi...
It is worth reviewing whether the queries used in both flows are not resulting in some sort of 'waiting for table lock' scenarios. Also it might be worth adding 'queryTimeout' to say 60 seconds or so at your odbc stanzas.

https://knowledgebase.progress.com/articles/Article/000029398

The exact 15 minute delay when retry is not configured is bit strange, you could try enabling odbc trace and see what it is waiting on
Back to top
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker Supportintermittent Connection dead issues
Jump to:



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.