We help IT Professionals succeed at work.

how to perform autocommit off in db2 using perl

Zahid786
Zahid786 asked
on
492 Views
Last Modified: 2009-04-02
Hi,
I have created a Perl scripts which run multiple DB2 sql commands but when any sql statment fails, it did not rollback, mean it is auto commit and i want to rollbacl all statements when any one of these statement fail. Would you please help to solve this issue.
Thanx in advance.

Zahid Mahmood
Comment
Watch Question

how do you run the db2 commands ? using the db2 command line processor ?
if so, just add the switch -c, for example
db2 -c "update table1 set col1 = 1"

this way auto commit is turned off and you will have control over transaction boundaries using commit and rollback commands

Author

Commented:
i try this but this does not work here is my query

$output = `db2  -xr $rpt -c "delete from mytable"`;
$rc = $?;
and i also try with


$output = `db2 -c -xr $rpt  "delete from mytable"`;
$rc = $?;

Regards,
Zahid Mahmood
Top Expert 2009

Commented:
Typically to have perl connect to a database, you would use the DBI module.  Are you using this module?

If so, to turn auto-commit off for the connection:
    my $dbh = DBI->connect($dsn, $user, $pass, {AutoCommit => 0});
what do you mean does not work?
what do you receive back and what do you want to receive back?

Author

Commented:
I have got the solution by using the query as
$output = `db2 -c-  -xr $rpt  "delete from mytable"`;
$rc = $?;
mean autocommit off in this case and rollback will roll back ur changes.

Any how thnx for your time and help.

Regards

Zahid Mahmood
but that is exactly what i told you to do

Author

Commented:
Hi
You asked me to do -c not -c-  any how thanx
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.