By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,237 Members | 1,242 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,237 IT Pros & Developers. It's quick & easy.

"WITH UR" in a UDF

P: n/a


Why wouldn't the "WITH UR" SQL construct work in a UDF?

Is the expectation to set it from the calling SQL?

Oct 2 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
ts******@gmail.com wrote:
Why wouldn't the "WITH UR" SQL construct work in a UDF?

Is the expectation to set it from the calling SQL?
WITH UR only works for a SELECT statement, and it should work in a UDF.

Oct 2 '06 #2

P: n/a
Try it. It does not work.

(that said, I'm on DB21085I Instance "nyemdi02" uses "64" bits and DB2
code release "SQL08023"
with level identifier "03040106".
Informational tokens are "DB2 v8.1.0.96", "s050811", "U803921", and
FixPak
"10".
Product is installed at "/opt/IBM/db2/V8.1".
)
Mark A wrote:
ts******@gmail.com wrote:
Why wouldn't the "WITH UR" SQL construct work in a UDF?

Is the expectation to set it from the calling SQL?

WITH UR only works for a SELECT statement, and it should work in a UDF.
Oct 2 '06 #3

P: n/a
Noe of the isolation and locking clauses work in SQL UDF or triggers
because of inline SQL PL. In CREATE FUNCTION there is actually a clause
stating that:
INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST or INHERIT ISOLATION LEVEL
WITH LOCK REQUEST
Specifies whether or not a lock request can be associated with the
isolation-clause of the statement when the function inherits the
isolation level of the statement that invokes the function. The default
is INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST.

INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST
Specifies that, as the function inherits the isolation level of
the invoking statement, it cannot be invoked in the context of an SQL
statement which includes a lock-request-clause as part of a specified
isolation-clause (SQLSTATE 42601).
INHERIT ISOLATION LEVEL WITH LOCK REQUEST
Specifies that, as the function inherits the isolation level of
the invoking statement, it also inherits the specified lock-request-clause.

If you want to control isolation and locking in a UDF or TRIGGER use the
CALL statement to invoke a procedure which can do what it pleases.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 2 '06 #4

P: n/a
Understood, but....
it would be nice if DB2 gave me the ability to have a table or set of
tables that are queryable only in UR mode. I understand an SP can do
this, but that is not much help if I want to access the table from
Business Objects, or some other tool where my only option to set
isolation exists globally.


Serge Rielau wrote:
Noe of the isolation and locking clauses work in SQL UDF or triggers
because of inline SQL PL. In CREATE FUNCTION there is actually a clause
stating that:
INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST or INHERIT ISOLATION LEVEL
WITH LOCK REQUEST
Specifies whether or not a lock request can be associated with the
isolation-clause of the statement when the function inherits the
isolation level of the statement that invokes the function. The default
is INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST.

INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST
Specifies that, as the function inherits the isolation level of
the invoking statement, it cannot be invoked in the context of an SQL
statement which includes a lock-request-clause as part of a specified
isolation-clause (SQLSTATE 42601).
INHERIT ISOLATION LEVEL WITH LOCK REQUEST
Specifies that, as the function inherits the isolation level of
the invoking statement, it also inherits the specified lock-request-clause.

If you want to control isolation and locking in a UDF or TRIGGER use the
CALL statement to invoke a procedure which can do what it pleases.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 3 '06 #5

P: n/a
ts******@gmail.com wrote:
Understood, but....
it would be nice if DB2 gave me the ability to have a table or set of
tables that are queryable only in UR mode. I understand an SP can do
this, but that is not much help if I want to access the table from
Business Objects, or some other tool where my only option to set
isolation exists globally.
Table level isolation? First time I see that request.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 3 '06 #6

P: n/a
ts******@gmail.com wrote:
it would be nice if DB2 gave me the ability to have a table or set of
tables that are queryable only in UR mode. I understand an SP can do
this, but that is not much help if I want to access the table from
Business Objects, or some other tool where my only option to set
isolation exists globally.
UDFs are pretty much always called from within another SQL statement like
SELECT, INSERT, ... Which isolation level do you expect to be relevant?
The one used inside the UDF of the one from the calling SELECT, INSERT, ...
statement?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Oct 4 '06 #7

P: n/a
Someone has to break new ground :)
Serge Rielau wrote:
ts******@gmail.com wrote:
Understood, but....
it would be nice if DB2 gave me the ability to have a table or set of
tables that are queryable only in UR mode. I understand an SP can do
this, but that is not much help if I want to access the table from
Business Objects, or some other tool where my only option to set
isolation exists globally.
Table level isolation? First time I see that request.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 4 '06 #8

P: n/a
I would expect the UDF to inherit the isolation, as designed.

The problem I am trying to solve is one where I have a single table
where I need UR, but the rest require CS or "higher". The 3rd party
reporting tool I have only permits isolation settings at a global
level. If I could call an SP from a table function...

I'm not sure why Serge thought it was so unusual to have table level
isolation. DB2 has table level locking, so why not table level
isolation?

At the end of the day, I expect the (reporting software) vendor should
fix their tool...

trs
Knut Stolze wrote:
ts******@gmail.com wrote:
it would be nice if DB2 gave me the ability to have a table or set of
tables that are queryable only in UR mode. I understand an SP can do
this, but that is not much help if I want to access the table from
Business Objects, or some other tool where my only option to set
isolation exists globally.

UDFs are pretty much always called from within another SQL statement like
SELECT, INSERT, ... Which isolation level do you expect to be relevant?
The one used inside the UDF of the one from the calling SELECT, INSERT, ...
statement?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Oct 4 '06 #9

P: n/a
Serge Rielau wrote:
Table level isolation? First time I see that request.
I'd rather see isolation clause being part of full-select. That way, a
"dirty view" can be created for specific requirements.

P Adhia

Oct 4 '06 #10

P: n/a
P. Adhia wrote:
Serge Rielau wrote:
>Table level isolation? First time I see that request.

I'd rather see isolation clause being part of full-select. That way, a
"dirty view" can be created for specific requirements.
Now THAT I can buy. It's the app that requires an isolation, not the table.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 4 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.