|
I get it (I think <smile>)...when you don't want a field to be considered in the selection, you just specify the from value to be *LOVAL and the to value to be *HIVAL. When you *do* want a field to be considered in the selection, you make the from and to values equal to the search value. Right? :) On 8/6/06, HauserSSS <Hauser@xxxxxxxxxxxxxxx> wrote:
midrange.com
Hi Michael,
>>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?
If you use logical files with select/omit clauses on your files, the query
dispatcher rerouts all SQL-statement based on this files to the classic
query engine (CQE). SQE cannot handle (joined) logical files at all. Even
if
you don't use the joined logical files in your select statements, the
queries are rerouted to CQE. You may avoid rerouting by specifying the
option IGNORE_DERIVED_INDEXES *YES in the QAQQINI file. But access paths
in
those tables then are not considered by the query optimizer.
Instead of using joined logical files, you you either can hard code those
criterias in the select-statements or, better you can create and use
SQL-views. Because SQL-views have no key information, access path
maintenance is always rebuild. That means you can have a much views as you
want, without any performance reductions. And a view is much mor powerfull
than a joined logical file, because you can use all you can do with a
select-statement, except an order by. It is even possible to create a view
over a view. A view can be specified in a SQL-statement like any physical
file/table. Views can be handled correctly by the SQE.
>>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.
DB2 UDB for iSeries is optimized for static SQL. Dynamic SQL will work,
but
there are some things, that must be done a runtime, such as syntax
checking,
building a new access plan, create a new ODP(open data path) that reduce
the
performance. That's why I always prefer static SQL. I only use dynamic SQL
if I have to handle different tables (i.e. in different libraries) in the
same statement. (but this rarely happens)
>>Does the Else NULL mean 'don't include this as part of the Order By
clause?
Correct
>>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?
Have you seen my between clauses?
We have a lot of programs where we have a bunch of select criterias. I
always use from/to-fields and between clauses.
Depending on what the user enters I fill the from/to-fields with either
the
inserted values or *HiVal or *LoVal. The optimizer can handle this
correctely.
Here is a hardcopy of a selection screen:
L + P Auslagerungs-Anforderungen LP
LL0401R 075 LOGSTAR MF0 HAUSER HSLP_A5
6.08.0
1 LOGSTAR BA BAMBERG
Postleitzahl . . . . . . . . . _____________ - _____________
Liefer-Termin . . . . . . . . _____________ - _____________
Empfanger . . . . . . . . . . _____________ *****
Bestell-Nr. . . . . . . . . . _____________ - _____________
Tour . . . . . . . . . . . . . _____________
Artikel-Nr. . . . . . . . . . ___________________ ________
Status . . . . . . . . . . . . __
Versandart . . . . . . . . . . __
Auftrags-Art . . . . . . . . . __
F3=Verlassen F4=Bedienerfuhrung F6=Erfassen F7=Wechsel KNDB/LAO
F12=Abbrechen
The next sceen shows the subfile. On the bottom you see a lot of function
keys with different sort sequences:
L + P Auslagerungs-Anforderungen LP
LL0401R 075 LOGSTAR MF0 HAUSER HSLP_A5
6.08.06
1 LOGSTAR BA
Auswahl eingeben und Eingabetaste drucken.
1=Auswahlen 2=Andern Kopfsatz 4=Loschen 5=Anzeigen Kopfsatz 7=Storno
8=Split 12=Andern Pos.Satz 15=Anzeigen Pos.Satz
Opt Bestell-Nr. Sendungs-Nr. Lief.Term. Wert/U Pos
STS
Empfanger Empfanger-Name Volumen/R Wert/R Pri
AAR
Land PLZ Ort Text Volumen Wert Pos
VSA
0714001 7000003139 14.07.2006 001
AB
005861 TOOM-Getrankemarkt 666 100
N
D 45711 Datteln 000
04
0714001 01 7000003139 14.07.2006 001
AB
005861 TOOM-Getrankemarkt 666 100
N
D 45711 Datteln 000
04
En
de
F10=Sort.Send.-Nr. F11=Sort.Volumen F13=Sort.Wert F14=Sort.PLZ
F24=Weitere
F15=Sort.Kunde
The declare-statement looks as follows. You'll see a lot of between
clauses
(for all the select criteria above) and a bunch of different order by
clauses to satisfy all the sort sequences when pressing F10, F11, F13,
F14,
F15:
* Auftrags-Art
C R1AARP IFNE *BLANKS Mit Auftrags-Art
C MOVELR1AARP $$AARV Von Auftrags-Art
C MOVELR1AARP $$AARB Bis Auftrags-Art
C ELSE
C MOVEL*LOVAL $$AARV Von Auftrags-Art
C MOVEL*HIVAL $$AARB Bis Auftrags-Art
C ENDIF
*
* Versand-Art
C R1VSAP IFNE *BLANKS Mit Versand-Art
C MOVELR1VSAP $$VSAV Von Versand-Art
C MOVELR1VSAP $$VSAB Bis Versand-Art
C ELSE
C MOVEL*LOVAL $$VSAV Von Versand-Art
C MOVEL*HIVAL $$VSAB Bis Versand-Art
C ENDIF
*
C/EXEC SQL
C+ Declare $CSRC1 CURSOR For
C+ Select AKRRN, AKBNR, AKSNU, AKLIT, AKPRI2, AKSTS,
C+ AKAAR, AKVSA, AKKND, AKZUA,
C+ COALESCE(ADNA1, ' '), COALESCE(ADLAKZ, ' '),
C+ COALESCE(ADPLZ, ' '), COALESCE(ADORT , ' '),
C+ AKVOL, AKKD37, AKKD35, AKKEA
C+ from LLAKOPV02
C+ where AKFINR = :FIRNR
C+ and AKKNDB = :RBKNDB
C+ and AKLAO = :RBLAO
C+ and AKBNR between :R1BNRV and :R1BNRS
C+ and AKKND concat AKZUA between :$$KNDV concat :$$ZUAV
C+ and :$$KNDB concat :$$ZUAB
C+ and AKLIT between :R1LIVN and :R1LIBN
C+ and AKLAKZ between :$$LANV and :$$LANB
C+ and AKPLZ between :R1PLZV and :R1PLZS
C+ and AKTOUR between :$$TURV and :$$TURB
C+ and AKSTS between :$$STSV and :$$STSB
C+ and AKAAR between :$$AARV and :$$AARB
C+ and AKVSA between :$$VSAV and :$$VSAB
C+ and AKDSN = ' '
C+ order by case when :$Sort = 'KND' then AKKND
C+ when :$Sort = 'SNU' then AKSNU
C+ when :$Sort = 'PLZ' then AKLAKZ
C+ else NULL End,
C+ case when :$Sort = 'VOL' then AKVOL
C+ When :$Sort = 'WRT' then AKKD35
C+ else NULL End Desc,
C+ case when :$Sort = 'KND' then AKZUA
C+ when :$Sort = 'SNU' then AKBNR
C+ when :$Sort = 'PLZ' then AKPLZ
C+ when :$Sort = 'VOL' or :$Sort = 'WRT'
C+ THEN AKKND
C+ else NULL End,
C+ case when :$Sort = 'KND' then AKBNR
C+ when :$Sort = 'PLZ' then AKKND
C+ when :$Sort = 'VOL' or :$Sort = 'WRT'
C+ THEN AKZUA
C+ else NULL End,
C+ case when :$Sort = 'PLZ' then AKZUA
C+ when :$Sort = 'VOL' or :$Sort = 'WRT'
C+ THEN AKBNR
C+ else NULL End,
C+ case when :$Sort = 'PLZ' then AKBNR
C+ else NULL End
C+ FOR READ ONLY OPTIMIZE FOR 1000 ROWS
C/END-EXEC
Normally users have their favorite selection and order by criteria and it
will noch change a lot during the day.
Hope this helps
Mit freundlichen Gru?en / 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+hauser=sss-software.de@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx]Im Auftrag
von Michael Ryan
Gesendet: Sonntag, 6. August 2006 14:37
An: RPG programming on the AS400 / iSeries
Betreff: 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.
>
>
--
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.
As an Amazon Associate we earn from qualifying purchases.
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.
