Chuck hit on a point that bit us a while back...Indexes.
We are at 7.1 TR6. When we loaded our latest cume, the SQE started acting a bit peculiarly.
We had a RRN() derived field on a table and when the cume was loaded, the indexes that had been temporarily created by DB2 disappeared.
The table, now having no indexes at all, routed through the SQE via a less performance enhanced path. This is from IBM. Apparently, the mere existence of an index, even if it didn't meet the needs of the current SQL request, caused the SQE to progress down a more performance enhanced path.
So we created an index on the table (over the derived column since we needed it anyway) and performance improved by > 30% for this SQL's execution.
Indexes, while not a cure-all, are certainly important to the choices made by SQE.
Mike Cain and Kent Milligan of IBM's DB2 for i Center of Excellence co-wrote this article about indexing strategies that I found very helpful:
http://www-304.ibm.com/partnerworld/wps/servlet/ContentHandler/stg_ast_sys_wp_db2_i_indexing_methods_strategies
Concerning your indication that no index advice is given, if the SQL contains any derived columns, the index advise is not generated.
For example; if you've got a WHERE clause comparing UPPER(LastName) = UPPER(:Parm_LastName), then you will not get any advice.
Select LastName, FirstName
From Employee
Where Upper(LastName) = Upper(:Parm_LastName)
And it will perform poorly (probably a table scan) because your index actually needs to be on UPPER(LastName) not LastName.
Steve Needles
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Friday, March 14, 2014 12:12 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: when v7r1 SQE is slower than v6r1 cqe
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.
--
Regards, Chuck
--
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 communication, including attachments, is confidential, may be subject to legal privileges, and is intended for the sole use of the addressee. Any use, duplication, disclosure or dissemination of this communication, other than by the addressee, is prohibited. If you have received this communication in error, please notify the sender immediately and delete or destroy this communication and all copies.
TRVDiscDefault::1201
midrange.com
