Professional Web Applications Themes

Commit on closing connection? - IBM DB2

Hi there, using a Java program on OS390 and DB2 version 7, we have the following problem: After creating a connection using "setAutoCommit(false)", we do some updates on the database. If all updates have been done, a commit is sent to the database. All this works well. If we make the update on the database without sending a (manual) commit to the database and closing our connection, the updates are also commited even if we did not call "connection.commit()". We have always thought that the database will do a rollback if the connection will be closed without sending a commit, ...

  1. #1

    Default Commit on closing connection?

    Hi there,

    using a Java program on OS390 and DB2 version 7, we have the following
    problem:

    After creating a connection using "setAutoCommit(false)", we do some
    updates on the database. If all updates have been done, a commit is
    sent to the database. All this works well.

    If we make the update on the database without sending a (manual)
    commit to the database and closing our connection, the updates are
    also commited even if we did not call "connection.commit()". We have
    always thought that the database will do a rollback if the connection
    will be closed without sending a commit, but this seems not to be
    true.

    The code we use looks like this:

    // Getting a connection
    Connection con = ...;

    // turn off auto commit
    con.setAutoCommit(false);

    // creating prepared statements
    PreparedStatement preparedStatement = ...;

    try
    {
    preparedStatement.executeUpdate("<sql command 1>");
    preparedStatement.executeUpdate("<sql command 2>");
    preparedStatement.executeUpdate("<sql command 3>");
    }
    catch (Exception e)
    {
    try
    {
    con.rollback();
    }
    catch (SQLException e)
    {
    // Logging information
    }
    }
    finally
    {
    con.close();
    }

    If no exception occurs, the finally block will be reached after
    executing the updates. After the connection is closed and no
    "connection.commit()" was called, all the updates made in the
    try-block are commited in the database.

    Is there any global setting of DB2 version 7 on OS390, where can be
    defined if a rollback should be called on closing a connection or a
    commit? If there is such a global setting, where can we find it and
    how is it called?

    Many thanks for your answers.

    We are looking forward to hearing from you as soon as possible.

    Best regards



    Ralf
    Ralf Assmann Guest

  2. #2

    Default Re: Commit on closing connection?

    MVS/OS390/ZOS
    http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnsqh13/5.17?ACTION=MATCHES&REQUEST=implicit&TYPE=FUZZY&SH ELF=dsnshha4&DT=20030509185119&searchTopic=TOPIC&s earchText=TEXT&searchIndex=INDEX&rank=RANK&ScrollT OP=FIRSTHIT#FIRSTHIT

    In all DB2 environments, the normal termination of a process is an implicit
    commit operation.



    On non MVS/OS390/ZOS platforms (LUW), the use of DB2NOEXITLIST variable
    would match your request.
    (not a recommendation)
    http://publib.boulder.ibm.com/infocenter/db2help/index.jsp


    Windows behaves one way and unix behaves/behaved an other way...

    other sources of info :
    newsgroup : bit.listserv.ibm-main
    list : http://listserv.ylassoc.com/archives/db2-l.html
    v7 books : http://www-3.ibm.com/software/data/db2/os390/v7books.html

    PM



    "Ralf Assmann" <de> a écrit dans le message de
    news:google.com... 


    PM Guest

  3. #3

    Default Re: Commit on closing connection?

    Additional info that you don't need but that relates to the topic.
    (just adding details to my previous post)

    Uncommitted units of work on UNIX platforms


    UNIX


    Change
    In Version 8, all application terminations implicitly roll back outstanding
    units of work. Windows-based applications will not change as they already
    perform an implicit ROLLBACK for normal or abnormal application termination.
    Prior to version 8, UNIX-based applications that did not use either explicit
    or implicit context support would commit an outstanding unit of work if the
    application terminated normally without directly invoking either a CONNECT
    RESET, COMMIT, OR ROLLBACK statement. CLI, ODBC, and Java-based applications
    (implicit context support) and applications that would explicitly create
    application contexts would always roll back any outstanding unit of work if
    the application terminated. Abnormal application termination would also lead
    to an implicit ROLLBACK for the outstanding unit of work.


    Resolution
    In order to ensure that transactions are committed, the application should
    perform either an explicit COMMIT or a CONNECT RESET before terminating.

    PM


    PM Guest

  4. #4

    Default Re: Commit on closing connection?

    Many thanks for your anwers ...
     

    Termination of a process does mean closing the connection using the
    method "connection.close()". So, this method does always an implicit
    commit operation. On the other side, if the program is terminated by
    an exception and no "connection.close()"-method is called, there will
    be a rollback done by the database itself.

    That is the final result of some tests using the OS390 platform. Is
    this correct?

    Nevertheless, is there any chance to tell the database or JDBC not to
    do an implicit commit using "connection.close()" on OS390 similar to
    the DB2NOEXITLIST variable on non MVS/OS390/ZOS platforms like you
    have written?

    Many thanks!


    Ralf
    Ralf Guest

Similar Threads

  1. Closing connection of one user..
    By Unknowner in forum Macromedia Flash Flashcom
    Replies: 1
    Last Post: July 14th, 05:32 PM
  2. Closing an ASP connection
    By htown in forum Macromedia Dynamic HTML
    Replies: 2
    Last Post: June 8th, 10:28 PM
  3. Closing one active ODBC connection
    By HSC Canada in forum Coldfusion Server Administration
    Replies: 5
    Last Post: February 28th, 02:49 PM
  4. closing a DB connection? ASP/VBScript
    By Nathon Jones in forum Macromedia Contribute Connection Administrtion
    Replies: 0
    Last Post: August 1st, 05:17 PM
  5. Closing Database Connection
    By patelajk in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 6th, 10:07 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139