On 14-Mar-2014 09:13 -0700, J Franz wrote:
We have a large volume of .net based client code doing SQL requests
to our i. At V6R1 is was performing reasonably well. Couple years ago
we did have issues when IBM made adjustments to CQE/SQE via PTF, and
after DB2 support involved we did find the QAQQINI derived index
setting to *NO would keep performance OK. We do set a copy of it to
*YES and have many apps use that QAQQINI. There are many posts
related to this on this list.
<
http://www.redbooks.ibm.com/redbooks/pdfs/sg247858.pdf>
_IBM i 7.1 Technical Overview with Technology Refresh Updates_
"...
6.3.10 _SQE support of simple logical files_
SQE supports simple logical files in IBM i 7.1. SQE support of simple
logical files has the following restrictions:
• No SQE support of OmniFind using logical files.
• No SQE support of multi-data space logical files.
• No SQE support of logical files over a partition table.
• SQE supports only read-only queries. There is no SQE support of
insert, update, or delete using logical files.
Chapter 6. IBM DB2 for i 189
The QAQQINI file option 'IGNORE_DERIVED_INDEX' continues to be
supported. If IGNORE_DERIVED_INDEX(*NO) is specified, and a select/omit
logical file exists based on <ed: ¿the same?> file of the simple logical
file, then SQE does not process the query of the simple logical file.
..."
Now updated a test partition to V7r1 and finding some of the problem
SQL is going to SQE (and performing badly (68 seconds SQE versus 2
seconds CQE).
The route from the SQL query request to the actual running of the
query includes an /optimizer/ which should make the query perform at
least somewhat optimally. Given the query is conspicuously sub-optimal
by a comparison from CQE to SQE, that seems to imply an obvious defect
with the SQE.?
This is old code, not well optimized, and when we previous sent to
IBM they said (at v6r1) SQE would not handle it well. Now at 7.1 and
more executing code going to SQE, it is again an issue.
Such a conclusion from defect support is not likely to lead anywhere
due to there being no further interaction between the customer and the
DB2 development i.e. the release is under defect support, not
development, so lacking a somewhat formal agreement to include those
queries as part of the testing of new development allows those queries
to be ignored or dropped for lack of any evidence that anyone [will]
depends on those queries operating similarly.
A followup with the [equivalent of a] benchmarking center for the
newer release would be a means to ensure the new release DB2 development
plans for the new release to effect the necessary improvements to the
SQE, or to feedback that there is no intention to make the SQE better
without revisions; most likely because there exist /simple changes/ that
can be implemented for which the SQE processes the revised query and\or
the revised database network acceptably.
Is there any ability to turn off the change (IBM sometimes creates a
data area to control version changes)?
That would be what the INI file [QAQQINI] intends to effect.
Has anyone else felt this?
Surely some have.
It's not a quick rewrite, and those developers long gone.
Often a rewrite is not required. Instead just creating an INDEX or
two [perhaps even SQL and derived], to give the optimizer more
information, is all that is required to make the optimized SQE query
perform nearly as good or even much better than the CQE.
midrange.com
