| Author |
Message
|
| Shwetabh |
 Posted: Tue Sep 29, 2015 2:58 am Post subject: Ambiguity while inserting/Update data using stored proc. |
 |
|
Newbie
Joined: 03 Aug 2015
Posts: 8
|
Hi Team,
We are having a situation:-
MQInput(transactional property set to Yes) -> Compute node(Making stored proc call twice and create xml output) -> MQOutput.
Compute mode is set to Automatic.
We are making stored proc 'A' call twice in such a way ,first call will insert in table T and in later part of same esql ,we update table T using same stored proc A.we are not using any commit operation in between to maintan transactionality.However one of the column is not updated correctly in table once transaction is complete.
But in the final xml generated , we are able to see the correct value in the xml tag.
Value is populated from input message.
What can be the possible reason?
Will there be two ODBC connection in this case.Probably not.My main dilemma is whether the sequence is maintained while executing the two insert/update on saame table using stored proc?
Please guide.
Regards,
SSS |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Thu Oct 01, 2015 6:18 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
There's only one ODBC connection available for each instance of the message flow that's running.
You can't do an insert and an update in the same transaction, as the insert is not committed until the transaction is, and so the update can't execute.
This doesn't have anything to do with how your message tree is populated.
You may have luck moving the update logic to a second compute node.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| Shwetabh |
| Posted: Thu Oct 01, 2015 6:40 am Post subject: |
|
|
Newbie
Joined: 03 Aug 2015
Posts: 8
|
Hi Jeff,
I agree there will be commit after transaction is complete and there is one ODBCconnection if we are connecting to one db. But we can give two insert ,insert and update in one compute node without any commit command in between.DML is managed by resource manager which will execute the sequence in the order it gets request.
Let's have analogy.in SQL developer we give two different insert statement and then commit.It is the work of resource manager to schedule the job.
However I made it non transactional by giving commit between first insert and second update .After first insert ,it is populating data properly.However after second commit it is not .
Either it is transactional or non transactional,it is giving same value.
Plus
In one of my local system ,it is working fine.
In other two it is not.
I had doubt on dsn configured on different system but config looks similar in all system .There are other fields with similar value and are getting populated properly.
Please correct me if I am wrong. |
|
| Back to top |
|
|
| Shwetabh |
| Posted: Thu Oct 01, 2015 6:51 am Post subject: |
|
|
Newbie
Joined: 03 Aug 2015
Posts: 8
|
| Plus it is not parallel operation as in case of any etl where different nodes try to execute parallel at same time.RM knows the timestamp when particular request is receiced.we are running one transaction only.So not an issue with multiple instance running parallel |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Oct 01, 2015 7:02 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Yes. I agree. It's not an issue with multiple instances running parallel. They would each have their own connection and each have their own transaction.
You need to do something to either do the insert out of transaction or otherwise make sure it has been committed before you try and update the same record.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| Shwetabh |
| Posted: Thu Oct 01, 2015 7:11 am Post subject: |
|
|
Newbie
Joined: 03 Aug 2015
Posts: 8
|
Like I said before ,I tried that also by inserting commit after first insert.same result at end.
However after first commit data getting inserted is correct.But second update is giving ambiguous result.
Plus
Code is giving proper result when I am running on IIB 9001 but not properly on iIB 9003 a and wmb7.
However I don't think this is issue with version as these are simple operations .
I suspected drivers and dsn properties on different system too.However that issue will not be there too as on system where one column is not getting updated properly.other column is updated properly.
need to enable log at db end and see why stored process behaving so.What value it is getting in input.
However please update me if same issue replicate and able to resolve.
With regards,
SSS |
|
| Back to top |
|
|
|
|
