Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    7

    Unanswered: problem about isolation level

    Hi , Could someone please tell me how would I ensure that my database supports a particular isolation level? (I get DB21053W when I change the isolation level using CHANGE ISOLATION command?) Thanks in advance.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: problem about isolation level

    mm .. I know this question (or similar ones ) have been around in this for the last couple of weeks ... But no one has given an answer ...

    Wonder if there is a way ...

    Cheers

    Sathyaram

    Originally posted by SYMBOL
    Hi , Could someone please tell me how would I ensure that my database supports a particular isolation level? (I get DB21053W when I change the isolation level using CHANGE ISOLATION command?) Thanks in advance.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: problem about isolation level

    From the Message Reference:

    DB21053W Automatic escalation will occur when you connect to a
    database that does not support <isolation-level>.

    Explanation: Isolation levels are database dependent. Some,
    like NC, are only supported by specific databases. If you select
    an isolation level that is not supported by the database you are
    connecting to, it will automatically escalate to a supported
    level.

    User Response: Connect to a database that supports the isolation
    level you have selected, or select a different isolation level.



    Note that that message is just a warning.



    From the Command Refernece Manual:

    Changes the way that DB2 isolates data from other processes while a database is being accessed.

    Authorization

    None

    Required Connection

    None

    Command Syntax

    .-CS-.
    >>-CHANGE--+-SQLISL----+--TO--+-NC-+---------------------------><
    '-ISOLATION-' +-RR-+
    +-RS-+
    '-UR-'



    Command Parameters

    TO

    CS
    Specifies cursor stability as the isolation level.

    NC
    Specifies no commit as the isolation level. Not supported by DB2.

    RR
    Specifies repeatable read as the isolation level.

    RS
    Specifies read stability as the isolation level.

    UR
    Specifies uncommitted read as the isolation level.

    Usage Notes

    DB2 uses isolation levels to maintain data integrity in a database. The isolation level defines the degree to which an application process is isolated
    (shielded) from changes made by other concurrently executing application processes.

    If a selected isolation level is not supported by a database, it is automatically escalated to a supported level at connect time.

    Isolation level changes are not permitted while connected to a database with a type 1 connection (see SET CLIENT). The back end process must be
    terminated before isolation level can be changed:


    My guess is that either they are asking for an isolation level that is not supported (in DB2 only NC is not supported), or their connection type is
    1, or that the backend process needs to be terminated.

    Andy

    Originally posted by sathyaram_s
    mm .. I know this question (or similar ones ) have been around in this for the last couple of weeks ... But no one has given an answer ...

    Wonder if there is a way ...

    Cheers

    Sathyaram

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: problem about isolation level

    Andy ... Thanks for the explanation ...


    For example:

    H:\>db2 change isolation to ur
    DB21053W Automatic escalation will occur when you connect to a database that
    does not support UR.
    DB20000I The CHANGE ISOLATION command completed successfully.

    H:\>db2 connect to sample

    Database Connection Information

    Database server = DB2/NT 7.2.1
    SQL authorization ID = xxxxx
    Local database alias = SAMPLE


    Now, I wish to know if the "change isolation " is in effect or in other words, after issuing some statements, I doubt whether I issued the "change isolation" statement or not ... Any ideas of how to check this ? Just like you do "db2 connect" to confirm that you are already connected ..


    Cheers
    Sathyaram






    My guess is that either they are asking for an isolation level that is not supported (in DB2 only NC is not supported), or their connection type is
    1, or that the backend process needs to be terminated.

    Andy [/SIZE][/QUOTE]
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: problem about isolation level

    I searched everywhere I can think of, and I cannot find a way to
    ask DB2 what the current isolation is for the client--bummer.

    Andy

    Originally posted by sathyaram_s
    Andy ... Thanks for the explanation ...


    For example:

    H:\>db2 change isolation to ur
    DB21053W Automatic escalation will occur when you connect to a database that
    does not support UR.
    DB20000I The CHANGE ISOLATION command completed successfully.

    H:\>db2 connect to sample

    Database Connection Information

    Database server = DB2/NT 7.2.1
    SQL authorization ID = xxxxx
    Local database alias = SAMPLE


    Now, I wish to know if the "change isolation " is in effect or in other words, after issuing some statements, I doubt whether I issued the "change isolation" statement or not ... Any ideas of how to check this ? Just like you do "db2 connect" to confirm that you are already connected ..


    Cheers
    Sathyaram






    My guess is that either they are asking for an isolation level that is not supported (in DB2 only NC is not supported), or their connection type is
    1, or that the backend process needs to be terminated.

    Andy
    [/SIZE][/QUOTE]

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: problem about isolation level

    Thanks Andy ...

    Cheers
    Sathyaram
    Originally posted by ARWinner
    I searched everywhere I can think of, and I cannot find a way to
    ask DB2 what the current isolation is for the client--bummer.

    Andy

    [/SIZE][/QUOTE]
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: problem about isolation level

    Originally posted by ARWinner
    I searched everywhere I can think of, and I cannot find a way to
    ask DB2 what the current isolation is for the client--bummer.


    I think that saying "current isolation for the client" is not entirely correct: the isolation level is specific to a package, or to a statement in case of dynamic SQL. This means that you may have successfuly set ISOLATION TO UR but if you type SELECT ... WITH RR, it's going to be RR during the execution of that statement.

    You can see what isolation level was in effect for each statement by looking at the execution plan.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Aug 2003
    Posts
    7

    Re: problem about isolation level

    Thanks in advance!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •