Isolation Levels, Locking & Deadlocks
1) What are the default isolation levels of DB2. How do we access this from GUI and command line or is it just a setting in sql.ini file etc.
2) How do we change isolation level for entire db2 server i.e. administrative server db2admin, for a particular instance and a particular database in an instance and transactions while connected to a database.
3) Is it true that by setting lowest isolation level i.e. uncommited read, we reduce chances of unreleased locks and deadlocks. What is value representations for this i.e. UR & 0? for ex.
4) Are there any specific locking & deadlock problems by default in db2. If so, how do we solve them.
5) How do we see the operations performed on a schema/database in db2 in last 1 or 2 days. We should also be able to see from which client(db2/application) that particular transaction was performed from.
2) How do we change isolation level for entire db2 server i.e. administrative server db2admin, for a particular instance and a particular database in an instance and transactions while connected to a database.
3) Is it true that by setting lowest isolation level i.e. uncommited read, we reduce chances of unreleased locks and deadlocks. What is value representations for this i.e. UR & 0? for ex.
4) Are there any specific locking & deadlock problems by default in db2. If so, how do we solve them.
5) How do we see the operations performed on a schema/database in db2 in last 1 or 2 days. We should also be able to see from which client(db2/application) that particular transaction was performed from.
ASKER
ghp7000, thank you. UR - Uncommited read & 0 its corresponding mapping number if one exists. Can you please explain what are cli applications. What about changing isolation level for JDBC applications in Java that are independent or launched from application servers like websphere. How do we see/get the isolation level for cli, sql, java etc.
If you wish to use the uncommited read isolation level, you can actually code your sql statements with 'with UR' at the end to force that characteristic. There are no true locking/deadlock 'problems' to the best of my knowledge...thing is depending on your tables and application usage you may encounter issues. As a general rule of thumb, I have programmers use the With UR if their application is simply retrieving data with no intent to update and precise data integrity is not required. I will go out on a limb and say that the UR & 0 you mentioned is probably a boolean type value of 0 or 1, 0 being off(false).
cli=call level interface, it is what the odbc driver talks to when submitting statements from any application that uses odbc.
db2 update cli cfg for dbname using TXISOLATION <isolationlevel>, is incorrect,
the correct statement should be:
db2 update cli cfg for section TXNISOLATION <isolationlevel>
or just edit the db2cli.ini file directly, it is located in \\sqllib.
to see the isolation level, either use the GUI Tool Event Monitor and monitor for statements belonging to the schema name or use db2audit, or use clp:
db2 update monitor switches using statement on
then execute your statements
then
db2 get snapshot for dynamic sql on <dbname>
db2 update cli cfg for dbname using TXISOLATION <isolationlevel>, is incorrect,
the correct statement should be:
db2 update cli cfg for section TXNISOLATION <isolationlevel>
or just edit the db2cli.ini file directly, it is located in \\sqllib.
to see the isolation level, either use the GUI Tool Event Monitor and monitor for statements belonging to the schema name or use db2audit, or use clp:
db2 update monitor switches using statement on
then execute your statements
then
db2 get snapshot for dynamic sql on <dbname>
ASKER
In db2cli.ini file =>
[ISOLATION LEVEL]
TXNISOLATION=UR
Is this okay or in quotes 'UR'.
db2 update cli cfg for section TXNISOLATION <isolationlevel> => I am getting an unexpected token error for which ever way I do it. Can you send sample working syntax.
Update monitor switches and get snapshot give useful results but not isolation level.
In GUI either at instance or at any given database, right click, Performance monitoring --> Show monitor activity, start default monitor, start monitor, stop monitor exist and I could not locate isolation level anywhere.
By starting default or any other monitor can we monitor in GUI. Can we redirect the monitoring of parameters to a file.
[ISOLATION LEVEL]
TXNISOLATION=UR
Is this okay or in quotes 'UR'.
db2 update cli cfg for section TXNISOLATION <isolationlevel> => I am getting an unexpected token error for which ever way I do it. Can you send sample working syntax.
Update monitor switches and get snapshot give useful results but not isolation level.
In GUI either at instance or at any given database, right click, Performance monitoring --> Show monitor activity, start default monitor, start monitor, stop monitor exist and I could not locate isolation level anywhere.
By starting default or any other monitor can we monitor in GUI. Can we redirect the monitoring of parameters to a file.
By using the client configuration you can set the isolation level...under odbc settings you can update the db2cli.ini that way or use this TXNISOLATION=1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, ghp7000.
2. to change isolation level for cli applications, use :
db2 update cli cfg for dbname using TXISOLATION <isolationlevel>,
For sql use select ... from table with RS
or whatever level you want
3. i dont know what your asking, what does UR & 0 mean?
4. in fact, DB2's locking and isolation level is far more sophisticated than Oracle, much finer granular control, so, no, there are no default problems
5. You have to enable the DB2audit mechanism to see this, enabling the audit program will impact database performance slightly