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

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

P: n/a
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.
Mar 18 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
m
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?
Mar 18 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.