| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| intermittent Connection dead issues |
« View previous topic :: View next topic » |
| Author |
Message
|
| akill |
 Posted: Mon Dec 02, 2019 2:29 am Post subject: intermittent Connection dead issues |
 |
|
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 |
|
 |
| abhi_thri |
| Posted: Mon Dec 02, 2019 3:34 am Post subject: Re: intermittent Connection dead issues |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
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 |
|
|
| akill |
| Posted: Mon Dec 02, 2019 4:04 am Post subject: intermittent Connection dead issues |
|
|
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 |
|
|
| abhi_thri |
| Posted: Mon Dec 02, 2019 7:06 am Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
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 |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
