Hi, all

I have a procedure in oracle that contains PRAGMA AUTONOMOUS_TRANSACTION, someone knows what is the statement in DB2 that makes the same think.

Thanks a lot.

There's no such thing in DB2.

But that isn't nothing in DB2, that can i do to make a commit or a rollback, only in the processes that are included in my procedure or routine?

There's always a workaround; however it's difficult to give adivce not knowing your reasons.

AUTONOMOUS_TRANSACTION feature in Oracle is often used to overcome that "mutating table" problem, which doesn't exist in DB2 - therefore you may not need autonomous transactions at all when developing for DB2.

In my case i want AUTONOMOUS_TRANSACTION to do something like that:

I have a procedure that executs a insert and i want to do a COMMIT only in this insert, if i execute before another insert this will not be commited, only the insert that contains the AUTONOMOUS_TRANSACTION, i don't know if you understand, i don't speek english very well.

Thanks a lot.

I haven't tried it myself by you could see if the following would work for you:

But in this case the db2 will commit the first insert, the second insert or both?

I think that if you roll back to the savepoint in the above example only the first insert will be committed; otherwise both will be committed.

In my case i want to commit only the second insert, the first i'll not commit, with a savepoint can i do that?

If you commit the second insert regardless of the success of the first one you might as well change their order; then the second becomes the first...

I know this, but i need the first insert to executes the second, i can't change the order of them, in oracle do that is simple, as the same as i can isolate a statement ( in this case a insert ) i only want to validate ( commit ) the rows inserted or updated in this statement, and the others statements that i execute after or before that will not be commited.

Thanks a lot for your atention.