rollback transaction for single phase commit

I have requirement that i need to insert into two databases i.e., sql and DB2.
But our code not using two phase commit.
The requirement:
i am inserting into sql-database table first by creating one transaction and doing commit after that, and next creating another transaction to inert into DB2-database table and doing commit.
Problem is: if it fails in second transaction its not doing rollback for sql-database table, its doing rollback for DB2-database table only.

Plz any one can help me in this

vamshidhar
scjp
[ December 13, 2005: Message edited by: vamshidhar nimbagiri ]

Vamshidhar,
Welcome to JavaRanch!

Is there any reason you can't use an XA (2 phase commit) driver? That would be a lot easier than simulating one.

If not, the key is to do all the updates to both database before committing either one. There is still a small window for failure - between the two commits - but at least it is smaller.

Hi Jeanne,
Thanks for your quick reply.
The problem is the existing code is done long before and we dont want to include 2-phase commit and ejbs as of now.

i thought of 2 solutions will you tell me weather this is correct:
1.Do you have any idea that DBlink work in SQL server.
2. Can i use savepoints for SQL server and rollback it once it fails.

or If you have any other solution plz tell me.

Thanks,
vamshi

Hi can u tell me weather i can do setSavepoint and rollback on UserTransaction interface.

Thanks

Vamshi,
A savepoint will have the same problem. Once you have committed, you can no longer roll back to a savepoint. The idea with savepoints is that they provide an interim rollback point.

2 phase commit is just the driver. if you are using a DataSource, this won't affect your actual Java code.

I don't know anything about dbLink so I can't comment on whether or not that will help.

Thanks Jeanne,

I thought if we setsavepoint the prior to the savepoint can be rollbacked even if we do commit.

But do any body have solution for this problem.

Thanks in Advance,
vamshi

Hi All,

Do any body knows about DBlink? if yes..
Plz send me some links related that or some notes how to use it?

Thanks in Advance
vamshi

Vamshi,
I recommend posting a new thread in this forum that includes the word dblink in the subject. People who know about dblink (of which I am not one) are more likely to see it that way.

Thanks Jeanne