Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    4

    Unanswered: Management of SQL transaction in a db2pb session.

    Hello,

    I want to execute delete and insert queries thru db2bp and I need to roll-back all the work if an error occured.

    Is-it possible to do it with the db2bp ?

    Thanks in advance

    Technical environment :

    DB2 UDB V7.3 FP10
    Linux Red Hat 7.3

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    There are multiple options :

    1) Using 'UPDATE COMMAND OPTIONS USING c off".
    Go to the db2 prompt and issue the above command, Any command issued following this will not be committed unless you issue an explict commit.

    This works only if you are in a db2 prompt.

    2) Using export DB2OPTIONS="+c" .

    Until you reset the DB2OPTIONS environment variable, your shell w.r. to db2 is autocommit off

    3) Using db2 +c
    Issuing
    db2 +c <SQL STmat>

    will mean that this particular SQL STatement is not autocommit.

    Use the option that suits you ...

    I generally prefer the third one.

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

  3. #3
    Join Date
    Mar 2004
    Posts
    448
    what I am getting is that you want to use db2 in command line mode.
    db2bp is the back process, every time when you start db2 session, it creates a db2bp for the session(not for each command) and definatly a front process that correspond with that.
    you can use

    db2 +c insert ...
    db2 +c do more work

    any time you start db2 without +c ,any commands that where issued with autocommit off will be automatically committed..
    Now you can explicitly commit or rollback you commands.

  4. #4
    Join Date
    May 2004
    Posts
    4

    db2 command line mode

    That's I want to do but with an input file.

    For exemple :

    * input file : test.sql :

    connect to test;
    create table my_table ( col int );
    insert into my_table values ( 1 );
    insert into my_table values ( a );
    commit;
    terminate;

    * Command tested :

    db2 +a -f -t test.sql

    The second insert generates an error ( data type mismatch ) but the first value is inserted is still in my_table.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You may have to use both -s and +c option

    db2 +c +a -stf test.sql
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    May 2004
    Posts
    4

    Thanks, it works fine now.

Posting Permissions

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