× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi, Eric

Actually, any true SQL statement is a candidate - embedded or not. IIRC, QSQROUTE is the top-level program that decides what goes where. I haven't looked at a job trace for awhile to see the order of what gets called.

The real determinant is the stuff the statement is trying to do. E.g., a LIKE predicate will always go to CQE, I think. At least that's what is said in the info APAR II13486, which is well worth reading. And there's more at <<http://www.iseries.ibm.com/db2/sqe.html>http://www.iseries.ibm.com/db2/sqe.html>, which is referenced in the APAR.

Here's a little from that APAR - it IS from last year, and some things have changed - check that web site and Memo to Users, etc.

Queries with the following attributes continue to run with the
Classic Query Engine:

- Non-Read (INSERT with subselect can use SQE)
- LIKE predicates
- UNIONS
- View or Logical File references
- Subquery
- Derived Tables & Common Table expressions
- LOB columns
- NLSS/CCSID translation between columns
- DB2 Multisystem
- Non-SQL queries (QQQQry API, Query/400, OPNQRYF)
- Queries with the STAR_JOIN QAQQINI attribute

In addition, the existence of certain keyed logical files can
result in a query initially being routed to SQE and then
rerouted back to CQE for processing, resulting in a slight
performance degradation.  The keyed logical files that cause
this behavior have the following attributes:

- Select/omit specification
- Logical files spanning multiple members
- Derived keys (for example, renaming fields, adding a
 translate or only selecting a subset of the columns)
- Alternate collating sequence on a key field
- Sort sequence specified for an index or logical file

Have fun
Vern

At 02:35 PM 6/16/2004, you wrote:
Vern,

LOL! I agree that there's probably a bit of marketing flair in this, but
we've been hearing about the convergence of DB2 platforms from IBM for eons
now, and I believe this is just the most recent in a series of incremental
changes needed to bring this to us.

Like most products in transition, I'd expect that hardly anything will
actually use the SQE.  I'd guess that embedded SQL (which uses QSQROUTE)
will continue to use CQE, just because that's the one the SQL preprocessor
was designed to use.

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Vern Hamberg [mailto:vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx]
Sent: Wednesday, June 16, 2004 1:59 PM
To: Midrange Systems Technical Discussion
Subject: RE: V5R3: OPNQRYF vs Imbedded sql


Eric

That's the official party line, as I see it. Not that it's not true, just
that it smells of a marketing "story" to "unify" the iSeries with the rest
of IBM. That IS a problem, as many at the other divisions still might not
know that the 400 still exists in some manifestation.

The beauty of the object-oriented design is, they can just plug in lots of
different processing node types into the access plan tree. And the group
putting it together was, when I was contracting there a couple years ago,
trying to take advantage of what other divisions had already learned. I'm
sure it still is. That did not used to be how it worked - Rochester was
this fortress - the book is right, in my view. That's changing a little, I
think.

I do not know why the non-SQL tools are not sent down to SQE (runs
completely in SLIC). I suspect they will always be around but will not
benefit directly from any performance gains that the new engine provides.
SQE for now is limited to read-only, IIRC. And some things in an SQL
statement will still force it back up to CQE. There's an info APAR
somewhere that gives the conditions for each path. One of the problems was,
a statement might be directed to SQE, which finds other reasons not to
process it and sends it back up to CQE (runs mostly above MI). Don't know
if that is still the case, or does the router (QSQROUTE or something) have
better smarts yet.

Maybe if all functions can be handled by SQE, they'll eliminate CQE and
have all the query engines go through SQE - that'd be nice, but don't hold
your breath. Besides, tens of programmers would need to find other
positions. ;-)

Statistics hold out much promise, IMO. They used to be able to hog your
system - pretty hard-hitting on the old I/O - but that's some better, I
believe. I don't have first-hand info on that. But the more the optimizer
can know about the distribution of data in a column, the better it can
decide what to do. Stats will help that when there is not an index on a
column, which already has statistical info.

UffDa - too much said.

Later
Vern

At 01:21 PM 6/16/2004, you wrote:
>Rob,
>
>As I recall, the SQE is written with object oriented design, so that the
>optimized DB2 code for other platforms can be shared with our flavor of
DB2.
>SQE has better statistical analysis capabilities that allow the optimizer
to
>make more intelligent decisions about how to perform efficiently.
>
>Eric DeLong
>Sally Beauty Company
>MIS-Project Manager (BSG)
>940-898-7863 or ext. 1863
>
>
>
>-----Original Message-----
>From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx]
>Sent: Wednesday, June 16, 2004 8:50 AM
>To: midrange-l@xxxxxxxxxxxx
>Subject: V5R3: OPNQRYF vs Imbedded sql
>
>
>I was reading an article at
>http://www.midrangeserver.com/fhg/fhg061604-story01.html
>and it talked about the differences between the newer SQL Query Engine
>(SQE) and the Classic Query Engine.  One of the things it said was that
>OPNQRYF, Query/400, and the QQQQry API all will use the CQE.  Deprecated
>products?  Poorer performers?
>
>Rob Berendt



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.