I bring nothing but bad news this time :)
Use of UPPER is considered 'translation' by the DB2 engine and translation
(NLS - National Language Support) is not supported by SQE even at V5R4.
This particular item will take a while to implement in SQE so I don't expect
that to change soon, but it will eventually.
So use of UPPER is what's causing your statement to go to CQE path.
Is it possible to avoid UPPER? Either by forcing 'a' to upper in the table,
or forcing the comparison value to be the match the case in 'a'?
I'd recommend using *LANGIDSHR (shared weight) indexes to avoid use of UPPER
but that'll probably cause it to go CQE as well.
Then you use LIKE clause. It's not clear if you use leading wildcards from
your example. If you do, index is HIGHLY unlikely to be used, so no matter
what indexes you build over 'a', they most likely will not be used (almost
guaranteed).
Oh, BTW, use of LIKE clause prior to V5R4 will also force your SQL statement
down CQE path. So, it seems like you're going to go down path regardless.
Don't despair, that's not necessarily a bad thing. I mean, CQE has been
running all queries on as400 for more than a decade and all of us have been
using it just fine.
Then you use inequality operator on 'b'. This will cause CQE optimizer to
think it'll probably bring 90% of the rows in, so it'll be biased against
using an index. This in only one of the costing indicators it uses so this
is by no means the prevailing factor in the decision to use index for
implementation or not. Again, no reason to despair, but I bring it up just
for completeness sake.
I don't think pushing selection (WHERE clause) down to final SELECT is a
good idea. I believe you're better off selecting 'few' records from
underlying tables and then having DB2 engine work with a 'small' subset of
records in the final, 'unionized' result set.
As for your thought on keyed LF being used 'under the covers' just the same
as SQL index, yes, you can think of it that way. 'Data space index'
internal data structure is the same for both keyed LF & SQL index and that's
where the binary radix tree structure is maintained (EVI is a whole
different deal). Prior to V5R4 page size is what made performance
characteristics of keyed LF & SQL index slightly different, but you don't
have to worry about this level of detail. Bottom line is that keyed LF is
considered by the query optimizer for statistics and alternate
implementation method.
Finally, what could you do and leave statement as is.... building index over
each of the individual tables on columns A,B seems like the right thing to
do, taking into account caveats I mentioned above.
Well, gotta go to a meeting. Hope that helps.
Elvis
Celebrating 10-Years of SQL Performance Excellence
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[
mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of eftimios pantzopoulos
Sent: Wednesday, April 04, 2007 2:20 AM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL, Unions, Indexes & Logical Files.
Last week I posted a question as per the subject line and I got some
interesting discussion out of it. I've been busy since, but want to gnaw at
the bone some more.
The actual SQL I was asking about is as follows and is an embedded SQL
(a,b,c are simple representations of the actual fields, and DLLT464 is a
logical built over the physical DLPT460). As you can see it's the same SQL
over the 5 variants of the file:
SELECT a, b, c
FROM P2S1ESLDTA/DLLT464
WHERE upper(a) LIKE ? AND b <> ?
UNION ALL
SELECT a, b, c
FROM P3M1ESLDTA/DLLT464
WHERE upper(a) LIKE ? AND b <> ?
UNION ALL
SELECT a, b, c
FROM P4B11ESLDTA/DLLT464
WHERE upper(a) LIKE ? AND b <> ?
UNION ALL
SELECT a, b, c
FROM P5A1ESLDTA/DLLT464
WHERE upper(a) LIKE ? AND b <> ?
UNION ALL
SELECT a, b, c
FROM P6P1ESLDTA/DLLT464
WHERE upper(a) LIKE ? AND b <> ?
FOR READ ONLY WITH UR
With the little bit of knowledge I have so far:
. The SQL will be passed to the CQE for either of two reasons:
. Because DLLPT464 is a logical file.
If I replaced DLLT464 with the physical DLPT460, then
the index represented by the LF would still be invoked, and disregarding the
second condition below, the SQL would be dispatched to the SQE.
. Because the upper function has been used.
Regardless of what I do with the logical, this condition will always force
the SQL to be dispatched to the CQE.
Something Birgitta said pricked my need for an interpretation. This was in
regards to the use of the logical file 'index'. She said:
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.
I'm now interpreting this to mean that the index defined by a logical file
will be used 'under the covers' by the SQE as long as none of the above
conditions is true. In other words at the very low level, an index defined
by a LF is the same as an index created through DDL, and as such the LF is
used.
Instead of using joined logical files, you should create SQL views instead.
Views are much more powerfull than DDS described logical files.
With the example SQL above there are parameter markers so I'm unsure of the
run-time. Would an SQL running over a view be better? Such as the following
(I'm guessing at this point!):
CREATE VIEW lib/viewX AS(
SELECT a, b, c
FROM P2S1ESLDTA/DLLT464
UNION ALL
SELECT a, b, c
FROM P3M1ESLDTA/DLLT464
UNION ALL
SELECT a, b, c
FROM P4B11ESLDTA/DLLT464
UNION ALL
SELECT a, b, c
FROM P5A1ESLDTA/DLLT464
UNION ALL
SELECT a, b, c
FROM P6P1ESLDTA/DLLT464)
Then a new SQL would be used:
SELECT a, b, c
FROM lib/viewX
WHERE upper(a) LIKE ? AND b <> ?
FOR READ ONLY WITH UR
Presumably indexes over a & b might be appropriate? I'll have to check the
index adviser for that.
Would it be better to place the upper(a) in the view creation so that it
doesn't have to be done dynamically? And would that stop the SQL from being
dispatched to the CQE?
Regards, Mike Pantzopoulos
midrange.com
