Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: Strange procedure requirement

    I have a stored procedure which manipulates data in certain tables (and followed by a commit statement always).

    Now my requirement is just to test whether this procedure is running fine so I don't want any data to be manipulated by this stored procedure.

    So if I run the procedure as below will it satisfy my requirement ?
    db2 +c "call proc_name()"
    db2 rollback.

    PS: I have around 50 such procedure so I do not want to comment out any existing DML operations within the procedure.
    Env : DB2 V8.2 / AIX 5.3

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by db2udbgirl
    I have a stored procedure which manipulates data in certain tables (and followed by a commit statement always).

    Now my requirement is just to test whether this procedure is running fine so I don't want any data to be manipulated by this stored procedure.

    So if I run the procedure as below will it satisfy my requirement ?
    db2 +c "call proc_name()"
    db2 rollback.

    PS: I have around 50 such procedure so I do not want to comment out any existing DML operations within the procedure.
    Env : DB2 V8.2 / AIX 5.3
    wouldn't it be the following to turn off autocommit?

    db2 -c- "call proc_name()"

    Anyway you should try it on a test environment first.

    Andy

  3. #3
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    I'm not understanding where the commit is located. Is it in the stored proc following the DML, or is it in the script that calls the stored proc? If the commit is in the stored proc, you're out of luck. If it's in the script, what you're thinking should work. The default for the 'c' option is autocommit on, so issuing the 'c' option will turn autocommit off.

    But like ARWinner said, test it to make sure. If you can't run the same stored procs in a test environment, create new ones for yourself.

  4. #4
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    Commit is present within the stored procedure after DML. Thanks for the comment. Let me start my work on test env.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Using -c- or +c will turn auto-commit off in the CLP. Using just -c will turn it on if it happens to be turned of via the DB2OPTIONS environment variable, for example.

    Since you commit inside the SP, then your only chance is to use a test environment. The commit operation inside the SP tells DB2 to make all changes persistent. Once this is done, the subsequent rollback applies to a new transaction that is implicitly started after the preceding commit/rollback.

    Unless you really have problems with network latency, I would recommend that you remove the COMMIT operation from the stored procedures. Transactional control should be left to the client application. Who knows in which context those procedures will be called and then interfere with the transactions that the client thinks it controls?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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