very very slow query with parameters

[aleicaro]

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

[Serge Rielau]

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