Results 1 to 11 of 11

Thread: About Commit

  1. #1
    Join Date
    May 2005
    Posts
    25

    Unanswered: About Commit

    Hi,

    I am novice in DB2 database, I use commit whenever I do DML operation in Oracle to ensure that My transaction is permanent. But I see strange in DB2, Eventhough i don't commit explicitly, my transactions are permanent.

    Kindly tell me what is the difference b/w SQL statement with commit and with out commit in DB2.

    And also please tell, is there any problem if I don't commit explicitly my SQL statments...

    Thanks,
    Shahnaz.

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Nothing is strange... its just that you are new to the vast DB2 world

    which tool are you using for connecting to server?
    Auto commit is enabled over it

    For command line , you can chk it using
    db2 LIST COMMAND OPTIONS

    ...... and disable if you dont require same
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Aug 2009
    Posts
    8
    if you are running sqls on db2 command line.. specify "+c" while running SQL..


    db2 +c "insert into t1 values(123)"

  4. #4
    Join Date
    May 2005
    Posts
    25
    Thanks for the information.

    autocommit, whether this is user specific or database specific.

    if the autocommit is on then whatever the transaction we do then all will be permanent, please correct me if I am wrong.

    Thanks,
    Shahnaz.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by shahnazurs

    autocommit, whether this is user specific or database specific.

    if the autocommit is on then whatever the transaction we do then all will be permanent, please correct me if I am wrong.
    Autocommit is an application setting, it has nothing to do with DB2. In most cases it is connection-specific.

    When autocommit in CLP is on, a commit is issued by the application after each successful SQL statement. The same is true for JDBC applications, except the case of executeBatch().
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by shahnazurs
    Thanks for the information.

    autocommit, whether this is user specific or database specific.

    if the autocommit is on then whatever the transaction we do then all will be permanent, please correct me if I am wrong.

    Thanks,
    Shahnaz.
    Auto-commit is a property of the client, not the database server. Auto-commit is the default for the command line processor, and is a property that can be set in other interfaces such as java.

    The command line processor options are defined in the Command Reference manual, and as mentioned above you can temporarily turn auto-commit off with the +c option. You could permanently turn it off (for your client machine) by changing your client configuration.

    For real application programs with multiple SQL within a logical transaction, auto-commit is rarely used.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    May 2005
    Posts
    25
    thanks all for your responses...

    My DB client setting or whatever it is, is configured to "Auto commit <ON>". Is it better to user SQL statement with out commit?

    Any consequenses?

    Thanks,
    Shahnaz.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Usually, when people submit scripts from a file or the command line to change a schema or update some data, auto-commit is preferred.

    Usually when an application program is running, there are multiple SQL statements within one logical transaction, and they must all be successful or they must all backout (rollback), so auto-commit is normally not used in these situations and the application controls when the commit SQL command is issued.

    But there are sometimes exceptions.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    May 2005
    Posts
    25
    Thanks Marcus for the explanantion.

    Just to clear my understanding, I have a SQL file where I have 3 to 4 SQL DML statements. Do you mean this as one logical transaction and I should use commit after all the SQL statments?

    Thanks,
    Shahnaz.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Normally when submitting DML via a file or script, auto-commit is used. However, if you suspect there is a possbility of one of the statements failing, and in that situation you want the other statements already run to ROLLBACK, then you will have to turn auto-commit off and do your own commit at the end.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    May 2005
    Posts
    25
    Thanks so much for all of your explanations.

Posting Permissions

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