Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Posts
    58

    Unanswered: Aborting DB2 scripts and rolling back changes

    How do I make a DB2 script one transaction that I can rollback if
    something fails?

    I would like to do somthing like this:

    BEGIN TRANSACTION;

    ALTER TABLE FOO ...

    ALTER TABLE FOO ...

    (if no errors so far)
    COMMIT

    else

    ROLLBACK


    I can throw a SQL EXCEPTION with SIGNAL SQLSTATE '80000'
    manually just fine, but the script just keeps on running.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Aborting DB2 scripts and rolling back changes

    Originally posted by wayneb64
    How do I make a DB2 script one transaction that I can rollback if
    something fails?

    I would like to do somthing like this:

    BEGIN TRANSACTION;

    ALTER TABLE FOO ...

    ALTER TABLE FOO ...

    (if no errors so far)
    COMMIT

    else

    ROLLBACK


    I can throw a SQL EXCEPTION with SIGNAL SQLSTATE '80000'
    manually just fine, but the script just keeps on running.
    You could do is within a shell script, like this:
    Code:
    #!/usr/bin/ksh
    db2 +c "begin transaction"
    db2 +c "alter table..."
    RC=$?
    db2 +c "alter table..."
    RC=$?
    if [[ $RC != "0" ]]; then
        db2 "commit"
    else
        db2 "rollback"
    fi
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2003
    Posts
    58
    I am running on a PC using the DB2 CLP. ksh is not an option.

    Here is what I get in the CLP:

    D:\workspace\Maestro\DB\bridges>db2 +c "begin transaction"
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "begin
    transaction". Expected tokens may include: "JOIN <joined_table>".
    SQLSTATE=42601

    D:\workspace\Maestro\DB\bridges>db2 begin transaction
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "begin
    transaction". Expected tokens may include: "JOIN <joined_table>".
    SQLSTATE=42601

    DB2 does not seem to recognize BEGIN TRANSACTION.
    How do you abort a SQL script when using DB2?
    How do you get transactional integrity in DB2?
    It must be possible, I just can't seem to find the syntax.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Not too sure ....

    But, check COMPOUND SQL

    BEGIN ATOMIC
    ...
    ..
    END


    Cheers

    Sathyaram


    Originally posted by wayneb64
    I am running on a PC using the DB2 CLP. ksh is not an option.

    Here is what I get in the CLP:

    D:\workspace\Maestro\DB\bridges>db2 +c "begin transaction"
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "begin
    transaction". Expected tokens may include: "JOIN <joined_table>".
    SQLSTATE=42601

    D:\workspace\Maestro\DB\bridges>db2 begin transaction
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "begin
    transaction". Expected tokens may include: "JOIN <joined_table>".
    SQLSTATE=42601

    DB2 does not seem to recognize BEGIN TRANSACTION.
    How do you abort a SQL script when using DB2?
    How do you get transactional integrity in DB2?
    It must be possible, I just can't seem to find the syntax.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Aug 2003
    Posts
    58
    I found the example below:

    !echo beginning complex.db2@
    !echo creating table....@
    CREATE TABLE HELLO (mycol VARCHAR(20))@

    begin atomic
    if (DAYOFWEEK (Current Timestamp)=2) then
    insert into HELLO values ('Hello Monday');
    elseif (DAYOFWEEK (Current Timestamp)=6) then
    insert into HELLO values ('Hello Friday');
    else
    SIGNAL SQLSTATE '80000'
    SET MESSAGE_TEXT='Script is for MON & FRI only!';
    end if;
    end@

    !echo Retrieving from HELLO table..@
    SELECT * FROM HELLO@

    !echo Cleaning up...@
    DROP TABLE HELLO@


    This works, but is useless. The SIGNAL command does not abort
    the script, and I cannot put any other SQL commands like ROLLBACK
    in there or I get errors.

    Surely someone uses DB2 scripts to write bridges that are transactionally safe.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have a look at the command reference for Command Line Processor Chapter ...

    See if -s and +c options help

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

  7. #7
    Join Date
    Aug 2003
    Posts
    58
    Thats funny, as I JUST discovered -s and +c.
    I can run a script with -s +c and then COMMIT or ROLLBACK
    manually after it completes. Still seems a bit crude however
    that I cannot code this logic directly into the script.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you can do a bit of scripting you can automate it ...

    In Unix, I'll do

    db2 +c -stf script1.sql

    Next line, check for the return code, ie $?
    If this is 0, then commit else rollback ..

    Windows should allow you to do something similar , I would assume

    Cheers
    Sathyaram


    Originally posted by wayneb64
    Thats funny, as I JUST discovered -s and +c.
    I can run a script with -s +c and then COMMIT or ROLLBACK
    manually after it completes. Still seems a bit crude however
    that I cannot code this logic directly into the script.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Aug 2003
    Posts
    58
    The problem is I work in both a Unix and a DOS environment.
    Thats why I want the bridge to be self-contained in SQL so
    I can run it anywhere without having to write two environment
    specific wrappers. I should not need a wrapper. MS SQLServer
    supports this just fine with BEGIN TRANSACTION. My only other
    altrenative is to write the bridge in Java. A bit more painful but
    I could throw it in a daemon and have the website run the
    bridges automatically when a new EAR file is dropped.

Posting Permissions

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