470,594 Members | 1,499 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,594 developers. It's quick & easy.

very very slow query with parameters

Hi,
I have a problem with db2 query...
I access to my database via jdbc using com.ibm.db2.jcc.DB2Driver. I
build my string query in a java class. I have some parameters in my
query, but there are two of these which gives me some problems:
If I set the condition in the string: "...and myfield between 20001 and
20520..." all works properly. But if I want to set the values via
parameters writing "...and myField between ? and ?..."

and then I value the parameters with values "20001" and "20520" when I
execute th query it doesn't give me nothing, no errors and no results;
all remain locked...:( I waited for some minutes, but the query without
the 2 parameters finish in 10-20 seconds...

P.S. 1: myfield is integer
P.S. 2: In the same query I have other parameters, and these works
properly....

Does anybody can help me?

Thank you
Alessandro Rossi

Jan 4 '06 #1
1 3241
al******@libero.it wrote:
Hi,
I have a problem with db2 query...
I access to my database via jdbc using com.ibm.db2.jcc.DB2Driver. I
build my string query in a java class. I have some parameters in my
query, but there are two of these which gives me some problems:
If I set the condition in the string: "...and myfield between 20001 and
20520..." all works properly. But if I want to set the values via
parameters writing "...and myField between ? and ?..."

and then I value the parameters with values "20001" and "20520" when I
execute th query it doesn't give me nothing, no errors and no results;
all remain locked...:( I waited for some minutes, but the query without
the 2 parameters finish in 10-20 seconds...

P.S. 1: myfield is integer
P.S. 2: In the same query I have other parameters, and these works
properly....

The reason may be that the BETWEEN predicate has a very good
selectivity, but without the actual values DB2 doesn't know that.
So... you have two choices.
1. Use the SELECTIVITY clause on the predicate
This is an SQL statement change which will tell DB2 which
selectivity you anticipate from the predicate.
http://publib.boulder.ibm.com/infoce...n/r0000754.htm
To enable this outside of user defined predicates you need to:
db2set DB2_SELECTIVITY = ALL
http://publib.boulder.ibm.com/infoce...n/r0005664.htm
(and restart the instance.. sorry)
2. Use REOPT(ONCE) for your environment.
This directive tells DB2 that it should wait with the compilation
of any SQL until it gets the first set of values for the parameters.
So the plan is "trained" with these values.
Now, I have no clue how to set this for JDBC. Presumably you need to
rebind the CLI packages with that option.(?)

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Jan 4 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by DJJ | last post: by
3 posts views Thread by Mario Soto | last post: by
50 posts views Thread by diffuser78 | last post: by
4 posts views Thread by pmacdiddie | last post: by
29 posts views Thread by wizofaus | last post: by
3 posts views Thread by Richard Hollenbeck | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.