How can I find table access isolation level in SP ?

DBFinder · #1 (**permalink**) 07-12-10, 21:31

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

ARWinner · #2 (**permalink**) 07-13-10, 08:23

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

DBFinder · #3 (**permalink**) 07-13-10, 08:27

Great, Thanks.

DBFinder · #4 (**permalink**) 07-13-10, 08:40

Is it true that SET ISOLATION does not work inside an SP.

Quote:

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

Regards

DBFinder

ARWinner · #5 (**permalink**) 07-13-10, 10:19

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

DBFinder · #6 (**permalink**) 07-13-10, 10:20

Got it.

Regards

Internet Services

Web Development

Digital Marketing

Consumer Tech