Contents


Determine the effective isolation level in DB2 for Linux, UNIX, and Windows

Have you ever been asked which isolation level is used?

Comments

Whenever a concurrency or locking issue occurs on a database, you might be asked which isolation level a statement effectively uses. You can identify the defined isolation level using the -dynamic option of the db2pd utility, which is shipped with DB2 Version 8. But what is the effective isolation level? For example, you could define read stability (RS) at the connection isolation level, but you are using the WITH UR clause in the statement itself. Will the RS be stronger, or can the statement clause overwrite the connection property? This article demonstrates that the statement level wins as the effective isolation level. This article also describes how to monitor the effective isolation level.

Understanding isolation levels

The ANSI isolation levels are defined to control the behavior of concurrent transactions. The isolation levels have different effects. In DB2 this is established by creating different lock types at runtime. Table 1 shows the phenomena associated with the various isolation levels.

Table 1. DB2 isolation levels and phenomena
ANSI isolation levelDB2 isolation levelDirty readsNon-repeatable readsPhantom reads
SerializableRR (repeatable read)NoNoNo
Repeatable readRS (read stability)NoNoYes
Read committedCS (cursor stability)NoYesYes
Read uncommittedUR (uncommitted read)YesYesYes

An isolation level determines how data is locked or isolated from other processes while the data is being accessed. Following are details about the different isolation levels:

  • UR: Allows an application to access uncommitted changes of other transactions.
  • CS: Locks any row accessed by a transaction of an application while the cursor is positioned on the row. This lock remains in effect until the next row is fetched or the transaction is terminated.
  • RS: Locks only those rows that an application retrieves within a unit of work. It ensures that any qualifying row read during a unit of work is not changed by other application processes until the unit of work completes.
  • RR: Locks all the rows an application references within a unit of work.

The following example explains the phenomena. Two applications are connected to a database. Application 1 (A1) selects rows from a single table in a cursor. Application 2 (A2) changes qualifying rows in the table, and it later commits the changes. Application 1 reopens the cursor.

Following are the possible results of this scenario.

  • Dirty reads: A1 is retrieving uncommitted data of A2
  • Non-repeatable reads: The reopened cursor in A1 retrieves updates of A2 on qualifying rows
  • Phantom reads: The reopened cursor in A1 retrieves inserts of A2 on qualifying rows

The following explanations show how different lock modes in the isolation levels are used to guarantee the described effects. But be aware that there are several circumstances that influences the locking behavior, including the size of the locklist, the optimization level, the access method (table scan, index scan, or block index scan), and the lock duration.

  • When using UR in application A1 on selecting multiple rows, there will be an IN (intent none) table lock on the accessed table. Application A2 inserts a new qualifying row. Application 2 can establish an IX lock on the table and an X row lock, because the table lock IX is compatible to the IN table lock from A1. As long as A2 does not commit or rollback the insert, application A1 is able to reopen the cursor and retrieve the new row. But A2 can afterwards roll back the insert, and then A1 has not retrieved uncommitted data.
  • When using CS in application A1, there will be an IS (intent share) table lock. Again, application A2 inserts a new qualifying row. But A1 cannot reopen the cursor before the insert has been committed without causing a lock-wait situation. This is because the new X row lock and the IS table lock are not compatible. Therefore uncommitted reads are not possible with CS. When A2 commits the insert, because IS and IX table lock mode are compatible, application A1 can reopen the cursor and retrieve the inserted row. So phantom reads are possible with CS.
  • RR does not allow phantom reads by creating an S (share) table lock when selecting rows. An insert creates an IX table lock, which is not compatible with S lock.

Notice that the ANSI isolation levels and DB2 isolation levels have different names, and "repeatable read" has different meanings. The ANSI isolation level names are also used in JDBC. This article uses the typical abbreviations for the DB2 isolation levels:

  • UR is uncommitted read
  • CS is cursor stability
  • RS is read stability
  • RR is repeatable read
  • NC is no commit, and it is supported only for DB2 Connect.

Understanding levels of definition

The levels of definition of the isolation level in an API are shown in Figure 1. It is a hierarchical structure, and each subsequent inner level overrides the preceding outer level.

Figure 1. Overview of levels of definition
Four levels of definition

Following are the levels in detail:

  • The first level is the driver level, which is the collection of driver functions and methods. This isolation level is defined when the driver and its libraries are loaded.
  • The next level is the connection level. Whenever a connection handle is being allocated, an isolation level can be defined. This isolation level is effective when the connection is opened.
  • The next level in DB2 is the package level. If using static SQL, a package is created and can be binded with a specific isolation level. This isolation level is effective when the package is described.
  • The last level is the statement level. Whenever a statement handle is allocated, an isolation level can be defined. This isolation level is effective when the statement is executed.

You can have multiple instances in a specific level. With the exception of the package level, for dynamic SQL you can have multiple connections (in different applications) and define different isolation levels for each connection. In each connection, you can define statements having different isolation levels, as shown in Figure 2.

Figure 2. Instances of definition
Instances of definition

For static SQL, you could create a copy of a module using a different name and bind it with a different isolation level for this new package.

Setting the isolation level

In DB2 the isolation level is always defined at the client. There is no database default isolation level that can be defined on the server side. Instead, if you do not explicitly define an isolation level at the client, DB2 uses CS as the default. DB2 Version 8 introduced the statement clause WITH ISOLEVEL, which can be used to define the isolation level at the statement level. Client level and environment level are synonyms for connection level in this article.

Complete the steps in the following example to set the isolation level at the connection and statement levels for the JDBC application.

  1. Define the isolation level through the connection property setTransactionIsolation at the connection (session) level. Listing 1 shows an example in JDBC to set UR.
    Listing 1. JDBC connection property
    Connection con = DriverManager.getConnection(this.aURL ,this.USER, this.PWD);
    con.setTransactionIsolation( Connection.TRANSACTION_READ_UNCOMMITTED )
  2. Define the isolation level at the statement level by using the WITH clause. Listing 2 shows an example of the Java® code to use UR.
    Listing 2. Statement clause
    sql= "SELECT mandantid, belegnr FROM "+this.SCHEMA+".beleg WHERE cat = ?  WITH UR" 
    PreparedStatement pstmt1 = con.prepareStatement(sql);

Table 2 describes all the methods to define the isolation level in different APIs.

Table 2. Defining connection isolation level
APILevelDescriptionCommand and example
SQLDriverOn the DB2 command line (db2cmd/shell) for the current sessiondb2 "SET CURRENT ISOLATION isolevel
For example: db2 "SET CURRENT ISOLATION RS"
CLIConnectionIn CLI applicationsBy setting the SQL_ATTR_TXN_ISOLATION attribute
JDBC type-4 driverConnectionThrough the setTransactionIsolation method setTransactionIsolation(isolevel_constant)
For example: con.setTransactionIsolation( Connection.TRANSACTION_READ_UNCOMMITTED )
ODBCDriverPer ODBC settingBy using the TXNISOLATION keyword in the db2cli.ini file
For example: db2 "update cli cfg at user level for section TESTDB using TXNISOLATION 4"
SQLJPackageFor SQLJ by the SQLJ profile bind optiondb2sqljbind -url jdbc:db2://host:port/db -bindoptions "ISOLATION isolevel" serialized-profile-name
For example: db2sqljbind -url jdbc:db2://localhost:50001/TESTDB -bindoptions "ISOLATION RS " $@_SJProfile0
Static SQLPackageIn static SQL by a bind optiondb2 "BIND bindfile ISOLATION isolevel"
For example: db2 "BIND client11.bnd ISOLATION RS"
SQL proceduresPackageIn SQL procedures, by a special registry parameterdb2set DB2_SQLROUTINE_PREPOPTS="ISOLATION isolevel"
For example: db2set DB2_SQLROUTINE_PREPOPTS="ISOLATION UR"

Table 3 lists the attribute name and valid values for isolation level attributes in APIs using constants.

Table 3. Isolation level attribute values
CLIJDBC type-4 driverODBC, embedded SQL, Cobol
AttributeTXNISOLATIONsetTransactionIsolation methodSQL_ATTR_TXN_ISOLATION
UR1Connection.TRANSACTION_READ_UNCOMMITTEDSQL_TXN_READ_UNCOMMITTED
CS2Connection.TRANSACTION_READ_COMMITTED>SQL_TXN_READ_COMMITTED
RS4Connection.TRANSACTION_REPEATABLE_READSQL_TXN_REPEATABLE_READ
RR8Connection.TRANSACTION_SERIALIZABLESQL_TXN_SERIALIZABLE

Especially in CLI applications, you can easily define three different levels:

Driver level
You can catalog multiple ODBC data sources, and you can update the TXNISOLATION parameter of each datasource CLI configuration separately.
Connection level
You can explicitly set the attribute SQL_ATTR_TXN_ISOLATION for a connection.
Statement level
In each statement, you can use the WITH clause to define a statement isolation level.

The example in Figure 3 shows two different database aliases: TESTDB and SECOND.

Figure 3. Example of instances
Example of instances
Example of instances

So you can update the CLI configuration of each ODBC data source separately. The applications APPL1 and APPL2 are connecting to the ODBC datasource TESTDB, while APPL3 connects to the ODBC datasource SECOND. The attribute SQL_ATTR_TXN_ISOLATION can be set differently (for example: APPL1 uses 4, and APPL2 uses 2). In each application you can define the isolation level at the statement level by using the WITH clause.

Monitoring the isolation level in DB2

Although the isolation level can be set on different levels, there can be only one effective isolation level. The last-defined level in the level hierarchy is always the effective isolation level. In other words, a statement isolation level overrides a package isolation level, a package isolation level overrides a connection isolation level, and a connection isolation level overrides a driver isolation level.

The following example compares connection and statement levels and shows that the statement level is always the effective one.

The isolation level can be monitored using any of the methods described in Table 4. The Reported level column shows whether the tool retrieves the isolation level at the connection level or at the statement level. This is the most important difference.

Table 4. Available tools
ToolReported levelKeywordValues/FieldsExample
Dynamic sql snapshot (or "db2pd -dyn").Statement levelStatement textClause WITH isolevelWITH UR
Application snapshotConnection levelPackage nameSYSSHxyySYSSH300 (3=RS)
Event monitor for statementsConnection levelPackage nameSYSSHxyySYSSH300 (3=RS)
Dynamic cache (db2pd -dyn)Connection levelDynamic SQL environmentsStmtUID EnvID Iso11 1 RR
Effective isolation level (db2pd -act)Statement levelActive statement list:StmtUID EffISO11 0
Workload manager (WLM) functions and event monitorsStatement levelSTMT_ISOLATION0..41 (=UR)

Note that the coding of the isolation levels in the tools differs in the values and ranges as follows:

  • Package names are in the format SYSSHxyy, SYSSNxyy, SYSLHxyy, or SYSLNxyy. x is the isolation level. 0=NC, 1=UR, 2=CS, 3=RS, and 4=RR.
  • The environment or session level in db2pd -dynamic is reported as UR, CS, RS, and RR.
  • The effective isolation level in db2pd -active and the table function WLM_GET_ACTIVITY_DETAILS is 0=RR, 1=CS, 2=UR, and 3=RS.
  • The effective isolation level of the statement isolation monitor element is 0=no isolation level specified, 1=UR, 2=CS, 3=RS, and 4=RR.

Table 5 summarizes the coding of the isolation levels of the different tools. N/A means it is not specified, and NC means No Commit, which is supported only for connecting to iSeries®. You can see that the coding in EffIso of db2pd and in EFFECTIVE_ISOLATION of WLM_GET_ACTIVITY_DETAILS are the most confusing ones. The other coding is predictable.

Table 5. Isolation level coding
Tool01234
Package nameNCURCSRSRR
db2pd EffIsoRRCSURRS-
EFFECTIVE_ISOLATION (WLM_GET_ACTIVITY_DETAILS)RRCSURRS-
STMT_ISOLATIONNONEURCSRSRR

The following list describes the usage of the tools.

  • Check the statement text in the dynamic SQL snapshot for a WITH clause in the field Statement text. This is a statement isolation level. In the example, WITH UR is used to set uncommitted read, as shown in Listing 3.
    Listing 3. Dynamic SQL snapshot
    Comamnd:
    db2 get snapshot for dynamic sql on dbname
    Example: db2 get snapshot for dynamic sql on TESTDB Output: Number of executions = 1 ... Statement text = SELECT mandantid, belegnr FROM DB2ADMIN.beleg WHERE cat = ? WITH UR
  • Check the package name in an application snapshot for a specific application handle. In the statement section, the package name contains a code for the connection isolation level. In the example, the package name reports RS (the code 3), while the statement uses the WITH UR clause. The package name reports the connection isolation level, as shown in Listing 4, but that is not the effective isolation level.
    Listing 4. Application snapshot
    Comamnd:
    db2 get snapshot for applications on dbname
    Example: db2 get snapshot for applications on TESTDB Output: Application handle = 471 ... Statement type = Dynamic SQL Statement ... Package name = SYSSH300 ... Dynamic SQL statement text: SELECT mandantid, belegnr FROM DB2ADMIN.beleg WHERE cat IN ( 1, 2 ) WITH UR
  • Check the package name in an event monitor for a specific application handle. In the statement section for any operation, such as PREPARE, the package name contains a code for the connection isolation level. In the example, the package name reports RS (code 3), as shown in Listing 5, which is the same as the application snapshot in Listing 4.
    Listing 5. Event monitor
    Comamnd:
    db2 CONNECT TO  dbname 
    db2 CREATE EVENT MONITOR eventmonitor for STATEMENTS WRITE TO FILE 'path' 
    db2evmon -db  dbname -evm  eventmonitor > file
    Example: db2 CONNECT TO TESTDB db2 CREATE EVENT MONITOR evmon01 FOR STATEMENTS WRITE TO FILE 'C:\db\data\event\' db2evmon -db TESTDB -evm evmon01 > evmon01.out Output: Type : Dynamic Operation: Prepare Package : SYSSH300 ... Text : SELECT mandantid, belegnr FROM DB2ADMIN.beleg WHERE cat = ? WITH UR
  • When retrieving the dynamic cache by db2pd (using the -dynamic option), check the settings for a statement in the environment section. The statement text is reported in the statement section. The link between these sections can be established using the identifiers AnchID and StmtUID. The environment reports the connection isolation level (in the example this is RR), while the statement uses the WITH UR clause, as shown in Listing 6.
    Listing 6. Dynamic cache
    Comamnd:
    db2pd -db dbname  -dynamic -file  file
    Example: db2pd -db TESTDB -dynamic -file db2pd_dyn.out Output: ... Dynamic SQL Statements: AnchID StmtUID NumEnv Text 96 1 1 SELECT mandantid, belegnr FROM DB2ADMIN.beleg WHERE cat = ? WITH UR ... Dynamic SQL Environments: AnchID StmtUID EnvID Iso QOpt Blk 96 1 1 RS 5 B
  • To retrieve the effective isolation level, run db2pd using the -active option. Note that this is the only monitor element in DB2 named effective isolation level. Check the value of EffISO in the section Active Statement List. It reports the statement isolation level. In the example, the statement isolation level is 2, which corresponds to UR, as shown in Listing 7.
    Listing 7. Active statement list
    Comamnd:
    db2pd -db TESTDB -active -file db2pd_act.out
    Output:
    Active Statement List:
    AppHandl UOW-ID     StmtID     AnchID StmtUID    EffISO
    30       1          1          96     1          2
  • You can use the Workload Manager (WLM) functions and event monitors starting in DB2 9.5. After setting up an event monitor for activities, you can retrieve the column STMT_ISOLATION from the event monitor tables. The column reports the statement isolation level. You first have to create the specific event monitor and to alter the default service class SYSDEFAULTSUBCLASS to collect activity details, as shown in Listing 8.
    Listing 8. WLM
    CREATE TABLESPACE monitor IN DATABASE PARTITION GROUP IBMDEFAULTGROUP ;
    CREATE EVENT MONITOR WLM_EVENT FOR ACTIVITIES WRITE TO TABLE
    	ACTIVITY (TABLE WLM_EVENT IN MONITOR),
    	ACTIVITYSTMT (TABLE WLM_EVENT_STMT IN MONITOR) AUTOSTART;
    SET EVENT MONITOR WLM_EVENT STATE 1 ;
    ALTER SERVICE CLASS SYSDEFAULTSUBCLASS UNDER SYSDEFAULTUSERCLASS 
     COLLECT ACTIVITY DATA ON ALL DATABASE PARTITIONS WITH DETAILS
     COLLECT AGGREGATE ACTIVITY DATA EXTENDED

    Then you can retrieve the value of STMT_ISOLATION from the event monitor table as the statement isolation level. In the example, the value 1 is retrieved in STMT_ISOLATION, which corresponds to UR, as shown in Listing 9
    Listing 9. WLM select
    Command:
    select STMT_ISOLATION  , STMT_FIRST_USE_TIME , substr(STMT_TEXT,1,100) AS TEXT
    from  DB2ADMIN.WLM_EVENT_STMT 
    Output:
    STMT_ISOLATION       STMT_FIRST_USE_TIME        TEXT
    -------------------- -------------------------- --------------------------
                       1 2011-04-30-12.32.21.031475 SELECT mandantid, belegnr 
    FROM DB2ADMIN.beleg WHERE cat = ?  WITH UR

    Instead of using the default service class, you can define a specific WLM setup for capturing the statements, such as a new service class and workload, that references only the APPLNAME('db2jcc_application') for the JDBC application.

Setting up the testcase environment

All test cases are run on DB2 9.5 on a database called TESTDB. A huge table called BELEG is created containing different ranges on column CAT. Select statements run about 5 seconds to be able to check the active statement db2pd tool.

The example uses a simple Java application that runs per JDBC type 4 against the local database TESTDB. Runtime attributes are defined in a properties file. This enables changing the isolation level at the connection and statement levels without recompiling the code. Listing 10 shows example content of Client01.properties. The property ISOLEVEL defines the connection isolation level (in the example, RS). The property STMTISOLEVEL defines the statement isolation level (in the example, UR).

Listing 10. Example content of properties file
DRIVER=com.ibm.db2.jcc.DB2Driver
URL=jdbc:db2:
SERVER=localhost
PORT=50001
ALIAS=TESTDB
USER=db2admin
PWD=ibmswg001
SCHEMA=DB2ADMIN
ISOLEVEL=RS
STMTISOLEVEL=UR

The Java application Client01.class needs the name of a valid properties file as a mandatory argument, as shown in Listing 11. The option -a is for silent mode and fetches all rows without prompting.

Listing 11. Usage of Java application Client01.class
java Client01 Client01.properties -a

Exploring Testcase (1): RS at the connection level, UR at the statement level

In this test case, you will define RS at the connection isolation level and UR at the statement isolation level (using the clause WITH UR), as shown in Listing 12. You will also retrieve UR as the effective isolation level.

Listing 12. Content of properties file for connection and statement level
ISOLEVEL=RS
STMTISOLEVEL=UR

Listing 13 shows a typical output, which displays settings and return code of the commands:

Listing 13. Running Client01.class
DB295>java Client01 Client01.properties -a
Starting Client01

###INFO: Properties loaded from file Client01.properties
###INFO: Connection string= jdbc:db2://localhost:50001/TESTDB,db2admin,***
###INFO: EnterMode= Non Single Row.
###INFO: Connection level= RS
###INFO: Statement  level= UR
###INFO: Positioned Update= true
###INFO: Running...  SELECT mandantid, belegnr FROM DB2ADMIN.beleg WHERE cat = ?  WITH UR
###INFO: Startdate= Sun May 01 12:23:04 CEST 2011

###INFO: Enddate= Sat Apr 30 12:32:26 CEST 2011
ResultSet Size = 49793

RESULT: Test ran successfully in 5687 ms.
Press ENTER ...
DB295>

The output of db2pd -d TESTDB -active reports the value 2, which corresponds to UR.

Listing 14. db2pd -active output
Database Partition 0 -- Database TESTDB -- Active -- Up 1 days 00:52:02 
Active Statement List:
Address    AppHandlUOW-ID  StmtID     AnchID StmtUID    EffISO      EffLockTOut EffDegree
RefTime
0x79117F50 298      1      1          96     1          2           -1          0

Check the locks to validate the effective isolation level as you can directly see the difference in the lock modes. The active lock is a table lock on BELEG with mode IN, which corresponds to UR, as shown in Listing 15.

Listing 15. db2pd -db TESTDB -lock showlocks: Lock mode is as UR
Address    Type       Mode Sts Owner      Dur HoldCount  
0x7E1B0300 Table      .IN  G   9          2   2          TbspaceID 3     TableID 20

db2pd displays the statement text containing the WITH UR clause. The isolation level of the connection is RS, as you defined it in the properties file, as shown in Listing 16.

Listing 16. db2pd -dynamic output
Dynamic SQL Statements:
AnchID StmtUID    NumEnv     Text
96     1          SELECT mandantid, belegnr
    FROM DB2ADMIN.beleg WHERE cat = ?  WITH UR
Dynamic SQL Environments:
AnchID StmtUID    EnvID      Iso QOpt Blk
96     1          1          RS  5    B

The application snapshot reports the connection isolation level RS (3), but you can also see the statement text containing the WITH UR clause, as shown in Listing 17.

Listing 17. Application snapshot
Statement type                             = Dynamic SQL Statement
Statement                                  = Fetch
...
Package name                        = SYSSH300
...
Dynamic SQL statement text:
SELECT mandantid, belegnr FROM DB2ADMIN.beleg WHERE cat = ?  WITH UR

The formatted statement event monitor output also reports in the PREPARE operation the connection isolation level RS (3), but you can also see the statement text containing the WITH UR clause, as shown in Listing 18.

Listing 18. Statement event monitor
  Type     : Dynamic
  Operation: Prepare
  Section  : 1
  Creator  : NULLID  
  Package  : SYSSH300
  Consistency Token  : SYSLVL01
  Package Version ID  : 
  Cursor   : SQL_CURSH300C1
  Cursor was blocking: FALSE
  Text     : SELECT mandantid, belegnr FROM DB2ADMIN.beleg WHERE cat = ?  WITH UR

The select on the Workload Manager event monitor table for statements retrieves 1 for UR as the statement isolation level, which you can also identify in the statement text, as shown in Listing 19.

Listing 19. WLM event monitor table content
select STMT_ISOLATION  , STMT_FIRST_USE_TIME , substr(STMT_TEXT,1,100) AS TEXT
from  DB2ADMIN.WLM_EVENT_STMT 
Output:
STMT_ISOLATION       STMT_FIRST_USE_TIME        TEXT
-------------------- -------------------------- --------------------------
                   1 2011-04-30-12.32.21.031475 SELECT mandantid, belegnr 
FROM DB2ADMIN.beleg WHERE cat = ?  WITH UR

Conclusion of test case 1: The effective isolation level reported by the tools in this case is UR, which is the statement isolation level.

Exploring Testcase (2): CS at the connection level, RS at the statement level

In this test case, you define CS at the connection isolation level and RS at the statement isolation level using the clause WITH RS, as shown in Listing 20. You also retrieve RS as the effective isolation level.

Listing 20. RS over CS
ISOLEVEL=CS
STMTISOLEVEL=RS

The output of db2pd -d TESTDB -active reports the value 3, which corresponds to RS, as shown in Listing 21.

Listing 21. db2pd -active output
Database Partition 0 -- Database TESTDB -- Active -- Up 1 days 01:11:30
Active Statement List:
Address    AppHandlUOW-ID  StmtID     AnchID StmtUID    EffISO      EffLockTOut EffDegree
RefTime
0x79118280 312      1      1          201     1          3           -1          0

db2pd displays the statement text containing the WITH RS clause. The isolation level of the connection is CS as defined in the properties file, as shown in Listing 22.

Listing 22. db2pd -dynamic output
Dynamic SQL Statements:
AnchID StmtUID    NumEnv     Text
201    1          1          SELECT mandantid, belegnr
    FROM DB2ADMIN.beleg WHERE cat = ?  WITH RS
Dynamic SQL Environments:
AnchID StmtUID    EnvID      Iso QOpt Blk
201    1          1          CS  5    B

The formatted statement event monitor output also reports the connection isolation level CS (2), but you can also see the statement text containing the WITH RS clause, as shown in Listing 23.

Listing 23. Statement event monitor
  Type     : Dynamic
  Operation: Prepare
  Section  : 1
  Creator  : NULLID  
  Package  : SYSSH200
  Consistency Token  : SYSLVL01
  Package Version ID  : 
  Cursor   : SQL_CURSH200C1
  Cursor was blocking: FALSE
  Text     : SELECT mandantid, belegnr FROM DB2ADMIN.beleg WHERE cat = ?  WITH RS

The select on the Workload Manager event monitor table for statements retrieves 3 for RS as the statement isolation level, which you can also identify in the statement text, as shown in Listing 24.

Listing 24. WLM event monitor table content
select STMT_ISOLATION  , STMT_FIRST_USE_TIME , substr(STMT_TEXT,1,100) AS TEXT
from  DB2ADMIN.WLM_EVENT_STMT 
Output:
STMT_ISOLATION       STMT_FIRST_USE_TIME        TEXT
-------------------- -------------------------- --------------------------
                   3 2011-04-30-12.34.21.066473 SELECT mandantid, belegnr 
FROM DB2ADMIN.beleg WHERE cat = ?  WITH RS

Conclusion of test case (2): The effective isolation level reported by the tools in this case is RS, which is the statement isolation level.

Monitoring static SQL

In DB2, static SQL is supported and results in the creation of packages. Packages have several bind options, one of which is the isolation level. Once a package is created, the isolation level cannot be changed without recompiling. Static SQL is used in embedded SQL, Java SQLJ, and when creating SQL procedures.

Check the tools on static SQL by creating a SQLJ Java application. The package name is ClientS1. Define RS for the package. The isolation level of the package can be retrieved from the catalog, as shown in Listing 25.

Listing 25. Isolation level package ClientS1
SELECT isolation FROM syscat.packages WHERE pkgname = 'ClientS1'
ISOLATION
---------
RS

The code contains this statement using UR as the statement isolation level, as shown in Listing 26.

Listing 26. SQLJ SQL code
#sql cs1 = { SELECT mandantid FROM db2admin.beleg WHERE cat = 1 WITH UR };

To display the isolation level of packages the -static option in db2pd is useful. It shows RS as the package isolation level, as shown in Listing 27.

Listing 27. db2pd -static output
Packages:
Schema   PkgName  NumSec UseCount   NumRef     Iso QOpt  Blk 
DB2ADMIN ClientS1 1      0          1          RS  5     B

The db2pd -active option does not really help when using static SQL, because the statement identifier is always 0. Instead, use WLM monitoring. The select on the Workload Manager event monitor table for statements retrieves 1 for UR as the statement isolation level, which you can also identify in the statement text, as shown in Listing 28. Note that the statement is reported as a cursor definition, because it is the section of a package.

Listing 28. WLM event monitor table content
select STMT_ISOLATION  , substr(STMT_TEXT,1,100) AS TEXT
from  DB2ADMIN.WLM_EVENT_STMT 
Output:
STMT_ISOLATION       TEXT
-------------------- --------------------------
                   1 DECLARE DB2JCCCURSOR1 CURSOR FOR SELECT mandantid 
  FROM db2admin.beleg WHERE cat = 1 WITH UR

Conclusion to static SQL example: The effective isolation level reported by the tools is UR, which is the statement isolation level.

Exploring additional concepts

  • When you run the same statement with different connection isolation levels in sequence, db2pd -dynamic reports different environment identifiers (EnvID) for this statement (referencing the different connection isolation levels). In the example, RS and RR are used as the connection isolation level. The column AnchID is 127, but you can see two environment entries for this ID, as shown in Listing 29.
    Listing 29. db2pd -dynamic output when using different connection isolation levels
    Dynamic SQL Statements:
    AnchID StmtUID    NumEnv     NumExe Text 
    127    1          2               2          SELECT mandantid, belegnr
        FROM DB2ADMIN.beleg WHERE cat = ?
    Dynamic SQL Environments:
    AnchID StmtUID    EnvID      Iso QOpt Blk
    127    1          2          RR  5    B
    127    1          1          RS  5    B
  • When you run multiple applications in parallel with exactly the same statement text, db2pd -dynamic reports a single statement and a single environment. But the column NumExe is increased as it reports the number of executions of the statement. In the example, call the statement three times, as shown in Listing 30.
    Listing 30. db2pd -dynamic output on same statement
    Dynamic SQL Statements:
    AnchID StmtUID    NumEnv     NumVar  NumRef   NumExe Text 
    96     1          1          1       1        3      SELECT mandantid, belegnr
        FROM DB2ADMIN.beleg WHERE cat = ?  WITH RS
    Dynamic SQL Environments:
    AnchID StmtUID    EnvID      Iso QOpt Blk
    96     1          2           CS  5    B
  • When you run multiple applications in parallel using different statement isolation levels, db2pd -dynamic reports different statement identifiers (AnchID). The statement text differs, because the WITH clause is part of the SQL text, as shown in Listing 31.
    Listing 31. db2pd -dynamic output on different statement isolation levels
    Dynamic SQL Statements:
    AnchID StmtUID    NumEnv     NumVar  NumRef   NumExe Text 
    96     1          1          1          1          1 SELECT mandantid, belegnr
        FROM DB2ADMIN.beleg WHERE cat = ?   WITH UR
    201 1 1 1 1 1 SELECT mandantid, belegnr FROM DB2ADMIN.beleg WHERE cat = ? WITH RS Dynamic SQL Environments: AnchID StmtUID EnvID Iso QOpt Blk 96 1 1 CS 5 B 201 1 1 CS 5 B
  • If you use different statement isolation levels in a single transaction, there is no effect. The statements work as described here; each statement isolation level is the effective one.
  • Instead of monitoring the isolation levels using the tools described in this article, you can use tracing facilities to check the settings. For example on a CLI application, you can activate a CLI trace. There you will see the values of TXNISOLATION and SQL_ATTR_TXN_ISOLATION if used. Listing 32 shows an example trace output in which the driver and connection isolation levels are defined. For .NET you can activate a .NET trace, and for JDBC you can activate a JCC trace to capture metadata details in the traces.
    Listing 32. Content of CLI trace from CLI application
    SQLConnect( )
        <--- SQL_SUCCESS
    ( DSN=""TESTDB"" )
    ( UID=""db2admin"" )
    ( PWD="" )
    ( DBALIAS="TESTDB" )
    ( TXNISOLATION="1" )
    SQLSetConnectAttr( hDbc=0:1, fOption=SQL_ATTR_TXN_ISOLATION, pvParam=&00000004)
    Listing 33 shows a jcc trace example.
    Listing 33. Content of jcc trace from Java application
    [jcc] Default isolation: 2
    ...
    [jcc][Time:...][Thread:main][Connection@1ae81ae8] setTransactionIsolation (4) called

Conclusion

This article illustrated the following key concepts:

  • Short transactions cannot be captured by the db2pd -active tool, so this is only useful for long running transactions.
  • db2pd -dynamic always reports the connection isolation level, not the effective one. db2pd -static for static SQL displays the package isolation level (the bind option of the package).
  • The WLM event monitor is always a proper method.
  • In summary, valid methods to retrieve the effective isolation levels include:
    • db2pd -dynamic always reports the connection isolation level.
    • db2pd -active can be used for long running statements and transactions to capture the effective isolation level.
    • The use of Workload Manager (WLM) functions or event monitor is feasible for short transactions and for having a complete history of activities. It always reports the effective isolation level.

In comparing the connection isolation level and statement isolation level (which are the most commonly used levels), the article explains that the statement isolation level is the effective isolation level. Generally the effective isolation level is the isolation level used at runtime. Basic rules are:

  • The isolation level is always set at the client.
  • The isolation level can be set at the client at different levels (driver, connection, package, and statement level). If none is defined, the default at the client is CS.
  • The isolation level is overwritten from the outer to the inner level (see Figure 1). The innermost isolation level is the effective isolation level.

Acknowledgment

I would like to acknowledge Andreas Weininger and Dirk Fechner for reviewing this article.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=741976
ArticleTitle=Determine the effective isolation level in DB2 for Linux, UNIX, and Windows
publish-date=07212011