Db2

 View Only

  • 1.  Get session issolation level from the server side

    Posted Fri September 15, 2023 05:26 AM

    Hello,

    Is it possible to get an isolation level of a specific session, but from the server side? I know it can be changed just for a specific query (using e.g. WITH UR with the query), but I am looking for the default settings that can be defined e.g. in a JDBC connection string. I want to check what is the isolation level for specific connections causing issues, but I am not able to access their application server and it takes a long to get an answer from them.

    I tried db2pd, but I don't see there anything, that could identify the default isolation level for the specific session/connection. 

    Does there exist a way to find that?

    Thank you



    ------------------------------
    Ondřej Žižka
    ------------------------------


  • 2.  RE: Get session issolation level from the server side
    Best Answer

    IBM Champion
    Posted Fri September 15, 2023 06:26 AM
    Edited by Lorraine Rizzuto Mon September 25, 2023 09:52 AM

    1. Until a SET CURRENT ISOLATION statement is issued within a session, or after RESET has been specified for SET CURRENT ISOLATION, the CURRENT ISOLATION special register is set to blanks and is not applied to dynamic SQL statements; the isolation level used is taken from the isolation attribute of the package which issued the dynamic SQL statement.
    2. Default isolation level is CS.
    3. For isolation attribute of the package check column ISOLATION of the SYSCAT.PACKAGES view.

    You may want to look at DB2_DEFAULT_ISOLATION_VALUE registry variable and also:

    DB2_BYPASS_DEFAULT_ISOLATION_APPS
    DB2_BYPASS_DEFAULT_ISOLATION_GROUPS
    DB2_BYPASS_DEFAULT_ISOLATION_USERS


    ------------------------------
    Jan Nelken
    ------------------------------

    Original Message


  • 3.  RE: Get session issolation level from the server side

    Posted Mon September 18, 2023 01:30 AM

    Thank you, Jan.



    ------------------------------
    Ondřej Žižka
    ------------------------------

    Original Message


  • 4.  RE: Get session issolation level from the server side

    Posted Mon September 18, 2023 10:59 AM

    Isolation level is per query/statement - you can capture that using a Statement Event Monitor.



    ------------------------------
    Lars
    ------------------------------

    Original Message


  • 5.  RE: Get session issolation level from the server side

    Posted Tue October 03, 2023 09:55 AM

    Hello.

    If isolation level has been set in a session (for example, with the `SET ISOLATION` statement or with the corresponding connection property setting), then you may get it using the current_isolation monitor element:

    SELECT CURRENT_ISOLATION 
    FROM TABLE (MON_GET_CONNECTION (<your_app_handle>, -2));



    ------------------------------
    Mark Barinstein
    ------------------------------

    Original Message