Dynamic SQL using Parameter Markers

We are currently only using embedded sql for reporting, all of it dynamic.
(build a string and prepare it)

If we start using it for interactive programs, we would like to change it to
use parameter markers. However we use the same programs to process the same
file names in different libraries. (we separate our different companies data
into different libraries)

Does the discussion below on affect this? Would we compile the program
optimizing for the "big" company and would the program when used on the
"smaller" companies rebuild the access plan?

Jim





message: 6
date: Fri, 20 Apr 2007 07:24:46 +0200
from: "BirgittaHauser" <Hauser@xxxxxxxxxxxxxxx>
subject: AW: Dynamic SQL using Parameter Markers

Hi,

Sometimes even authors of books are wrong!

Tables/Views and Libraries/Schemas cannot be used in static SQL as host
variables, nor can they used as parameter markers.

Parameter markers are a replacement of host variables in dynamic SQL.
Parameter markers can be used to gain performance, because the ODP needs
only to be built a single time and can be reused several times but with
different values.

If a table/view or library/schema must be changed, the ODP must be rebuilt.
That means, an existing ODP must be deleted. Then the string must be
converted into an exeuctable SQL-Statement, i.e. PREPARE must be executed.
After the converted SQL Statement can be executed. When executing a SQL
statement after the PREPARE statement, a full optimization occurs, i.e. an
access plan must be built or at least validated, the necessary temporary
objects (for example Hash tables, Bitmaps, temporary datastructures ...)
must be built and finally filled with data.
Because it makes no sense to use parameter markers in this way, they not
allowed.

Mit freundlichen Gr??en / Best regards

Birgitta Hauser