| Author |
Message
|
| pratikp.vasani |
 Posted: Mon Jul 04, 2011 5:02 am Post subject: Roll back ATOMIC feature in ESQL |
 |
|
Novice
Joined: 04 Jul 2011
Posts: 11
|
I have two tables one is the Main table having primary keys and the second is the Child table having foreign keys pointing reference towards the Main table.
I want to update the foreign key, so in the ATOMIC loop I am first deleting the record from the child table and updating the Main table, then inserting the updated values in the Child table. But if any exception is there the ROLLBACK is not happening.
Please give a proper way to use the ATOMIC ROLLBACK.
Also if you can suggest a way to update the primary keys in the Main table and the Child table(in which the foreign keys are the reference to the primary keys to the Main table). |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Mon Jul 04, 2011 7:04 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
I'd do all of this in a Database Stored procedure and not in ESQL.
_________________
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 |
|
|
| pratikp.vasani |
| Posted: Tue Jul 05, 2011 1:04 am Post subject: |
|
|
Novice
Joined: 04 Jul 2011
Posts: 11
|
Yes I should have gone for the procedures itself but I have wrriten a huge code for it in ESQL and now it will be quit long thing for me to do it via database procedures.
Can you suggest something in ESQL which could deal with it.
Thank you. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Jul 05, 2011 3:33 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
What I was suggesting is that you do the WHOLE thing inside a DB Stored proc. That way you can guarantee the transactionality and importantly, a properly controlled rollback.
_________________
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 |
|
|
| lancelotlinc |
| Posted: Tue Jul 05, 2011 4:43 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Compensation is not a function of the Compute node. If you write ESQL to perform transactions, you must also write other code the perform compensating transactions in the event you want to rollback. Usually this other code is in your un-happy path part of the message flow in a totally different node.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| pratikp.vasani |
| Posted: Tue Jul 05, 2011 4:51 am Post subject: |
|
|
Novice
Joined: 04 Jul 2011
Posts: 11
|
| Is there any syntax for rollback in ESQL, because I was able to get only the ROLLBACK keyword but I am not able to find the syntax to rollback the commited queries. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Jul 05, 2011 4:54 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Even if there were, how would you rollback when the Exception occurs downstream from your Compute node?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| pratikp.vasani |
| Posted: Tue Jul 05, 2011 4:57 am Post subject: |
|
|
Novice
Joined: 04 Jul 2011
Posts: 11
|
| ok, so any other way to deal with the above thing(Updation of primary keys and foreign keys in two tables) with the possibility of data consistancy in both the tables. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Jul 05, 2011 5:08 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Yes, you write the compensating transactions separately, hanging on the Exception path of your message flow.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| paintpot |
| Posted: Tue Jul 05, 2011 7:49 am Post subject: |
|
|
Centurion
Joined: 19 Sep 2005
Posts: 112
Location: UK
|
If I understand your problem correctly...
Avoid using primary keys that are mutable - if you do need to change them, use a meaningless primary key, and store the 'old' primary key as just another column on the parent table (maybe a unique column).
e.g. Parent table primary key = 1, column 2 = policy number 0123456. Policy number can then be modified, without altering the keys between parent and child entities. Child entity can inherit the correct policy number by a join with its parent.
Therefore the children never need to be updated for this use case. |
|
| Back to top |
|
|
| j.f.sorge |
| Posted: Tue Jul 05, 2011 9:59 pm Post subject: |
|
|
Master
Joined: 27 Feb 2008
Posts: 218
|
| lancelotlinc wrote: |
| Even if there were, how would you rollback when the Exception occurs downstream from your Compute node? |
PASSTHRU('ROLLBACK') within an CONTINUE HANDLER could do the trick.
_________________
IBM Certified Solution Designer - WebSphere MQ V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| pratikp.vasani |
| Posted: Tue Jul 05, 2011 10:28 pm Post subject: |
|
|
Novice
Joined: 04 Jul 2011
Posts: 11
|
Can you share the complete syntax to use the ROLLBACK. I tried but it was giving errors.
And also the way to handle the DB exceptions in the code. |
|
| Back to top |
|
|
| j.f.sorge |
| Posted: Tue Jul 05, 2011 10:31 pm Post subject: |
|
|
Master
Joined: 27 Feb 2008
Posts: 218
|
| pratikp.vasani wrote: |
Can you share the complete syntax to use the ROLLBACK. I tried but it was giving errors.
And also the way to handle the DB exceptions in the code. |
PASSTHRU statement
DECLARE HANDLER statement
_________________
IBM Certified Solution Designer - WebSphere MQ V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| pratikp.vasani |
| Posted: Wed Jul 06, 2011 4:41 am Post subject: |
|
|
Novice
Joined: 04 Jul 2011
Posts: 11
|
I have two insert statements the first one is right and it is getting inserted into the db but the second one is wrong and the SQLSTATE is 23505 and the below IF condition is happening but the PASSTHRU ROLLBACK is not done
SET sqlerror1=SQLSTATE;
SET sqlerror2=SQLCODE;
SET sqlerror3=SQLERRORTEXT;
IF sqlerror1='23505' THEN
PASSTHRU 'ROLLBACK TABLE mbid.SAMPLE' TO Database schemaname};
THROW USER EXCEPTION MESSAGE 2345 VALUES('ERROR',sqlerror1,sqlerror2,sqlerror3);
END IF;
Please suggest a way to implement it. |
|
| Back to top |
|
|
| j.f.sorge |
| Posted: Wed Jul 06, 2011 4:44 am Post subject: |
|
|
Master
Joined: 27 Feb 2008
Posts: 218
|
How did you configure the transaction handling of the flow / the node where you're doing the INSERTs?
_________________
IBM Certified Solution Designer - WebSphere MQ V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
|
|
