Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    7

    Unanswered: why I cant rollback to savepoint?

    I run the following script in DB2 V8.1 under Linux.

    CREATE TABLE tmp(
    <table definition>
    );
    savepoint SPTMP on rollback retain cursors;
    insert into tmp (select * from orders where o_id<10000);
    select count(*) from tmp;
    rollback to savepoint SPTMP;
    select count(*) from tmp;

    But get the following errors:

    rollback to savepoint SPTMP
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0880N SAVEPOINT "SPTMP" does not exist or is invalid in this context.
    SQLSTATE=3B001

    Apparently I have the savepoint defined. What have I done wrong?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Do you turn off autocommit when you run this script?

  3. #3
    Join Date
    Oct 2004
    Posts
    7
    Thanks for your quick reply. But if I don't want to turn off autocommit, I just want to run the following piece of code in a transaction. What should I do?

    savepoint SPTMP on rollback retain cursors;
    insert into tmp (select * from orders where o_id<10000);
    select count(*) from tmp;
    rollback to savepoint SPTMP;

    I tried wrapping those statements with BEGIN ATOMIC ... END but it doesnt work.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rosbur
    But if I don't want to turn off autocommit, I just want to run the following piece of code in a transaction. What should I do?
    What you're saying essentially is "I want a transaction but I don't want a transaction." A COMMIT ends the current transaction. If you have autocommit turned on it issues a COMMIT after each statement. So, the answer is: if you want to run several statements in a single transaction, turn off autocommit.

  5. #5
    Join Date
    Oct 2004
    Posts
    7
    Maybe I migrated the wrong idea from oracle to DB2. In oracle I can have a bunch of statements executed as a single transaction if they are wrapped with BEGIN ... END clause, but the autocommit flag is still ON. That is I can have some statement autocommited but other running as a transaction.

    --the following statements will be auto committed
    <some statements>
    --statements inside BEGIN..END run as a transaction
    BEGIN
    <statements>
    END
    I thought I can do that as well in DB2.

Posting Permissions

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