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.

_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.

This thread ...


Return to Archive home page | Return to MIDRANGE.COM home page