Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: How can I find table access isolation level in SP ?

    Hi,

    DB2 V9.5.5 on Win 2003

    Other than command line explain, how can I see the select statement's table access isolation level ?

    I was able to use command line, still have a doubt.

    Here is the story.

    My developers want to execute all selects at UR level in an SP, default being CS.

    Just for example

    Code:
    CREATE PROCEDURE TESTSP()
    LANGUAGE SQL
    BEGIN
       SET ISOLATION UR;
       insert into testsp SELECT CTIME FROM tests;
       SET ISOLATION RESET;
    END
    @
    I want to see table access isolation level in tabscan in SELECT CTIME FROM tests statement.

    Can some one help me with idea to test this. Using visual explain and/or IBM Data Studio.

    I had used visual explain some times but do not know where the table access level could be found.

    Thanks

    DBFinder
    Last edited by DBFinder; 07-12-10 at 22:39.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can use db2expln to show you this. But you need to know the package name that is generated when your Stored Procedure is created. This is in the catalog. You will need something like this:

    Code:
    select r.routineschema,r.routinename,r.specificname,rd.bname,r.text,p.last_bind_time,p.valid,r.fenced,r.language from syscat.routines as r inner join syscat.routinedep as rd on (r.routineschema = rd.routineschema and r.specificname = rd.routinename) inner join syscat.packages as p on (rd.bschema = p.pkgschema and rd.bname = p.pkgname) where r.routineschema = ? and r.routinetype = 'P' and r.language = 'SQL' and rd.btype = 'K' order by r.routinename
    And then do this:

    Code:
    db2expln -d MyDB  -u MyUser MyPassword -c MySchema -p MyPackageName -g -t
    Andy

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Great, Thanks.

  4. #4
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Is it true that SET ISOLATION does not work inside an SP.

    CREATE PROCEDURE TESTSP()
    LANGUAGE SQL
    BEGIN
    SET ISOLATION UR;
    insert into testsp SELECT CTIME FROM tests;
    SET ISOLATION RESET;
    END
    @
    Regards

    DBFinder

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The documentation says that you can use them, but I think I might have tried it a few years ago and it did not work. You can use the preopts to set the isolation level when you create the SP.

    Andy

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Got it.

    Regards

Posting Permissions

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