Re: AW: Ideas for logicals (index).

[Michael_Schutte@xxxxxxxxxxxx]

Thank you,

That's what I was looking for.


--

Michael Schutte
Admin Professional



Bob Evans Holiday Farmhouse Feast, Serves 6-8 l $74.99
A complete homestyle meal TO GO, ready to heat at home, serve & enjoy!
Perfect for Thanksgiving, Christmas or holiday entertaining.
For more information, visit www.FarmhouseFeast.com




                                                                       
             Charles Wilt                                              
             <charles.wilt@gma                                         
             il.com>                                                    To
             Sent by:                  Midrange Systems Technical      
             midrange-l-bounce         Discussion                      
             s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>       
                                                                        cc
                                                                       
             11/20/2009 09:37                                      Subject
             AM                        Re: AW: Ideas for logicals (index).
                                                                       
                                                                       
             Please respond to                                         
             Midrange Systems                                          
                 Technical                                             
                Discussion                                             
             <midrange-l@midra                                         
                 nge.com>                                              
                                                                       
                                                                       




Michael,

Default page size is one reason SQL DDL performs better than DDS.  So,
specifing PAGESIZE of 64KB, for your DDS that would help.

However, the bigger reason for using DDL to define the physical table,
is that SQL defined tables validate data on WRITE, whereas DDS defined
tables validate on READ.  Assuming you do more reads than writes to
the table, SQL tables are more efficient.

read the "Modernizing Database Access The Madness Behind the Methods ", by
Dan
Cruikshank. That Luis and I gave you a link to.

HTH,
Charles

On Fri, Nov 20, 2009 at 9:05 AM,  <Michael_Schutte@xxxxxxxxxxxx> wrote:
> Instead of creating the SQL index first, can I just specify 64k into the
> PAGESIZE keyword?  FYI our default in  *KEYLEN not 8k.
>
>
> --
>
> Michael Schutte
> Admin Professional
>
>
>
> Bob Evans Holiday Farmhouse Feast, Serves 6-8 l $74.99
> A complete homestyle meal TO GO, ready to heat at home, serve & enjoy!
> Perfect for Thanksgiving, Christmas or holiday entertaining.
> For more information, visit www.FarmhouseFeast.com
>
>
>
>
>
>             "Birgitta Hauser"
>             <Hauser@sss-softw
>             are.de>                                                    To
>             Sent by:                  "'Midrange Systems Technical
>             midrange-l-bounce         Discussion'"
>             s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>
>                                                                        cc
>
>             11/20/2009 01:26                                      Subject
>             AM                        AW: Ideas for logicals (index).
>
>
>             Please respond to
>             Midrange Systems
>                 Technical
>                Discussion
>             <midrange-l@midra
>                 nge.com>
>
>
>
>
>
>
> ... yet another comment about indexes and keyed logical files:
> An SQL index has per default a page size of 64K, while a DDS described
> logical file only has per default a page size of 8K.
> Because of the larger page size an SQL index cannot share access path
with
> a
> DDS described logical file.
> In this way, if you first create an index and after a logical file with
the
> same key fields, you'll create only a single access path, which must be
> actualize with every modification of the base dates.
> If you first create a logical file and after an SQL index with the same
key
> fields access path cannot be shared, that means 2 access paths are
created
> which must be both actualized.
>
> SQL statements that can use SQL indexes will perform better than if they
> have to use DDS described logical files with the same key fields instead.
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training
them
> and keeping them!"
>
>
> -----Ursprüngliche Nachricht-----
> Von: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Birgitta Hauser
> Gesendet: Friday, 20. November 2009 07:07
> An: 'Midrange Systems Technical Discussion'
> Betreff: AW: Ideas for logicals (index).
>
> Hi,
>
> to get a little more theoretically:
>
> There are two kinds of indexing strategies:
> 1. Proactive Approach - Create the indexes you already know you'll need
>   You need indexes created over the fields/columns needed to join with
> other files/tables.
>   You also need indexes over fields/columns which will used in the where
> conditions with comparison = or IN-Predicate or Like without leading %.
>   Other access path can be determined with the reactive indexing
strategy.
>   You may also create indexes over the fields/columns you'll use in Group
> By and Order By.
>   But Group By and Order By are not as important as Join and Where
> conditions.
>   The main goal is to get access to the requested data as fast as
> possible.
>
>   In this way the optimizer may use an index with other key fields than
> specified in the order by clause.
>   Because it is sometimes faster to store the data in a temporary object
> and sort the information stored in the temporary object,
>   than using an index with the requested order by columns.
>   Example: You specified 3 fields in the where clause (for example
> delivery
> date and special delivery terms),
>            but want to have your result ordered after Order No.
>            In this situation it may be faster to use an index with
> delivery
> date and delivery terms instead of using an index with OrderNo.
>   When Creating Indexes specify the most selective columns first.
>
> 2. Reactive Approach - Analyze your SQL environments
>   2.1. Look at the system wide index advisor, which is a physical
> file/table SYSIXADV which exists since V5R4.
>        In the SYSIXADV all index advices made by both query engines (CQE
> and SQE) are stored.
>        You'll also get information how often the indexes are advised and
> when the last advice occurred.
>        You simply can use SQL or query to access this file, better is to
> use the tools integrated into the iSeries Navigator
>   2.2. Run your SQL Statement with iSeries Navigator's Run an SQL Script
> and use Visual Explain for explanation and to look for advises.
>        You may also use a STRDBG (without any parameters) in Green Screen
> before executing STRSQL or calling a program with embedded SQL.
>        With STRDBG information about your SQL statements are written into
> the joblog, which can be analyzed.
>        ... but STRDBG is no longer a strategic product for SQL
information
> and does not contain all information.
>   2.3. Use STRDBMON or iSeries Navigator's Performance Monitors to
collect
> information about SQL statements
>        To analyze these information use the tools integrated into the
> iSeries Navigator database.
>
> There is a very good white paper about indexing strategies written by
Mike
> Cain. Even though it is written several years ago (and may not have the
> newest features) it excellently covers the basics.
> http://www-03.ibm.com/servers/enable/site/bi/strategy/strategy.pdf
>
>
> DDS described logical files and SQL-Indexes:
> Forget about logical files, only create SQL Indexes. Even though an SQL
> index cannot be specified directly in an SQL statement it can be used
with
> native I/O like any keyed logical file. Beginning with release 6.1 it is
> also possible to create additional columns (for example quantity*price)
and
> use them as key fields. Further it is also possible to specify where
> conditions in an index (which is even more powerful then
> SELECT/OMIT-Clauses
> in DDS).
> You only need DDS described logical files if you need to access a keyed
> join
> file with native I/O.
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training
them
> and keeping them!"
>
> -----Ursprüngliche Nachricht-----
> Von: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
> Michael_Schutte@xxxxxxxxxxxx
> Gesendet: Thursday, 19. November 2009 22:34
> An: Midrange Systems Technical Discussion
> Betreff: Ideas for logicals (index).
>
>
>
> I'm looking for some suggestions.  We're going to be creating a large
> summary file and I'd like to get some ideas on what logical files I
should
> create.  Normally, we fly by the seat of our pants and create them when
> needed. But since this file is going to be large, I'd like to get them
> created beforehand.
>
> Anyway, the file unique key is by Company, Restaurant, Date, Item, Meal
> Period.    I believe SQL will mainly be used to retrieve the data from
this
> file.
>
> I believe one logical should be created by Company and Meal Period, then
> just throw in restaurant, date and item behind it.   I say Company and
meal
> period first because currently there are only 2 possible values for
company
> and 4 possible values for Meal Period.
>
> But then my concern comes in when join in other files.
>
> For example, I may want to get region 1 restaurants only.  So I'm going
to
> need a logical by company and restaurant, but should I throw in the other
> fields too?
>
> Then we might need to join in the Item Master file to select only
> beverages.
> So a logical by company and item would be needed.
>
> Finally, I think a locigal by  Company, date would be good to have.
>
> I guess I'm all over the place here, not really sure how to explain
myself.
> Does anyone have any suggestions?  I've read for SQL indexes that you
don't
> want to create indexes with the same fields (but in different order)
> because
> it doesn't really buy you anything (take longer for record to be
inserted).
> Would this be true for logicals too?  What's the harm if we add the other
> fields anyway, just in case someone in the future wants to do I/O reads
on
> the file or for the group by clause?
>
> I think I've told before that you want to try to create you logicals
where
> the key fields with the least amount of unique values are first.
>
> (Please no DDL solutions, boss doesn't want to start using them yet
because
> not too many of us have experience in them).
>
> --
>
> Michael Schutte
> Admin Professional
>
>
>
> Bob Evans Holiday Farmhouse Feast, Serves 6-8 l $74.99 A complete
homestyle
> meal TO GO, ready to heat at home, serve & enjoy!
> Perfect for Thanksgiving, Christmas or holiday entertaining.
> For more information, visit www.FarmhouseFeast.com
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.