Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Posts
    63

    Unanswered: AutoCommit in Stored procedure

    Friends

    I am not sure of setting autocommit off in one of my stored prcedure.
    Can any one help me .
    I appreciate for any immediate help

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I usually (but not always) do commits inside the stored procedure. One of the main benefits of a stored procedure is that it minimizes the elapsed time between multiple SQL statements (by running them back-to-back on the server). When I want to minimize the elapsed time of the Unit of Work, I commit as soon as possbile withiin the SP.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    While Marcus has a good point, you should also think carefully about the context in which your SP may be called. A SP is called in a transaction/unit-of-work initiated by the caller, you will effectively commit the caller's transaction. That may be acceptable - or maybe not!
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2010
    Posts
    4
    Quote Originally Posted by stolze View Post
    While Marcus has a good point, you should also think carefully about the context in which your SP may be called. A SP is called in a transaction/unit-of-work initiated by the caller, you will effectively commit the caller's transaction. That may be acceptable - or maybe not!
    If I do not want to commit callers update in a stored procedure, how to turn autocommit OFF for a SQL stored procedure when it is ON at system level.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Stored Procedues by default have auto commit off. In other words, unless you issue an explicit COMMIT inside a stored procedure or you mention "COMMIT ON RETURN YES", then no commit is performed.
    The caller (eg CLP) may have auto-commit ON, which means, once the stored procedure execution completes and the control returns back to the caller, a commit is issued automatically.


    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Feb 2010
    Posts
    4
    Quote Originally Posted by sathyaram_s View Post
    Stored Procedues by default have auto commit off. In other words, unless you issue an explicit COMMIT inside a stored procedure or you mention "COMMIT ON RETURN YES", then no commit is performed.
    The caller (eg CLP) may have auto-commit ON, which means, once the stored procedure execution completes and the control returns back to the caller, a commit is issued automatically.


    HTH

    Sathyaram
    Thanks for your quick response.
    However if I invoke a stored procedure in a transaction under IMS, I get SQLCODE -925 and SQLSTATE 51006 the moment stored procedure gets completes (i.e. transaction is till progress).
    That tells me stored procedures is issue the commit.
    What to do? Is there any idea to find out where commit is getting invoked?

    Thanks and Regards
    Aqeel..

Posting Permissions

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