Michael,
Vern and Birgitta covered all of the main points on SQE vs CQE. There are a
lot more subtle nuances which are covered in 1000s of pages of IBM redbooks
and manuals.
I am afraid though that you are getting hung up on the details and missing
the big picture.
You want to run case insensitive queries over your data. This is possible
with *LANGIDSHR SRTSEQ but it is not handled by SQE. So what? That just
means it'll go down the old, trusty, CQE path.
Nothing wrong with that as long as it performs well. What's going to make
it perform well is having good indexing strategy in place (IBM has written
volumes on this topic) and writing "good" SQL code. What's good? In my
mind, anything that performs well and is easy to maintain. In general,
avoid overly complex SQL queries (I've seen some multi-page SQL statements
written by "SQL generator" engines).
Just so we're clear, CQE DOES use indexes. CQE is bonafide DB2 database
engine (as is SQE).
To address your points directly:
* since SQE is the only db engine being enhanced for the past 10 years or
so, ideally, you'd like most of your queries going down SQE path since
chances are your queries will perform better. But it's not a big deal that
they don't if your queries are leveraging the indexes you have in place (SQL
index or keyed LF - doesn't matter, it's the same object internally).
* SQE & CQE both use the same indexes. They may use them slightly
differently under the covers, but bottom line is that both engines can
leverage them and perform well.
* correct. I suspect in couple of releases time (perhaps one), translation
caused rerouting will disappear and SQE will handle it. One thing I don't
see disappearing any time soon is querying logical files directly. If I was
IBM I wouldn't implement that path in SQE ever, thus motivating shops to
move away from DDS paradigm to purely SQL paradigm. But, it's hard to
predict what IBM will do.
What really frightened me was your comment that "there's no point in
worrying about indexes". This is FALSE on any DB platform. They are
critical part for making your database perform well.
To summarize, you can do what you're after (case insensitive queries that
leverage existing indexes) in two ways (that I know of, there may be other
ways):
1) Queries that run under *LANGIDSHR SRTSEQ with indexes being built with
that SRTSEQ as well
2) In my last email I mentioned that there's a good chance your UPPER clause
may even use an index if you build an index specifying appropriate
translation table (i.e. one that weighs lower and upper case the same).
Please consider (and test) this option. Also, note that "translation table"
is a National Language Support (NLS) related object, not a database related
object.
I suspect Birgitta can contribute a lot on both of these options as she's
located in Germany and has to deal with NLS issues first hand.
As for being a newbie, I actually think you're doing very well!
SQL performance is a difficult subject with ever changing rules :)
Hope that helps.
Elvis
Celebrating 10-Years of SQL Performance Excellence
-----Original Message-----
Subject: More SQE CQE Theory.
I'm still curious about the relationship between SQE & CQE
(
http://archive.midrange.com/midrange-l/200705/msg00066.html). Can
someone please comment on the validity of the following perceptions (of
mine)?
* In all that I've read, I thought that the goal of SQL was to
have it run by the SQE and not the CQE.
* The reason for this is that the SQE uses indexes to make the
resolution of the SQL more efficient.
* However, at the moment there are a number of conditions
(reducing with every OS upgrade) under which the SQL will be routed to
the CQE.
The discussion in the earlier topic implied that there was no point in
getting hung up about CQE & SQE, implying that it didn't really matter.
That really confused me because of the amount of literature being
generated on SQL for the iSeries.
I guess I've been under the impression that larger efficiencies can be
made by using a good indexing strategy. But if the indexes can't be used
because of the despatch conditioning, then there's no point.
In other words, unless we can overcome the conditions that switch the
SQL to the CQE, there's no point in worrying about indexing.
Unless of course the CQE has some capacity to be managed by the
developer. Or is the CQE aware of logical file indexes?
Is this a reasonable 'newbie' summary?
Regards,
midrange.com
