RE: embedded SQL versus Query

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

To clarify - QMQRY will use SQE if it can, according to whatever the 
limitations are. That is because it IS an SQL interface. The II13486 
info APAR for V5R2 still says:

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

But there is a newer presentation that has more on what is allowed -
http://www-03.ibm.com/servers/eserver/iseries/db2/pdf/sqeoverview_v5r3.pdf

This is worth keeping up on, I think, and it is a good reason to stay 
current on the DB group PTF.

Vern

At 11:36 AM 12/21/2005, you wrote:

> Douglas,
>
> Embedded SQL is one of those beasts that wears many faces....  You have
> certain performance issues if you're dealing with dynamic (or Extended
> Dynamic) SQL that you won't have for static SQL.  Using static SQL with
> parameter markers or host variables for selection criteria would usually
> perform better than a dynamic (prepared) statement, because the SQL
> templates can be retained in the "package" for the next run.
>
> There's also a new consideration, in that Query/400, QMQRY, OPNQRYF, and so
> forth can only use what's known as CQE (Classic Query Engine), where
> embedded SQL may be allowed to used the new SQE (SQL Query Engine) which
> contains all the new optimized code that the iSeries has inherited from
> other DB2 platforms.
>
> SQL performance is still a bit like alchemy...  It's more an art than a
> science, but mastery of the techniques can pay off handsomely.
>
>
>
> Eric DeLong
> Sally Beauty Company
> MIS-Project Manager (BSG)
> 940-297-2863 or ext. 1863
>
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Douglas W. Palme
> Sent: Wednesday, December 21, 2005 11:05 AM
> To: Mid Range List
> Subject: embedded SQL versus Query
>
>
> Are there any performance issues to consider when looking at embedded SQL
> versus Query400?
>
> It seems that Query400 might actually run faster, but I can definitely see
> the advantages of creating an SQL statement on the fly per se.
>
> Thoughts, comments? Tell me I'm nuts....ok skip the last one, that's a
> given.
>
>
> If you bought it, it was hauled by a truck - somewhere, sometime.
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.