AW: SQL file not hardcoded

Hi,

What was your first question?
How to get around hardcoding the file in a SQL statement?

In static SQL a file must always be hard coded, because an access plan will
be stored in the program object.
An access plan is absolutely neccessary to built/open the data path (ODP).
It's less expensive to validate an access plan than creating it by scratch.
Access plan building or validation means, checking all access path (keyed
logical files or SQL indexes), determine the temporary objects needed (such
as hash tables, relative record lists, temporary files) to access the data.

To built or validate an access plan the physical files (or SQL Tables) must
be known. Each SQL statement gets its own ODP which is built according the
underlying access plan.

With dynamic SQL, first a string, containing the SQL-Statement to execute,
must be built. After the SQL statement must be converted into an executeable
SQL statement, i.e. the access plan must be built, by using the SQL
statement PREPARE.
The same SQL statement only with different values in the parameter markers
can be exeucted several times, by using the SQL statement EXECUTE. When
using EXECUTE the ODPs can be reused, i.e. it is not necessary to rebuilt
the temporary objects each time the SQL statement is performed. (EXECUTE
IMMEDIATE is a combination of PREPARE and EXECUTE)

When changing the phyiscal file an already opened access path cannot be
reused, that means a full optimization must be performed. Because after
changing the tables a new PREPARE-Statement must be executed, it makes no
sense to specify the tables as parameter markers, instead they must be
specified directly in the string containing the SQL-statement.
MySQLString = 'Select .... From ' + MyFile + ' Where x = ? And y = ? .... ';

An other way to avoid use different files without hardcoding them is to use
the CL-Command OVRDBF to specify the tables you want to access before
executing the SQL statement. The optimizer is smart enough to recognize that
the already opened ODP can no longer be used, hard closes the ODPs and
executes a full optimization.

For performance issues, it is much better to hard code the physical
files/SQL tables. The SQL-statements should be enclosed into procedures that
run in service programs with named activation groups.
Keep in mind each SQL-Statement gets its own ODP, i.e. full optimization
must be performed. If you code the same SQL-Statement in several programs,
procedures or subroutines, for each of these SQL statements a full
optimization must be performed with all overhead.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jeff Crosby
Gesendet: Friday, September 14, 2007 17:14
An: 'Midrange Systems Technical Discussion'
Betreff: RE: SQL file not hardcoded


"rare mood to get along . . ."

Surely you jest. <g> _I_ don't have any problem getting along with you.

BTW, did you notice no one answered my first question? <bg>