Re: SQL, Logical Files, Unions & Indexes.

Birgitta,

(or is there ?) .

is to create a DDS described Logical file over all these tables (or PFs).
In this case I don't see another way but
"A DDS described logical file has to be used in the SQL-statement ".
and therefore

Any suggestions or alternatives are more than welcome.

luc

To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Sent: Sunday, April 01, 2007 3:02 PM
Subject: 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.

--

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.