Re: SQL, Logical Files, Unions & Indexes.

Mike,

your setup is a quite normal situation that will happen in many shops.
We have exactly the same situation in our environement
- with of course the same problem - .

( One thing I do not understand in the DB2-definitions is that iSeries
logical files are treated as SQL-views,
although they are views AND indexes at the same time. )


At the moment our environment is as follows:
We have created Logicals in each library over each table.
And we have created "overal" logicals in an  "overal" library.

Not creating an index (a logical I mean) will definitely degrade any reading
of the files (via a general (SQL)-view).
With the logical, as the performance redbooks tell us, the new SQE will not
be used.

Conclusion:
1) we have to stick to the old engine,
but this (including the 20% penalty) will still be better then not having
any access-path  at all.
(=> what might mean: calculating an accesspath each run; as a result each
time creation of a temporary file with sorting, ...)

2) SQL and the query optimizer engines at the moment still have some severe
drawbacks.
(a join using one or more of  these  "overal"-logical files, or creating an
"overal"-join logical file over 2 series of tables  is not possible either,
as far as I have been able to test ...)


Mike, with you I would beg the IBM and DB2 Folks,

Dear developers,
can we hope for any new evolutions in this important area of
data-consolidation in the near future?

luc

To: <midrange-l@xxxxxxxxxxxx>
Sent: Sunday, April 01, 2007 5:45 AM
Subject: SQL, Logical Files, Unions & Indexes.

I'm curious (and confused) about the following scenario:

We have an embedded SQL which selects records from a number of files over
a
number of libraries in order to provide a nationwide view of a business
function.

The files are duplicate objects in different libraries, each library
representing a state, and of course containing data pertaining to the
state
represented by the library.

The SQL brings all the data together into one ODP.

I thought we could speed it up by creating an index as advised by the
index
advisor, but of course an index can only be created over a single table.

However a logical file can be built over a number of files each in
different
libraries.

If I create the logical however, I understand that SQL will route the
request to the CQE instead of the SQE if a logical file is used and we
would
take a 15-20% hit (according to a tuning red-book I read)

Are there any benefits to using a logical to provide an index or should we
just create an index in every library we want to access the file from?


On a more philosophical note:

I get the impression that if we had a pure SQL based environment - in
other
words if only SQL tables, views, and indexes were used - then a programmer
should only create the table required to hold the data, and a DBA would
then
create the views and indexes required to support the functions required?

Thanks In Advance

Regards,
Mike Pantzopoulos

_________________________________________________________________
Advertisement: Find new & used iPods; designer clothing and more. Join
free
at http://www.ebay.com.au
http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Frover%2Eebay%2Ecom%2Frover%2F1%2F705%2D10129%2D5668%2D323%2F4%2F%3Fid%3D3&_t=760348364&_r=Findnew&_m=EXT

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