Problem Statement: Cognos Locks the database tables.

Problem Statement: Cognos Locks the database tables.

Transaction Isolation Property.

Depending on how you use Cognos, you want to have it access your database in different ways.  This access affects how Cognos interacts with other process accessing and modifying your database.  For an overview of what Isolation Levels in Cognos means for various database providers, A very useful document by IBM l document here. This document from  IBM describes the level to be set for different database (ex Informix,oracle,DB2 etc). 

1.Read Uncommitted

Changes made by other transactions are immediately available to a transaction.

2.Read Committed

A transaction can access only rows committed by other transactions.

3.Cursor Stability

Other transactions cannot update the row in which a transaction is positioned.

4.Reproducible Read

Rows selected or updated by a transaction cannot be changed by another transaction until the transaction is complete.

5.Phantom Protection

A transaction cannot access rows inserted or deleted since the start of the transaction.

6.Serializable

A set of transactions executed concurrently produces the same result as if they were performed sequentially.

The Lowest level for SQL Server is Read Uncommitted. So if you want your database tables not to be affected by the reports running, set the Isolation level property to Read Uncommitted.

For our example we will consider having SQL Server as the Database and Isolation level set to Read Uncommitted. This means Cognos will read the database without creating Locks and ignoring all Locks in place.

What is a lock?

This goes by the literal meaning. A database Lock works just like a Drawer lock or the lock you put up at your house when you got to the office.  I go to office and lock my house, now my roommate wants to open the house and keys are with me, he cannot get inside unless I go and unlock, for instance no one can get inside my house unless they have keys or I go back and open the lock. This is good for me considering the fact that my stuff is safe at home.

Read Uncommitted doesn't add a lock. I don’t lock my house means others can mess with it, anyone irrespective of being my friend/roommate. Vulnerable for my house and potentially bad for the database. But since Cognos is Read Only anyway, I'm fine if someone changes the database (people are updating a record) while I'm running a report. Also if my report hooks up, I don't leave locks behind

Read Uncommitted ignores all Locks, which means if someone else is working on a row before I run a report, I can still read it to display.  Once in a while that will get me wrong or weird results, but it means I don't have to worry about people doing their work at the database or ETL level. Since Cognos isn't writing, it's not worrying so much about it being different the next time it gets to that row.  It will fire the same SQL statement the next time, even if the row is updated by any process.

 Follow steps below to set Isolation property

·         Launch Cognos Administration

·         Click on the Configuration Tab

·         Click the name of the Data Source you want to edit

·         Click on the Properties icon (to the right) of the Connection in the Data Source

·         Click the Connection Tab

·         Under Isolation Level, click the Radio Button by "Specify a Value" and use the drop down to select the value that works best for you.

·         Click OK

New Connections to Cognos will use the new Isolation Level.  You can force this by restarting the Cognos Services.  This can also be done from Framework Manager Level.