DB2 table is locked by Cognos report

[cogtrack]

I created data source connection(DB2), and specified Isolation level to UR, DBA told me that my report still hold lock on the table in DB2 database, it prevented other users from running.
Can anyone help on this, how to avoid table lock?
thank you in advance.

[RobsWalker68]

Hi,

Not sure what UR level means in DB2 but the very general rules on any relational database are:

1. If your report is reading data then other users will also be able to read data
2. If your report is reading data then users who are performing updates, inserts and deletes will wait until your read has completed.

There are exceptions with row locking but the general rules remain especially if selecting large datasets of data

If the data sets you are reading are having an impact on the operational system then you should consider the development of a seperate reporting database and use this to report from.

If you selecting a relatively small dataset then you should check with the DBA that the appropriate indexes have been defined so that you are not escalating to a table lock

Rgds

Rob

[MFGF]

Spookily enough, I had this conversation with a friend (and knowledgable poster in these forums) very recently. The consensus seemed to be that setting the Transaction Isolation Level to "Read Uncommitted" in the properties of your data source in Cognos Administration will prevent the dreaded DB2 deadlocking issues. From her findings, it's worth a try, I would suggest?

Cheers!

MF.

[cogtrack]

Thank you for your help, I already set to UR in cognos admin page, but the report still hold lock on table.  not sure how the UR works, the report(comlicated bursting report) takes about 15 minutes to complete, DBA found other job can't access the table.

[kravguy]

Did you ever get this issue resolved?  We are experiencing a similar issue where DB2 table is getting locked by Cognos which in turn prevents a BATCH job from updating the table.  Cognos has seemingly finished "reading" the table however its lock still remains.  My DBA and Cognos Admin skills do not go very deep so I am here searching for answers. 

Thanks