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

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.

Commit is present within the stored procedure after DML. Thanks for the comment. Let me start my work on test env.

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?