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

SELECTIVITY clause with BETWEEN predicate

P: n/a
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=''SYSCAT'' '
||'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.

Feb 7 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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=''SYSCAT'' '
||'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
Feb 7 '07 #2

P: n/a
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);
?

Feb 7 '07 #3

P: n/a
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
Feb 7 '07 #4

P: n/a
>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
Feb 7 '07 #5

P: n/a
>
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...

Feb 7 '07 #6

P: n/a
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
Feb 7 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.