|
Hi, Eric
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
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 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.