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.
