Hi,
There must be a misunderstanding:
1. The new OLAP ranking functions can only be used by the SQL Query Engine
2. When DDS described logical files are specified in a Select-Statement the
execution of the SQL statement will automatically be rerouted to the CQE.
Instead of using a DDS described logical file, you have to specify the
physical table or a SQL view
3. Even when specifying the physical file the SQL statement may be rerouted
to the CQE because of the existence of logical files with
Select/Omit-Clauses
or DYNSLT defined over the physical files.
The query optimizer of the SQE cannot handle this kind of access path.
But there is a solution:
Copy the QAQQINI-File from the QSYS-Library into a library of your Choice
by using CRTDUPOBJ (or iSeries Navigator Database)
In the QAQQINI-File is an option IGNORE_DERIVED_INDEX that is set to *NO by
default.
That means all access paths (including those in DDS described logical files
with select/omit clauses) must be considered. In the copied version of this
file just set this option to *YES. Now only access paths that can be handled
by the SQE will be considered and the query can be executed by the SQE.
To use this copied QAQQINI-File you have to execute the CL-Command CHGQRYA
QRYOPTLIB(MySchema)
Be aware that you may have to create additional indexes with the same keys
as in your DDS described logical files with select/omit clauses. The best
way will be, first to delete the DDS described logical files, create the SQL
index with the appropriate key fields and recreate the DDS desrcibed logical
files. In this way the DDS described logical files can share access path
with the SQL Index. The SQL index can be used for SQL statements executed by
the SQE and the logical file can be used for native I/O as before.
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+hauser=sss-software.de@xxxxxxxxxxxx
[
mailto:midrange-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx] Im Auftrag
von Buck
Gesendet: Monday, May 14, 2007 22:33
An: midrange-l@xxxxxxxxxxxx
Betreff: OLAP functions and logical files
A colleague tried to clue me in on OLAP functions (new SQL support
V5R4). Of course when I tried the simplest one I could think of, it
failed with the spectacular message SQL0255 'Function not supported for
query.' Reason code 6.
Go read the book some more. Find out that there are restrictions. This
is the one that applies: a table referenced directly or indirectly in
the fullselect must not be a DDS-created logical file. In the case of
the example statement (below) it refers specifically to a PF, but of
course the optimiser may choose a logical file if it prefers (although
debug doesn't tell me because it error out before telling me.)
Whaaaaa? Who has a database without any DDS logical files? Must be
some large SQL-only server customer is all I can figure. OLAP is quite
useless to me with this restriction.
select rank() over (order by state) as rank,state,count(*)
from custfile
group by state
order by rank,state
midrange.com
