SQL from Java - PREPARE (with parameter markers) vs. Dynamic

[m]

Maybe this has been asked and answered - but I have been looking through
old archives for quite some time....

Typical Java app running from Websphere connected to DB2 V8.2FP15 on
Linux using JDBC Type 4 drivers. Programmers use mostly PREPARE/EXECUTE
logic for security and 'other' reasons - but some SQL executes MUCH
slower than same SQL using plain old SQL with values then the SQL with
variable markers. Worst case 20 seconds vs. 1 second. Best case 4
seconds vs. .2 seconds. I've read about REOPT(ALLWAYS), etc. but can't
figure out HOW this could be implemented in Java or at the database for
SQL being submitted by these Java apps. Any help would be appreciated.

[m]

Re: SQL from Java - PREPARE (with parameter markers) vs. Dynamic

m wrote:

Maybe this has been asked and answered - but I have been looking through
old archives for quite some time....
>
Typical Java app running from Websphere connected to DB2 V8.2FP15 on
Linux using JDBC Type 4 drivers. Programmers use mostly PREPARE/EXECUTE
logic for security and 'other' reasons - but some SQL executes MUCH
slower than same SQL using plain old SQL with values then the SQL with
variable markers. Worst case 20 seconds vs. 1 second. Best case 4
seconds vs. .2 seconds. I've read about REOPT(ALLWAYS), etc. but can't
figure out HOW this could be implemented in Java or at the database for
SQL being submitted by these Java apps. Any help would be appreciated.

Main goal is to let programmers continue to use PREPARE/EXECUTE - but
somehow implement REOPT(ONCE) or REOPT(ALLWAYS) to get the SQL optimizer
to pick an access plan based on actual predicate values. I know REOPT is
a BIND option - so again - how can this be implemented on dynamic SQL
packages?