AW: SQL, Logical Files, Unions & Indexes.

Hi,

Just a question:
Have you ever created a joined logical file with key fields from more than
one physical file? 

As I already mentioned in an other response on this thread, the optimizer
does not care about any access path stored in a logical file. All specified
logical files are handled as views and the SQL statement will be rewritten
by the optimizer based on the physical files with the information (field
selection, join and select/omit) from the DDS described logical file. But
only the CQE can get these information from DDS described logical files. If
you create SQL views instead, the SQL statement can be executed by the SQE.

But you need both indexes and views. An index (access path) can be used to
get fast access to your data otherwise a table scan will be performed.

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 22:53
An: Midrange Systems Technical Discussion
Betreff: Re: SQL, Logical Files, Unions & Indexes.


Birgitta,

in the original message Mike talked about an Index over a series of 
analogous tables in different libraries.
He stated that it's not possible to create an index over all the tables, and

I don't know of any way either.
(or is there ?) .
As you mention "with the right indexing strategie SQL performs much better 
than natibe I/O" .
Therefore it should be possible to create indexes over multiple tables, just

as it is possible to make logical files over multiple files? . (I cannot see
how views, without indexes, are more powerfull than  logical 
files)

So the only solution at the moment -as I see it, the same that Mike 
suggested,-
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 "with the logical (the one
over all the tables) the new SQE will not be 
used" .

Any suggestions or alternatives are more than welcome.

luc


----- Original Message ----- 
From: "BirgittaHauser" <Hauser@xxxxxxxxxxxxxxx>
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%2Fr
over%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.