Results 1 to 6 of 6

Thread: Transactions

  1. #1
    Join Date
    Aug 2003
    Posts
    24

    Arrow Unanswered: Transactions

    Just want to verify something.
    While using SQL statements, I dont seem to have control over transactions.
    For example ->
    1. Issue "SAVEPOINT foo ON ROLLBACK RETAIN CURSORS"
    (I even tried "SET CURRENT ISOLATION = RR")
    2. Update a column
    3. Intentionally do not issue "COMMIT WORK"
    4. Select the column that I updated.

    The column gets updated, even though I didn't commit the transaction.
    Im assuming that DB2, uses some form of "autocommit", which is probably on.

    The situation is Im accessing DB2 (8.1.3) via php/w2k, and Im trying to overcome problems using their extensions and/or functions, and directly issue sql commands, to get the desired effect that I want.

    Is there a way to control DB2's "autocommit" feature via an SQL statement?
    Thanks
    Last edited by trini0; 06-05-04 at 09:21.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Within the same UOW, you will be able to see the updated record ... It is only from another transaction that you cannot 'see' the new row ...

    After step 2, open a command window and issue a SELECT Statement to retrieve the row in question ... You should not be able to retrieve the row if it has not been committed ...

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

  3. #3
    Join Date
    Aug 2003
    Posts
    24

    Unhappy

    I understand your answer, and I guess I provided a bad example.
    But given the example sequence that I provided, Im able to view the
    changed database column via the Control Center, even though, I intentially,
    didn't commit to the changes, in the php script.
    Ill let it sit on the brain for a few, and see if I didn't overlook anything...

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The database engine itself does not auto-commit. Some of the DB2 interfaces such as CLI, jdbc, etc have an auto-commit feature in them if they are enabled (some have auto-commit as the default). I don't know which DB2 interface PHP uses, or how to turn off auto-commit from PHP, but maybe you can find out with some research.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    One option is to set the db2cli.ini parameter AUTOCOMMIT=0. This will disable autocommit for all CLI/ODBC applications connnection to the database using that alias .. You can find details at
    http://publib.boulder.ibm.com/infoce...d/r0007964.htm

    On the php side(i'm a novice here), check if this page helps

    http://www.php.net/manual/en/functio...autocommit.php

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

  6. #6
    Join Date
    Aug 2003
    Posts
    24
    Thanks for your replies.
    I used to use php's ODBC extension to access DB2, but it only supports v2 of the ODBC spec,
    and doesn't work well with DB2.
    Im currently trying out another php extention ODBTP (http://odbtp.sourceforge.net/) which supports v3 of the ODBC spec, but Im currently working with the developer to work out some
    gliches (of which I cant seem to turn off autocommit).
    I started this thread to see if I had a last resort to control autocommit behaviour (at will) via an SQL statement, like how mySQL does.

Posting Permissions

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