| Author |
Message
|
| jkb |
 Posted: Thu Jan 20, 2011 4:46 am Post subject: Rollback because of DB timeout |
 |
|
Newbie
Joined: 12 Jun 2009
Posts: 7
|
Hi,
We have WMB 6105 running on win using a remote SQL Server as DB.
We see problems with timeouts as seen below.
We see that one message gets timeout and then 100 are processed successful and then one gets a timeout again.
Is there a setting were I can increase the wait time for all threads using the ODBC connection? Internal broker threads as well as threads used by message flows to access user data.
The DBA tries to minimies the timeouts but he can not garantee there will never come a timeout.
Rgds
Jakob
| Quote: |
DatabaseException
File: F:\build\S610_P\src\DataFlowEngine\ImbOdbc.cpp
Line: 453
Function: ImbOdbcHandle::checkRcInner
Type:
Name:
Label:
Catalog: BIPv610
Severity: 3
Number: 2322
Text: Child SQL exception
Insert
Type: 5
Text: HYT00
Insert
Type: 2
Text: 0
Insert
Type: 5
Text: [Microsoft][ODBC SQL Server Driver]Timeout expired
DatabaseException
File: F:\build\S610_P\src\DataFlowEngine\ImbOdbc.cpp
Line: 453
Function: ImbOdbcHandle::checkRcInner
Type:
Name:
Label:
Catalog: BIPv610
Severity: 3
Number: 2322
Text: Child SQL exception
Insert
Type: 5
Text: IM006
Insert
Type: 2
Text: 0
Insert
Type: 5
Text: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
|
|
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Thu Jan 20, 2011 6:26 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
SQL Server has excellent facilities that will help you solve this problem.
I've used them in the past to find a deadlock issue.
If you are using the remote DB for reads only, you can code the select statement via a passthru to do it readonly. ESQL Level SELECT won't let you do this.
_________________
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 |
|
|
| akshat.jain |
| Posted: Wed Feb 23, 2011 4:47 am Post subject: A simmilar problem using passthrough and insert |
|
|
Novice
Joined: 02 Feb 2010
Posts: 13
|
Hi i m using the Passthrough function in a FOR LOOP with Insert Statement.
This data is added to the DB2 database.
The problem i m facing is that after a few thousand inserts the database locks and no further insertion takes place for that particular thread and it starts to roll back.A lot of data is being lost.
Also we cannot decrease the data size, it directly keeps on reading from an XML file and insert it into the table.
A strange this is also happening, it sometimes auto commit some where in the middle, why is this happening ?
How can i resolve this lock issue ? |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Feb 23, 2011 5:40 am Post subject: Re: A simmilar problem using passthrough and insert |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| akshat.jain wrote: |
| A strange this is also happening, it sometimes auto commit some where in the middle, why is this happening ? |
Because DB2 tries to be helpful.
| akshat.jain wrote: |
| How can i resolve this lock issue ? |
Speak to your DB2 DBA if you can't commit in the middle of the loop for integrity reasons.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| akshat.jain |
| Posted: Wed Feb 23, 2011 5:51 am Post subject: |
|
|
Novice
Joined: 02 Feb 2010
Posts: 13
|
- A strange this is also happening, it sometimes auto commit some where in the middle, why is this happening ?
- Because DB2 tries to be helpful.
No seriously i want to know shouldnt nothing be inserted as the entire transaction is not being completed, it has falied somewhere in the middle of the for loop, Commit usually happens when the the transaction is complete ?
- How can i resolve this lock issue ?
- Speak to your DB2 DBA if you can't commit in the middle of the loop for integrity reasons.
I acctually want to know what is the reason of this lock ? Its only 1000 Records, but yeah also it is a 3rd party DB2 database, and want to know if it could be resolved on broker level. Also this is a frequest matter, the flow runs like every hr and this happens like 4 - 5 times a day. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Feb 23, 2011 6:19 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| akshat.jain wrote: |
| Commit usually happens when the the transaction is complete ? |
But when the transaction is complete is decided by you explicitly or implicitly ending the transaction, not by some weird magic.
| akshat.jain wrote: |
| i want to know shouldnt nothing be inserted as the entire transaction is not being completed |
Nothing is being inserted - you said earlier that:
| akshat.jain wrote: |
| it starts to roll back |
Which all of your insertts effectively never happened to DB2. So nothing has been inserted.
If by "data is being lost" you mean that it's not in the database and the XML is consumed, then that's a bug in your flow. You should code for database problems and handle them. Why are you using passthru rather than any of the WMB methods of dealing with databases?
| akshat.jain wrote: |
| I acctually want to know what is the reason of this lock ? Its only 1000 Records, but yeah also it is a 3rd party DB2 database, and want to know if it could be resolved on broker level. |
The only way to find out why the database locked is to ask the DB2 what the problem is. I'd be interested to know how you've arrived at the conclusion it's a lock; if you actually had a SQL code then why doesn't your flow react appropriately to the error?
It could be resolved by a code change in broker - I've already suggested intermediate commits but that relies on the business use for this data allowing for that and it being a problem with the number of uncommitted transactions. If it's a true deadlock you can't fix that in code.
Likewise better error handleing your end to catch the XML & represent it to the database when it's in a better mood could help.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
