× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi Michael,

sorry I was too fast!

first specifying DDS described logical files in an SQL statement is almost
the worst thing you can do!
1. The query optimizer only takes the field selections, join conditions and
select/omit clauses from DDS described logical files an rewrites the SQL
statement based on the underlying physical files. In this step the optimizer
does not care about any key information specified in the logical file. In
the second step ALL access paths (in either DDS described keyed logical
files or SQL indexes) are checked to determine the optimal access path.
Because the optimizer does not know anything about the access path of the
specified logical file, it is nothing more than hazard if this access path
will be used. If you need a predefined sort sequence, you have to add an
order by clause to your select statement.

All SQL statements that use DDS described logical files are rerouted to the
old (Classical) Query Engine (CQE) and cannot profit from the advantages of
the new SQL Query Engine (SQE). The rerouting may cost between 10 to 15 % of
performance.

2. If you want to use dynamic SQL a single Cursor would be enough. Each time
you want to change the order by criteria you simply rebuild your string,
prepare it, declare the cursor, open it and fetch the results. Don't forget
to close it after. But because the SQL statement is not known at compile
time, syntax checking must be executed each time you execute your prepare
statement. Also, with dynamic SQL no access plan will be stored in the
program object. That means each time the open statement will be executed an
access plan will be built from scratch and used, but not stored. (With SQE
access plans stored in the SQL plan cache can be validated, but not with
CQE)

3. But dynamic SQL will not be neccessary, dynamic sorts are even possible
with static SQL. Contrary to dynamic SQL the SQL statement is already known
and checked at compile time and also the first access plan is built and
stored in the program object. At runtime the access plans stored in the
program object can be validated, updated and used. (BTW old access plans are
not deleted, that means your program object may grow over the time without
any changes in the souce code).

Here is an example of dynamic selection criteria and dynamic sorts in a
static SQL:
There are two display file fields DspFFld1 and DspFFld2 where the user can
enter selection criteria and a third field DspFSort where the user can enter
a sort sequence. Depending on the user's inserts host variables containing
the from and to values are filled. These host variables are then used in
between clauses in the select statement. The query optimizer is smart enough
to detect if a single value is choosen or a wide range. Depending on the
sort sequence, the result set will be ordered as follows:
DspFSort = 1 --> Fld1, Fld2
DspFSort = 2 --> Fld7, Fld8, Fld11
DspFSort = 3 --> Fld5

 /Free
    If DspFFld1 <> *Blanks;
       HostFld1From = DspFFld1;
       HostFld1To   = DspFFld1;
    Else
       HostFld1From = *LoVal;
       HostFld2From = *HiVal;
    EndIf;

    If DspFFld7 <> *Blanks;
       HostFld7From = DspFFld1;
       HostFld7To   = DspFFld1;
    Else
       HostFld7From = *LoVal;
       HostFld7From = *HiVal;
    EndIf;
 /End-Free
C/Exec SQL
C+  Declare CsrC1 Cursor For
C+     Select Fld1, Fld2, Fld7, Fld11
C+       from MyTable
C+       Where     Fld3   = :HostFld3
C+             and Fld4   = :HostFld4
C+             and Fld1   between :HostFld1From and :HostFld1To
C+             and Fld7   between :HostFld7From and :HostFld7To
C+ Order By Case When :DspFSort = 1 then Fld1  Else NULL End,
C+          Case When :DspFSort = 1 Then Fld2  Else NULL End,
C+          Case When :DspFSort = 2 Then Fld7  Else NULL End,
C+          Case When :DspFSort = 2 Then Fld8  Else NULL End,
C+          Case When :DspFSort = 2 Then Fld11 Else NULL End,
C+          Case When :DspFSort = 3 Then Fld5  Else NULL End
C/End-Exec

Hope this helps

Mit freundlichen Gruessen / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Michael Ryan
Gesendet: Samstag, 5. August 2006 22:48
An: RPG programming on the AS400 / iSeries
Betreff: SQL Multiple Prepared Statements - Best Practice


I have a program that's currently using 7 different logicals over the same
physical. No select/omit criteria - just different keys. Depending on user
selection (via function keys), a subfile is populated using a different
logical. Because of some (new) additional selection criteria, I've decided
to rewrite using embedded SQL.

Here's my question: Should I be using 7 different Prepares and Executes and
Opens of cursors? Is there a different way of doing it? I know I can't use
Execute Immediate because I'm using a Fetch. Looking for best practices or
suggestions.

Thanks...
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.