× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.




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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.