How to identify the Isolation Level of an application?

Content

Use db2exfmt to look at the access plan a query. The access plan shows the current isolation level.

To get the access plan for a query executed from the CLP follow the steps listed:
1) db2 terminate
2) db2 change isolation to <isolation level>
3 )db2 connect to <db>
4) db2 -tvf ~/sqllib/misc/EXPLAIN.DDL
5) db2 set current explain mode explain
6) execute a query
7) db2exfmt -d <dbname> -g TIC -w -1 -n % -s % -# 0 | grep "Isolationl" 8) db2 set current explain mode no.

A sample output is as follows:

db2 terminate
DB20000I The TERMINATE command completed successfully.

db2 change isolation to UR
DB21053W Automatic escalation will occur when you connect to a database that does not support UR.
DB20000I The CHANGE ISOLATION command completed successfully.

db2 connect to sample
Database Connection Information

Database server = DB2/6000 8.1.6
SQL authorization ID = MURALIDS
Local database alias = SAMPLE

db2 -tvf ~/sqllib/misc/EXPLAIN.DDL

******* IMPORTANT **********

USAGE: db2 -tf EXPLAIN.DDL

******* IMPORTANT **********


UPDATE COMMAND OPTIONS USING C OFF
<snip>
COMMIT WORK
DB20000I The SQL command completed successfully.

db2exfmt -d sample -g TIC -w -1 -n % -s % -# 0 | grep "Isolation"
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connect to Database Successful.
Enter outfile name. Default is to terminal ==>
Isolation Level: Uncommitted Read
Executing Connect Reset -- Connect Reset was Successful.