Is there some way to track the isolation level of an indivual SQL
statement submitted via JDBC in a snaphot or some other similar means?
I have JDBC programs that are changing the isolation level via
Connection.setTransactionIsolation(int level) method, such as
TRANSACTION_SERIALIZABLE or TRANSACTION_REPEATABLE_READ and I need to
verify that in DB2 at a SQL statement level. I don't see isolation
level in a snapshot for Dynamic SQL.

m0002a@yahoo.com wrote:
[color=blue]
> Is there some way to track the isolation level of an indivual SQL
> statement submitted via JDBC in a snaphot or some other similar means?
> I have JDBC programs that are changing the isolation level via
> Connection.setTransactionIsolation(int level) method, such as
> TRANSACTION_SERIALIZABLE or TRANSACTION_REPEATABLE_READ and I need to
> verify that in DB2 at a SQL statement level. I don't see isolation
> level in a snapshot for Dynamic SQL.[/color]

I'm not sure if this is what you're looking for, but the CURRENT
ISOLATION register might help you here. Take a look at

http://publib.boulder.ibm.com/infoce...om.ibm.db2.udb
..doc/admin/r0010945.htm

for more information (if that link doesn't work it's under Reference /
SQL / Language elements / Special registers / CURRENT ISOLATION in the
DB2 Info Center).

For example, VALUES CURRENT ISOLATION returns the value of the CURRENT
ISOLATION special register (usually this is blank, indicating that the
default transaction isolation level for the package / session is in
use, unless it's been changed with SET CURRENT ISOLATION).

I'm not sure how the setTransactionIsolation method alters the
transaction isolation level so this might not be any help, but it's
worth a shot.

HTH,

Dave.
--

Dave Hughes wrote:[color=blue]
>
> I'm not sure if this is what you're looking for, but the CURRENT
> ISOLATION register might help you here. Take a look at
>
> http://publib.boulder.ibm.com/infoce...om.ibm.db2.udb
> .doc/admin/r0010945.htm
>
> for more information (if that link doesn't work it's under Reference /
> SQL / Language elements / Special registers / CURRENT ISOLATION in the
> DB2 Info Center).
>
> For example, VALUES CURRENT ISOLATION returns the value of the CURRENT
> ISOLATION special register (usually this is blank, indicating that the
> default transaction isolation level for the package / session is in
> use, unless it's been changed with SET CURRENT ISOLATION).
>
> I'm not sure how the setTransactionIsolation method alters the
> transaction isolation level so this might not be any help, but it's
> worth a shot.
>
> HTH,
>
> Dave.
> --[/color]

That is not what I am looking for. I need to see the actual isolation
level of each individual dynamic SQL statement, which can be overridden
for a given connection by "WITH RR" or other means. I also cannot
modify the code.

I ran:
db2pd -database sample -dynamic
and got some interesting output. In one section it lists the SQL
statements in package cache:

Dynamic SQL Statements:
Address AnchID StmtUID NumEnv NumVar NumRef NumExe
Text

0x40A120D0 3 1 1 1 112261 112261
SELECT JOB_LISTENER FROM QRTZ_JOB_LISTENERS WHERE JOB_NAME = ? AND
JOB_GROUP = ?

0x409EB400 10 1 1 1 49902 49902
DELETE FROM QRTZ_FIRED_TRIGGERS WHERE INSTANCE_NAME = ?

and also in another section of the report:

Dynamic SQL Environments:
Address AnchID StmtUID EnvID Iso QOpt Blk
0x40A121C0 3 1 1 RR 5 B
0x409EB4E0 10 1 1 RR 5 B
0x409AB420 22 1 1 CS 5 B
0x409ABA50 22 1 2 CS 5 B

But I cannot match up the hex addresses of the SQL statements with the
hex addresses of the Iso (isolation level). (The above is only a
partial list of the actual output).

I just figured it out. The AnchID's match up, not the hex addresses. So
now I can determine the actual isolation level of all the SQL
statements in package cache.

But I don't get this:

Address AnchID StmtUID NumEnv NumVar NumRef NumExe
Text
0x151223D0 195 1 1 1 3 1
SELECT COUNT(*) FROM t WITH
UR
....
Address AnchID StmtUID EnvID Iso QOpt Blk
0x15122490 195 1 1 CS 5 B
....

So, as you see, I ran a simple query with UR isolation explicitly
specified but db2pd indicated it was run using CS level.
Any comment?

-Eugene

Can you open an PMR with DB2 support and report the answer here?