Hello.
v8.2.7
This procedure is not working (SQL20046N):
---
create procedure tst(nm1 varchar(20), nm2 varchar(20))
language sql
dynamic result sets 1
begin
declare stmt varchar(4096);
declare c1 cursor with return for s1;
set stmt=
'select tabname, colname '
||'from syscat.columns '
||'where tabschema=''SYS CAT'' '
||'and tabname between ? and ? '
||'selectivity 0.01 '
;
prepare s1 from stmt;
open c1 using nm1, nm2;
end
@
---
But if I do
---
....
||'and tabname=? '
||'selectivity 0.01 '
;
prepare s1 from stmt;
open c1 using nm1;
....
---
it works.
Q:
SELECTIVITY clause with BETWEEN predicate is not allowed?
Why?
It would be very useful...
Sincerely,
Mark B.
6  5752  4.****@mail.ru wrote:
Hello.
v8.2.7
This procedure is not working (SQL20046N):
---
create procedure tst(nm1 varchar(20), nm2 varchar(20))
language sql
dynamic result sets 1
begin
declare stmt varchar(4096);
declare c1 cursor with return for s1;
set stmt=
'select tabname, colname '
||'from syscat.columns '
||'where tabschema=''SYS CAT'' '
||'and tabname between ? and ? '
||'selectivity 0.01 '
;
prepare s1 from stmt;
open c1 using nm1, nm2;
end
@
---
But if I do
---
...
||'and tabname=? '
||'selectivity 0.01 '
;
It shouldn't work here either. Looks like a defect to me.
prepare s1 from stmt;
open c1 using nm1;
...
---
it works.
Q:
SELECTIVITY clause with BETWEEN predicate is not allowed?
Why?
It would be very useful...
No, it is not useful. It is the optimizer's job to figure out the correct
selectivity.
The SELECTIVITY clause is a way to tell the DB2 optimizer what to expect for
extended indexes (see CREATE INDEX EXTENSION).
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
It shouldn't work here either. Looks like a defect to me.
How about this article: http://www-128.ibm.com/developerwork...ps/dm-0312yip/
?
>
No, it is not useful. It is the optimizer's job to figure out the correct
selectivity.
How would you figure out the correct selectivity for this dynamically
prepared statement:
select * from tab where dt between ? and ?;
for table:
create table tab (dt date primary key);
? 4.****@mail.ru wrote:
>
>It shouldn't work here either. Looks like a defect to me.
How about this article:
http://www-128.ibm.com/developerwork...ps/dm-0312yip/
?
I wasn't aware that you set the DB2 registry variable DB2_SELECTIVITY to
YES. If you don't do that, then SELECTIVITY is applicable to user-defined
predicates as the error message says.
>No, it is not useful. It is the optimizer's job to figure out the
correct selectivity.
How would you figure out the correct selectivity for this dynamically
prepared statement:
select * from tab where dt between ? and ?;
Ok, you are right on that. The optimizer can only guess/use defaults there
or rely on some sort of statistics and information about previous queries.
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
>How about this article:
>http://www-128.ibm.com/developerwork...ps/dm-0312yip/
?
I wasn't aware that you set the DB2 registry variable DB2_SELECTIVITY to
YES. If you don't do that, then SELECTIVITY is applicable to user-defined
predicates as the error message says.
You can also set it to ALL. In that case there are virtually no
restrictions on where it can be used. However obedience by the optimizer
is also limited to "best effort".
If there are "typical" scenarios for the BETWEEN with parameter markers
I would recommend looking into REOPT(ONCE) re-optimization. That way the
optimizer is trained using the very set of actual values.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
>
If there are "typical" scenarios for the BETWEEN with parameter markers
I would recommend looking into REOPT(ONCE) re-optimization. That way the
optimizer is trained using the very set of actual values.
But what shall I do in cli application?
AFAIK REOPT applicable only to packages. Is it true?
I don't want to rebind cli packages with this option.
And I couldn't find any "online" command to change optimizator's
behaviour (to use or not to use re-optimization) in cli
applications... 4.****@mail.ru wrote:
>If there are "typical" scenarios for the BETWEEN with parameter markers
I would recommend looking into REOPT(ONCE) re-optimization. That way the
optimizer is trained using the very set of actual values.
But what shall I do in cli application?
AFAIK REOPT applicable only to packages. Is it true?
I don't want to rebind cli packages with this option.
And I couldn't find any "online" command to change optimizator's
behaviour (to use or not to use re-optimization) in cli
applications...
I'm not sure how it works with CLI..
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Rolf Kemper |
last post by:
Hi Luke,
for any reason answering your mail in the usual way did not work yet.
However, if I undestood it right you have the problem of overwriting
values in case they exist in a second xml. I had a similar problem
which could be solved by union.
So may be you can create something like this
<xsl:variable "myMerge" select="($nodeset1 |
|
by: hall |
last post by:
I accidently overloaded a static member function that I use as predicate
in the std::sort() for a vector and ended up with a compiler error. Is
this kind of overload not allowed for predicates and if so, why not?
Shouldn the compiler be able to tell which of he overloaded functions to
use?
The second A::comp() is the one I accidently added and gives the error
message (in Borland C++Builder 6)
Unit1.cpp E2285 Could not find a match for
|
by: Dam |
last post by:
Using SqlServer :
Query 1 :
SELECT def.lID as IdDefinition,
TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour
FROM serveur.Data_tblDEFINITIONTABLEDECODES def,
serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeur
where def.TYPEDETABLEDECODES = 4
|
by: Paul Reddin |
last post by:
Hi,
I'm sure I read somewhere that the SELECTIVITY Clause cannot be used
with static
SQL, can anybody confirm/deny this?
Also, at the risk of a philosophical war, when will the optimizer
provide for some real
form of hints. It is so very,very painful and very,very time consuming
trying to work around bad optimizer plans. I know IBM think it is
|
by: Emin |
last post by:
Dear Experts,
I have a fairly simple query in which adding a where clause slows
things down by at least a factor of 100. The following is the slow
version of the query
-------------------------
SELECT * FROM
( Select x.event_date From x FULL OUTER JOIN y
ON x.event_date = y.event_date
| | |
by: Matt |
last post by:
Hi There,
Can anyone explain me the real advantages of (other than syntax)
lambda expressions over anonymous delegates?
advantage for one over the other.
delegate int F(int a);
F fLambda = a =a++;
F fAnonymous = delegate(int a) { return a++; };
fLambda(1);
|
by: laurenquantrell |
last post by:
Is there an equivalant construction to the CASE WHEN statement that
can be used in the WHERE clause?
For example, this works:
SELECT
FirstName = CASE WHEN c.FirstName = 'Bob' THEN
'Robert'
ELSE
c.FirstName
|
by: Bernard Dhooghe |
last post by:
Table definition:
CREATE TABLE "SCHEMA1 "."X2" (
"C1" CHAR(20) NOT NULL ,
"C2" CHAR(10) NOT NULL ,
"C3" CHAR(30) NOT NULL GENERATED ALWAYS AS (C1||
C2) )
IN "USERSPACE1" ;
-- DDL Statements for primary key on Table "SCHEMA1 "."X2"
|
by: =?ISO-8859-2?Q?Gregor_Kova=E8?= |
last post by:
Hi!
Is it possible to use parameter markers like this:
1.) SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%?%'
If I now set parameter 1 to '' (empty string) I don't get any rows back,
but if I run:
2.) SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%%'
I get back data I expect.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
