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.

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).

Well, if you use CS isolation level then only the row the cursor is currently at will be locked.

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.

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?