| Author |
Message
|
| sunny_30 |
 Posted: Wed May 16, 2007 10:50 am Post subject: Update statement failure. |
 |
|
Master
Joined: 03 Oct 2005
Posts: 258
|
I m having a strange problem with the DataBase updation from the compute node ESQL.
Environment:
WMB 6.0.0.3
MQ 6.0.2.0
AIX
DataBase: Oracle 10g
I have an UPDATE statement in the ESQL which is sometimes not updating the DataBase. This occurs when I process multiple messages through the flow. i concluded this from the debugger & getting trace.
To avoid this, Im checking to see if the UPDATE made is a success, If it is not, Im Updating the DB again with the same data immediately in the following line. This way Im able to process any number of messages.
The UPDATE is successful atleast once in two tries.
I know this is a bad idea. Im not sure whats going wrong.
Will there be a limit to a maximum number of Oracle connections from the AIX broker. If so where do I check it?
What are the other factors that affect? If the UPDATE statement Im using is a complex one, does this happen? Do I need to simplify the query?
Do I need to increase JVM HeapSize in broker?
The stack & data size are made unlimited for the user-id accessing the broker, still the problem is not solved?
please guide me on what Im missing.
Thanks,
-Sunny |
|
| Back to top |
|
 |
| jbanoop |
| Posted: Wed May 16, 2007 11:38 am Post subject: |
|
|
Chevalier
Joined: 17 Sep 2005
Posts: 401
Location: SC
|
how exactly did you conclude from debbuger and trace that the statement was not executing ? were there any errors ?
if max number of connections have been reached you will receive an ORA error adn the flow will roll back . Is that what is happening or is it that the flow is completing successfully without the changes reflecting in the DB ? |
|
| Back to top |
|
|
| sunny_30 |
| Posted: Wed May 16, 2007 12:48 pm Post subject: |
|
|
Master
Joined: 03 Oct 2005
Posts: 258
|
I appreciate your reply.
My scenario is exactly the below way:
| Quote: |
Insert DB with value val1, val2.
Send req to an application
Get the reply from application, with val2. (No val1 information).
Update DB row, by adding val3 where val2 row is matched.
Immediately retrieve value val1 where val2 & val3 match in DB. |
When I process multiple messages through the flow and also coz Im retrieving immediately after Updation, Im sometimes getting a NULL that is stopping my processing. twice or thrice in every 50 messages.
I changed the above design to:
| Quote: |
Insert DB with value val1, val2.
Send req to an application
Get the reply from application, with val2. (No val1 information).
Update DB row, by adding val3 where val2 row is matched.
Immediately retrieve value val1 where val2 & val3 match in DB.
When the retrieved value is NULL, I do the Update the second time.
Retreive again. |
This way, Im not having any problems to process any # of messages.
But I understand this is a very bad design. When I trace my initial design, I cd clearly see that Im sometimes getting a NULL back on immediate retreival.
My question now is:
What is the reason for the Update failure. Am I not giving it enough time to store OR Is it the connections problem- Im not getting any exceptions in the flow, so I guess not. What can I do to avoid this?
However as Im on 6.0, I omitted the above retrieval process by using a shared variable to store the val1 and continue the processing after reply.
Im just doing the update & not having any problems.
But question still remains to know the cause of failure.
-Sunny.
Last edited by sunny_30 on Thu May 17, 2007 5:26 pm; edited 1 time in total |
|
| Back to top |
|
|
| jbanoop |
| Posted: Wed May 16, 2007 1:50 pm Post subject: |
|
|
Chevalier
Joined: 17 Sep 2005
Posts: 401
Location: SC
|
i am not quite sure I understand your requirement. Are you using 1 message flow or two message flows ..
| Quote: |
| Update DB row, by adding val3 where val2 row is matched |
If it is one message flow then most probably because the transaction mode is set to automatic the updates you are performing earlier in the flow are not commited yet because of which selects on that would return no rows..
also why would you need to
| Quote: |
| Immediately retrieve value val1 where val2 & val3 match in DB |
? In my understanding you already have all the information you require in he flow itself (assuming you are doing it in the same flow) |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed May 16, 2007 11:52 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi sunny_30,
It is not possible that the same query which works in most cases fails for certain UNLESS there is something wrong with that request message...
I would suggest you tick the 'Throw exception on database warnings' and remote the double update....Then take broker traces and catch the failure msgs...Try to find out why it fails....
Another suggestion would be to write all ur update data to a trace file in the flow, and once it has failed, go to that file and try to run the same update through the database command line....
Regards. |
|
| Back to top |
|
|
| kishoreraju |
| Posted: Thu May 17, 2007 1:00 pm Post subject: |
|
|
Disciple
Joined: 30 Sep 2004
Posts: 156
|
| is that whole transaction happeneds in a single flow.if you are doing it in a single flow can you try by commitng the transation before updating it. |
|
| Back to top |
|
|
| sunny_30 |
| Posted: Thu May 17, 2007 5:24 pm Post subject: |
|
|
Master
Joined: 03 Oct 2005
Posts: 258
|
Thanks a lot for all your suggestions.
Yes Im using just one flow to request & grab the replies using Aggregation. As you pointed out, the problem lies with commiting the initial insert made before making the later update in the same flow.
Without commiting, Im getting occassional failures in the reply section of the same flow. |
|
| Back to top |
|
|
| jbanoop |
| Posted: Thu May 17, 2007 5:36 pm Post subject: |
|
|
Chevalier
Joined: 17 Sep 2005
Posts: 401
Location: SC
|
| why would you need to select something again which you just used in the update ? you should have the information already within your message flow (say in the environment) .. or did i miss something ? |
|
| Back to top |
|
|
| sunny_30 |
| Posted: Thu May 17, 2007 6:12 pm Post subject: |
|
|
Master
Joined: 03 Oct 2005
Posts: 258
|
Hi Jbanoop,
Sorry for the confusion. I had problem framing my question.
The scenario is below way:
Insert rows with values (val1,val2) for each request in the AggregateRequest part of the flow.
For each reply I get in the AggregateReply part of the flow, I update the rows with val3 by matching val2.
Whatever rows I just updated, I need to retrieve all the val1 s to continue processing.
The Update I made is failing occassionally, coz the Insert made in the request part is not committed in the compute node, I m sometimes, retrieving NULL for val1. So, to avoid this part, I tried to update the same rows again(the second time) with the same info, which was minimising the number of failures (Null returns for val1).
Anyways now, after committing the compute nodes of the AggReq & AggRep all the DB interactions are made right away & Im not having any probs later.
Thanks for your help.
-Sunny |
|
| Back to top |
|
|
|
|
