|
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?
midrange.com
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.
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.
