Problem:

ODBC interface to DB2

Trying to resolve blocking/locking problem for multi-user situation:

Trying With NOLOCK on declare cursor statement is producing invalid sql statement on open cursor (sqlcode=-104). WITH NOLOCK it works fine for SQL Server.

Resolution:

NOLOCK is not an ansi standard option, and is supported by SQL Server, but not by DB2.

Use the FOR READ ONLY clause in the declare statement.

The IBM docs indicate if you do not explictly state FOR READ ONLY the cursor is assumed to be updateable.

In addition, compile with sql(thread=isolate)

With  THREAD  set to ISOLATE, all connections, cursors and so on are local to the thread that creates them.

With THREAD=ISOLATE, each thread gets its own connection.

RESOLUTION: Setting compiler directive sql(thread=isolate) and adding the FOR READ ONLY clause on declare cursor resolved the blocking problem with DB2.