Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    4

    Red face Unanswered: oracle - DB2 ? Consider this..

    In oracle in the sql i can execute the following query.

    SQL> begin update table1 col1 = 1 where col1 = 100; update table2 set col1 = 1 where col1 = 100; commit; end;
    SQL>/

    It works fine.

    Is there any similar way to execture the above query in DB2 ?

    I referred DB2 reference manuals.
    I tried with begin atomic and compoun statements also.
    But i couldn't able to find a solution?

    Can anyone help me?

    Thanks & Regards,
    muthu

  2. #2
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    Muthu,

    I don't know Oracle (I suppose, the "SQL>" is an interpreter???). If you want to work the same way in DB2, you will use the command line processor (CLP). By default, the CLP works with AUTOCOMMIT (you can see the options with the command "db2 list command options").
    You don't need a "begin" to start your transaction. In DB2, a transaction starts at programm begin or after the last unit of work (COMMIT/ROLLBACK).
    So, if I understand you correctly, you can achieve your goals in DB2 the following way:

    <your shell> db2 connect to <your_db>
    <your shell> db2 -c- update table1 col1 = 1 where col1 = 100
    <your shell> db2 -c- update table2 set col1 = 1 where col1 = 100
    <your shell> db2 commit

    (The -c- option tells the interpreter not to use autocommit. You can also set the options with the command "db2 update command options". Give a look on the manuals for it).

    HTH.
    Rodney Krick

  3. #3
    Join Date
    Mar 2004
    Location
    India
    Posts
    4
    Krick,

    Thanks for your reply and nice comments.
    You are right. 'SQL>' is an interpreter.

    In oracle the block of statement is a PL\SQL procedure

    'begin update table1 col1 = 1 where col1 = 100; update table2 set col1 = 1 where col1 = 100; commit; end;'

    You can execute this block as a single statement in SQL [Application Development].

    I referrred the auto commit options mentioned by you.

    But my concern is i want to execute this as a single statement.
    Whether is it possible to execute this as a single statement from the CLP.

    Hopefully i explained you properly.

    Thanks & Regards,
    Muthu

    Originally posted by RKrick
    Muthu,

    I don't know Oracle (I suppose, the "SQL>" is an interpreter???). If you want to work the same way in DB2, you will use the command line processor (CLP). By default, the CLP works with AUTOCOMMIT (you can see the options with the command "db2 list command options").
    You don't need a "begin" to start your transaction. In DB2, a transaction starts at programm begin or after the last unit of work (COMMIT/ROLLBACK).
    So, if I understand you correctly, you can achieve your goals in DB2 the following way:

    <your shell> db2 connect to <your_db>
    <your shell> db2 -c- update table1 col1 = 1 where col1 = 100
    <your shell> db2 -c- update table2 set col1 = 1 where col1 = 100
    <your shell> db2 commit

    (The -c- option tells the interpreter not to use autocommit. You can also set the options with the command "db2 update command options". Give a look on the manuals for it).

    HTH.

  4. #4
    Join Date
    Dec 2002
    Posts
    134
    "begin atomic/end" can help you. the only catch - transaction control is not allowed inside.

  5. #5
    Join Date
    Mar 2004
    Location
    India
    Posts
    4
    Hi chuzhoi,

    Thanks for your reply.

    I tried with begin atomic also

    I executed in the following way

    ************************************************** ********

    db2 => begin atomic update <table_name> set col1 = 1 where col1 = 100; commit; end

    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 "commit" was found following "1=1 where col1=100;". Expected tokens may include: "SIGNAL". LINE NUMBER=1. SQLSTATE=42601

    ************************************************** ********

    Can you help me to find out a solution?

    Thanks & Regards,
    Muthu

  6. #6
    Join Date
    Dec 2002
    Posts
    134
    As I mentioned in my previous post - transaction control is not allowed inside compoud statements (no commit/rollback)

    What layer do you you use to access database? Most of the APIs have autocommit option.
    db2 command line has autocommit on by default. You can turn it off by "db2 +c" or "db2 update command options using c off"

    -dmitri

  7. #7
    Join Date
    Mar 2004
    Location
    India
    Posts
    4
    Thanks a lot.

    I though it might be available with DB2 also.

    I gave the commit after the update statments explicitly.

    Thanks a lot for your inputs.

    Regards,
    Sudalai muthu. M

Posting Permissions

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