A_DB2_LOCK_METHOD configuration variable

Use this configuration variable to specify the locking method that Database Connectors for DB2 should use when accessing your data source. Possible values are:

None
SETPOS
SETSTMTOPTION
UPDATECOLUMN

The default is UPDATECOLUMN.

Example:

A_DB2_LOCK_METHOD UPDATECOLUMN

SETPOS

Specify SETPOS as your lock method to tell the Connector to perform the following locking sequence:

When setting up a statement handle for accessing the DB2 data source, Database Connectors calls a function called SQLSetScrollOptions, with values (SQL-CONCUR-LOCK, SQL-SCROLL-KEYSET-DRIVEN, 1)
When fetching rows from the data source, Database Connectors calls SQLExtendedFetch instead of SQLFetch
Finally, the Connector calls SQLSetPos, with the values (1, SQL-POSITION, SQL-LOCK-EXCLUSIVE)

If any of these functions do not exist, A_DB2_LOCK_METHOD reverts to None.

Note: Even if these functions do exist, there is no guarantee that any rows lock using this sequence of calls. Contact your database vendor to determine whether or not this sequence is effective for your data source.

SETSTMTOPTION

This option is similar to SETPOS, except a function called SQLSetStmtOption performs the task of SQLSetScrollOptions and has more functionality.

In particular, when using this method of locking, Database Connectors calls SQLSetStmtOption a number of times, with values (SQL-CONCURRENCY, SQL-CONCUR-LOCK), (SQL-CURSOR-TYPES, SQL-CURSOR-KEYSET-DRIVEN), and (SQL_KEYSET-SIZE, 1). Again, if the function does not exist, A_DB2_LOCK_METHOD reverts to None.

UPDATECOLUMN

Specifying UPDATECOLUMN as your lock method performs an entirely different type of locking. Instead of trying to lock a row while reading it, this method creates a new statement handle for the data source. Then, after fetching the data from the data source, it resubmits an SQL query to select the same row (based on the primary key) and adds an UPDATE clause. Last, it fetches the data from the data source. This method has the most overhead, but it is much more likely to succeed in locking records.