× 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.



Glad to hear it. I hope you are going ahead and using DDL for the
physical and any logicals you can. (Unless you're at v6r1, you'll
need DDS logicals for Select/omit if needed for RPG.)

I'd do single column EVIs.

I've never really seen an explanation of how a multi column EVI helps
any more. Sure it's slightly faster than combining two single
columns. But the combination is simply a logical AND/OR of the vector
table anyway. It doesn't take long.

It would seem to me that a multi-column EVI would be more prone to
running out of room for new keys in the overflow area.

Lastly, where are you getting "2 byte vector"? With less than 255
values, the width of the vector table (or size of the byte code) is
only 1 byte. Note the lenght of the vector table is = the number of
rows in the table you're building the EVI over.

HTH,
Charles






On Fri, Nov 20, 2009 at 11:36 AM, <Michael_Schutte@xxxxxxxxxxxx> wrote:

One last question,

I've gotten the go ahead to use EVI.  And we've created one by company and
meal period.  My last question (hopefully), I think it would be worthwhile
to do one by company and item and then by company and restaurant.  The one
by item would end up being a 2-btye vector and the unit one will be a 2
byte vector too.  But should I just do an EVI with company only,  EVI by
meal period only, EVI by unit only, EVI by item only.     Maybe even do one
by date because it'll be around hundred years before it'll reach the 4-byte
correct?

I'm not sure if company should be included because unit is shared between
the two companies. Item numbers technically could and obviously meal period
is shared between the two.

If I do it just on the single fields, then the query engine could combine
multiple EVIs together and find matches. correct?

--

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 10:46                                      Subject
            AM                        Re: AW: AW: Ideas for logicals
                                      (index).

            Please respond to
            Midrange Systems
                Technical
               Discussion
            <midrange-l@midra
                nge.com>






Mark,

That's a new one to me.  I always thought SQL indexes could share
another SQL index.

A quick test seems to confirm it, but where is this documented?

The "conformance to ANSI SQL standards" seems a bit fishy to me.  As
I'd expect the sharing to be an implimentation detail tucked deep
inside the OS.  I wonder if Chuck or somebody has more information.

Charles

On Fri, Nov 20, 2009 at 10:08 AM, Mark S. Waterbury
<mark.s.waterbury@xxxxxxx> wrote:
Do not forget about Access Path Sharing.

DDS logical files can "share" access paths (including with pre-existing
SQL indexes), while SQL indexes never share access paths (due to
conformance with ANSI SQL standards, apparently.)

So it is probably still a "good idea" to create SQL indexes first, then
create any DDS Logical Files, so that those LFs can potentially share
access paths with some of your SQL indexes.

Mark S. Waterbury

 > Birgitta Hauser wrote:
Instead of creating the SQL index first, can I just specify 64k into
the

PAGESIZE keyword?

Yep.
BTW that's why I wrote "per Default".
Sometime ago I asked Mike Cain something about indexes and page sizes.
He
suggested not to talk about the different page sizes that can be
specified
since V5R4 for logical files and SQL indexes. The IBMers will not speak
about this subject either.

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: Friday, 20. November 2009 15:05
An: Midrange Systems Technical Discussion
Betreff: Re: AW: Ideas for logicals (index).


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.


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

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.