Dynamic Query Mode does not respect the Isolation Level setting of a Data Source
When creating a data source, there is an option to manually specify the Isolation Level for that data source (e.g. Read Uncommitted). If you set this, when running Compatible Query Mode reports against the data source you see the correct isolation level used. If you run a Dynamic Query Mode report against the same data source, it does not set the specified Isolation level.
As documented in the IBM Cognos Business Intelligence Administration and Security Guide under the Data Source Creation section, isolation levels are not implemented for JDBC connections.
Resolving the problem
An alternate way to set the isolation level is to add an Open Connection command block to the data source which issues a command specific to your database to set the transaction isolation level. Use the following steps:
1) Launch IBM Cognos Administration -> Configuration -> Data Source Connections
2) Click on the data source to drill into it, then click on the Properties icon for the Connection object.
3) Expand the Commands link, then click the "Set..." link for the "Open connection commands" entry
4) Paste the following XML into the XML Database Commands box:
<sql>set transaction isolation level read uncommitted</sql>
5) Click OK twice to save the changes.
Note that the command syntax here is specific to Microsoft SQL Server - replace "set transaction isolation level read uncommitted" with the appropriate command to set the isolation level for your database.
More support for:
Cognos Business Intelligence
Software version: 10.2.1, 10.2.1.1, 10.2.2
Operating system(s): AIX, HP-UX, Linux, Solaris, Windows
Software edition: Edition Independent
Reference #: 1665985
Modified date: 27 February 2014