Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Posts
    5

    Question Unanswered: Question on COMMIT inside child procedure

    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!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes you can do that. If each child SP is atomic, then that is what you want to do.

    Andy

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Dec 2009
    Posts
    5
    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!

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Dec 2009
    Posts
    5
    Quote Originally Posted by stolze View Post
    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!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •