How do I determine the default isolation level for our 11.5 DB2 instance running on Linux?
I see there is a "special register" from this IBM documentation, however I'm not a DB2 expert so I'm not sure where to look for that, or how to "see" values for "special" registers.
I also looked at this, but I am having no luck understanding how to obtain the current isolation from that either.
In Db2 the isolation level is specified at the statement or package (=a precompiled group of statements) level, not at the database or instance level. If not explicitly specified as part of a statement (...WITH <isolation level) or via the register that you have discovered, or by other means, it'll be CS (cursor stability). The behaviour of the CS level is controlled by the database configuration parameter cur_commit.
You can see the value of the CURRENT ISOLATION register by issuing VALUES CURRENT ISOLATION, and that will determine how the next statement in this session will be compiled/prepared. However, if you call a stored procedure in that same session, the statements in it might run with the isolation level specified (in effect) at the SP creation time.
You can see the isolation level for each statement in the package cache using MON_GET_PKG_CACHE_STMT().
You can see the current isolation level of each in-flight transaction via MON_GET_UNIT_OF_WORK().
