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