> 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?
[quoted text clipped - 3 lines]
> verify that in DB2 at a SQL statement level. I don't see isolation
> level in a snapshot for Dynamic SQL.
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/infocenter/db2luw/v8/topic/com.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.
m0002a@yahoo.com - 11 Mar 2006 00:43 GMT
> 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
[quoted text clipped - 19 lines]
> Dave.
> --
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).
m0002a@yahoo.com - 11 Mar 2006 00:53 GMT
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.
Eugene F - 13 Mar 2006 21:37 GMT
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
m0002a@yahoo.com - 13 Mar 2006 22:32 GMT
Can you open an PMR with DB2 support and report the answer here?