Question on COMMIT inside child procedure

raizel · #1 (**permalink**) 12-09-09, 21:35

Hi to all.

I hope someone can help me find asnwers for this.

Scenario: I have one stored procedure which then calls multiple stored procedures. These children stored procedures have insert update and delete statements. Is it possible to issue a 'COMMIT;' statement "inside" these children stored procedures? I have no testing environment at the moment that's why I cannot test if its allowed. My first design was to issue the commit inside the parent stored procedure every after child procedure is done. But I found out that I need it inside child procedures.

Thanks a lot!

ARWinner · #2 (**permalink**) 12-10-09, 08:20

Yes you can do that. If each child SP is atomic, then that is what you want to do.

Andy

rahul_s80 · #3 (**permalink**) 12-10-09, 08:41

two things to remember:
1. cursor will be closed if not with hold because of commit.
2. You cannot issue commit within a begin atomic statement

stolze · #4 (**permalink**) 12-10-09, 10:27

Another remark: if a nested procedure commits, it actually commits at the transactional scope of the DB2 client. Calling a stored procedure does not open a nested transaction or autonomous transaction. The only thing you can do is to set a savepoint and rollback to that savepoint within a procedure (but not COMMIT).

raizel · #5 (**permalink**) 12-10-09, 17:41

Thanks for all the replies =).

Here is a simple Pseudo code:

Parent Procedure:

Code:

Procedure 1()

	declare global session table here;

	for each stored procedure

		call procedureChildX()	;
	
		do something here;

		commit;

	end for;	

	open cursor for global session table here;

end procedure 1

Child Procedure:

Code:

procedureChildX()


	for each selected rows from tableA
		
		insert to TableB
		
		commit;

	end for;

	insert something to global session table here;

end procedureChildX

I'm just wondering if there will be problem here I must address. I still have no testing environment at the moment.

Thanks a lot!

stolze · #6 (**permalink**) 12-11-09, 11:26

As I said, each COMMIT statement (regardless where it occurs) commits the transaction initiated by the DB2 client. Whether those semantics are acceptable for your client application is something you have to determine. Other than that, you can do this...

raizel · #7 (**permalink**) 12-12-09, 05:16

Quote:

Originally Posted by stolze

As I said, each COMMIT statement (regardless where it occurs) commits the transaction initiated by the DB2 client. Whether those semantics are acceptable for your client application is something you have to determine. Other than that, you can do this...

That clarified my question. Thank you so much!

Internet Services

Web Development

Digital Marketing

Consumer Tech