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