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 Index » WebSphere Message Broker (ACE) Support » Update statement failure.

Post new topic  Reply to topic
 Update statement failure. « View previous topic :: View next topic » 
Author Message
sunny_30
PostPosted: Wed May 16, 2007 10:50 am    Post subject: Update statement failure. Reply with quote

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
View user's profile Send private message
jbanoop
PostPosted: Wed May 16, 2007 11:38 am    Post subject: Reply with quote

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
View user's profile Send private message Yahoo Messenger
sunny_30
PostPosted: Wed May 16, 2007 12:48 pm    Post subject: Reply with quote

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
View user's profile Send private message
jbanoop
PostPosted: Wed May 16, 2007 1:50 pm    Post subject: Reply with quote

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
View user's profile Send private message Yahoo Messenger
elvis_gn
PostPosted: Wed May 16, 2007 11:52 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
kishoreraju
PostPosted: Thu May 17, 2007 1:00 pm    Post subject: Reply with quote

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
View user's profile Send private message
sunny_30
PostPosted: Thu May 17, 2007 5:24 pm    Post subject: Reply with quote

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
View user's profile Send private message
jbanoop
PostPosted: Thu May 17, 2007 5:36 pm    Post subject: Reply with quote

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
View user's profile Send private message Yahoo Messenger
sunny_30
PostPosted: Thu May 17, 2007 6:12 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Update statement failure.
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.