Re: SQL Multiple Prepared Statements - Best Practice

Hi Birgitta -

This is great - it really helps! I'm not using the logical files in the
prepared statement - I'm using the underlying physical. I'm just replacing
the function of the 7 logicals with the SQL statement, so I'm good there. A
question about point 1: If I did have select/omit criteria, field
selections, or join conditions specified in a logical file, would it still
be better to specify those criteria on the Select statement over the
physical file; or would it be better to use the logical file in the Select
and let the optimizer rewrite the SQL statement?

In point 2, I understand that you're saying I *could* do what what I'm
currently doing now (changing the string, preparing, executing, opening)
every time the user wanted to change the selection criteria, but my program
would suffer from poor performance. So, it's doable and would work, but it's
not a good practice.

So in point 3, your example shows different conditons. A couple of
questions: In the CASE statement, when the user makes the selection that has
multiple parts of the Order By, does the SQL pre-processor put in the commas
between the items? In other words, would the end statement (constructed by
the optimizer) look like this:

Select Fld1, Fld2, Fld7, Fld11
      from MyTable
      Where Fld3   = :HostFld3
          and Fld4   = :HostFld4
          and Fld1   between :HostFld1From and :HostFld1To
          and Fld7   between :HostFld7From and :HostFld7To
Order By Fld7, Fld8, Fld11

Does the Else NULL mean 'don't include this as part of the Order By clause?

Another question: What would be the best way to selectively have selection
criteria? For instance, in some selections I may want to select based on a
value in Fld4, and in other selections I may not. Would I have the Where
clause in a Case statement?

Thanks loads for your help, and for all your help to the forum!

- Michael

On 8/6/06, HauserSSS <Hauser@xxxxxxxxxxxxxxx> wrote:

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.



--
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.