Different isolation level for each query

RamsIBM · New User Joined: 08 Jan 2007 Posts: 53 Location: Chennai

A program having ten SQL query, how shall I give different isolation level for each query?

stodolas · Posted: Thu Mar 13, 2008 5:33 pm

Add WITH ## to the end of the query. Check the manual for syntax and details on this.

Gnanas N · Posted: Thu Mar 13, 2008 6:53 pm

This information is from Jim Wankowski's white paper on Isolation Levels and Data Consistency.

Starting with version 8.1, DB2 provides the ability to specify the isolation level for a particular query with the WITH [RR | RS | CS | UR] clause, which can be appended to a SELECT statement.

See this also,

Although isolation levels control behavior at the tansaction level, they're actually set at the application level. Therefore, in most cases, the isolation level specified for a particular application is applicable to every tansaction that's initiated by that application.

For embedded SQL statements, the isolation level is specified at precompile time or when the application is bound to database(if deferred binding is used) via the ISOLATION option of the PRECOMPILE or BIND command.