Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2005
    Posts
    9

    Unanswered: "FOR UPDATE" added to queries

    Hello people,

    A strange things happens when a program is used with DB2:
    The SQL queries will get "FOR UPDATE" appended to them, which locks the tables and kills performance.

    When working with a different DB (MS SQL Server, Oracle) this does not happen.

    Is that a "feature" of the DB2 ODBC driver?
    Is there a way to turn it off?

    Thanks,
    Alex.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No, it is a feature of DB2 and you cannot turn it off. If you don't want to lock the rows, the don't use the "FOR UPDATE" clause.

    AFAIK, SQL server also locks the rows. Oracle makes a separate copy of the data for readers, so that they are not blocked your lock on the row, but this a significant amount of overhead to accomplish.

    You need to make sure you are not having lock escalation to the table level, which can occur if the LOCKLIST is too small (default is very small).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, if you use CS isolation level then only the row the cursor is currently at will be locked.

  4. #4
    Join Date
    Nov 2005
    Posts
    9
    Quote Originally Posted by Marcus_A
    No, it is a feature of DB2 and you cannot turn it off. If you don't want to lock the rows, the don't use the "FOR UPDATE" clause.
    I am not using the "FOR UPDATE" clause as no update is supposed to happen.

    The original query that the code generates looks like:
    SELECT * FROM P.ENR002_SUBSCRIBER WHERE CONT_ID = '9999999999' ORDER BY CONT_ID.

    The FOR UPDATE is being added automagically by something, only when using a DB2 database.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The tool you are using is probably doing that, not DB2 itself. I would download the DB2 runtime client and install it. You will then find an IBM DB2 ODBC driver that you can use, instead of the MS driver. I think it will work a lot better for you.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Nov 2005
    Posts
    9
    Quote Originally Posted by Marcus_A
    The tool you are using is probably doing that, not DB2 itself. I would download the DB2 runtime client and install it. You will then find an IBM DB2 ODBC driver that you can use, instead of the MS driver. I think it will work a lot better for you.
    Thank you for the suggestion, Marcus.

    When you say "tool", do you mean the ODBC driver?

Posting Permissions

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