Why wouldn't the "WITH UR" SQL construct work in a UDF?
Is the expectation to set it from the calling SQL?
10  7363  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.
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.
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/
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/ 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/ 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
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/
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: ribchr00 |
last post by:
Hi all,
I would like to replace line breaks such '+' with '<br />'. Easy
task. Problems start when I try to only replace lines that do not end
with HTML tags.
I tried preg_replace("/+/", "<br />\n") but this does not
seem to work.
An example to better understand what I would like to achieve:
first line
|
by: Marco Bubke |
last post by:
Hi
I have read some mail on the dev mailing list about PEP 318 and find the new
Syntax really ugly.
def foo(x, y): pass
I call this foo(1, 2), this isn't really intuitive to me!
Also I don't like the brackets.
|
by: cooldv |
last post by:
i know how to replace the sign " when SUBMITTING a form in asp by this
code:
message = Replace(usermessage, "'", "''").
My problem is DISPLAYING data in an asp FORM, from an an access
database, when the data already contains a " sign
problem is like this:
access database .... to update on the internet .... a *dataupdate.asp*
page ..... On this page, the data gets displayed in a form where i
|
by: Prince Kumar |
last post by:
When running LOAD with "ALLOW READ ACCESS", I get the following error
if select is running againt the table (isolation UR).
load.sql
---------
db2 load from /u02/data/dly_d040817_test.dat of asc \
modified by implieddecimal nullindchar=Y \
fastparse anyorder \
METHOD L \(01 10,11 12,13 15,16 24,25 28,29 48,49 64\) \
MESSAGES /u02/load/msg/auth_detail.msg \
|
by: Bartosz Milewski |
last post by:
My resource file compiles correctly, but when I try to open it in VS, I get
the error, "cannot find include file". The file in question is a "constant
include". This is what it looks like in the rc file:
////////////////////////////////////////////////////////////////////////////
/
//
// Generated from the TEXTINCLUDE 2 resource.
//
#include "buildoptions.h"
| | |
by: Kirt |
last post by:
i have walked a directory and have written the foll xml document.
one of the folder had "&" character so i replaced it by "&"
#------------------test1.xml
<Directory>
<dirname>C:\Documents and Settings\Administrator\Desktop\1\bye
w&y </dirname>
<file>
<name>def.txt</name>
<time>200607130417</time>
</file>
|
by: samjnaa |
last post by:
Please check for sanity and approve for posting at python-dev.
In Visual Basic there is the keyword "with" which allows an object-
name to be declared as governing the following statements. For
example:
with quitCommandButton
.enabled = true
.default = true
end with
|
by: situ |
last post by:
Hi,
i'm using Db2 Version 8.2.
in a stored procedure i'm assiging a the result of sql query ( with
"WITH UR" ) to a variable as shown below.
SET v_temp = ( SELECT 1 FROM
Table_1 A , Table_2 B
WHERE a.cd=b.cd )
|
by: FullBandwidth |
last post by:
I have been perusing various blogs and MSDN pages discussing the use
of event properties and the EventHandlerList class. I don't believe
there's anything special about the EventHandlerList class in this
context, in fact some articles from pre-2.0 suggest using any
collection class of your choice. So my questions focus more on the
syntax of event properties provided by the "event" keyword in C#.
(Disclaimer - I am a C++ programmer working...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
