AW: SQL, Logical Files, Unions & Indexes.

Hi,

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.

Not sure on wihch redbook you refer, but SQE can be used, even if DDS
described logical files are defined over the physical files (or SQL Tables)
as long as:
1. DDS described logical files are not specified in the SQL-Statements
2. there are no select/omit clauses in any of the DDS described logical
files defined over the physical one.
   But even with logical files with select/omit clauses the new SQE can be
used, 
   if the option IGNORE_DERIVED_INDEX in the QAQQINI file is set to *YES
(Default is *NO).
   
Believe me it works, we work with an application where all files (physicals
and logicals) are DDS described and there are only a few SQL defined indexes
and views, but almost all SQL statements are executed by the SQE.

BTW it is not neccessary to create DDS described logical files AND SQL
indexes with the same keys. 
SQL indexes can be used with native I/O like any DDS described logical file.


Instead of using joined logical files, you should create SQL views instead. 
Views are much more powerfull than DDS described logical files.

And with the right indexing strategie SQL performs much better than native
I/O even if you are only working with DDS described physical and logical
files.

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 Caura
Gesendet: Sunday, April 01, 2007 13:58
An: midrange-l@xxxxxxxxxxxx
Betreff: 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



----- Original Message ----- 
From: "eftimios pantzopoulos" <eftimios@xxxxxxxxxxx>
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%2
F1%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.