Re: Ideas for logicals (index).

[Charles Wilt <charles.wilt@xxxxxxxxx>]

You misunderstand.

The biggest performance hit when adding a new row with a new distinct
value is that then gives you more distinct values than the current
byte code size can hold.  With your existing data, you'd start with a
byte code size of 1 byte, which means you wouldn't have an issue till
you hit distinct value  #257.

But it's a one time hit while the EVI is rebuilt with the larger next
byte code size (up to 4 bytes).

Another hit, is if you add a new distinct codes out of order.  As long
as there's still room in the overflow area, the hit is minimal.  If
there's no room in the overflow, you have to wait while the EVI is
rebuilt.  Again, it's a one time hit.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/evimaintenance.htm

HTH,
Charles


On Fri, Nov 20, 2009 at 8:55 AM,  <Michael_Schutte@xxxxxxxxxxxx> wrote:
> I was thinking that too and probably would be a good idea over meal period.
> I hate to do it on company number because if I create the EVI over two
> unique values and at some point we create a new company, I'll have to try
> to recreate the EVI again because if I don't, from what I understand, the
> adding of new records take longer.
>
>
> --
>
> 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 08:31                                      Subject
>             AM                        Re: Ideas for logicals (index).
>
>
>             Please respond to
>             Midrange Systems
>                 Technical
>                Discussion
>             <midrange-l@midra
>                 nge.com>
>
>
>
>
>
>
> On Thu, Nov 19, 2009 at 4:34 PM,  <Michael_Schutte@xxxxxxxxxxxx> wrote:
> > 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.
>
> Company and Meal Period sound like possible candidates for an EVI
> index.  EVI indexes ofter considerable help in ad-hoc queries.
>
> > 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.
>
> You certainly want indexes on the foreign key fields.  The white paper
> by Mike Cain that Birgitta gave you a link to goes over that.
>
> > Finally, I think a logical 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.
>
> Actually, it's the other way around.  You want the most unique, or
> selective, columns first.  The white paper by Mike Cain that Birgitta
> gave you a link to is specific in that.
>
> > (Please no DDL solutions, boss doesn't want to start using them yet
> because
> > not too many of us have experience in them).
>
> You need to get this changed, TODAY!
>
> Especially if you're planning on using SQL to query the file.  Two main
> reasons:
> SQL tables and indexes perform better than DDS logical and physical
> files.  (Even with RPG RLA access)
> EVI indexes can only be created with SQL DDL
>
> Read this:
> http://www-03.ibm.com/systems/resources/systems_i_software_db2_pdf_Performance_DDS_SQL.pdf
>
>
> Also this related Redbook is good to have:
> http://www.redbooks.ibm.com/abstracts/sg246393.html
>
>
> Good luck!
> Charles
> --
> 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.