Re: when v7r1 SQE is slower than v6r1 cqe

Kind of ugly as sin but
VALUES right('00000000' CONCAT trim(CAST(123 AS CHAR(8))),8)
VALUES
00000123


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: J Franz <franz400@xxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 03/17/2014 01:57 PM
Subject: Re: when v7r1 SQE is slower than v6r1 cqe
Sent by: midrange-l-bounces@xxxxxxxxxxxx



Well I spoke too soon...
The visual studio client editor won't pass the digits keyword.

How can I do something like this: cast(thictl as char)= FCDKEY8 but where

result of cast has leading zeros ? Must be code Visual Studio will accept.







btw - FCDKEY8 does have non numeric characters in some records - it's a
generic sorting field of Content Manager, and only the records that have
our control number in FCDKEY8 should be selected (and they have leading
zeros like '002021234'


Jim













________________________________
From: J Franz <franz400@xxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Monday, March 17, 2014 1:16 PM
Subject: Re: when v7r1 SQE is slower than v6r1 cqe


Appreciate all the responses. It appears the derived column in the join
stmt was the killer (trying to join from number to text field (and prob 7
year old code)) - this is 15 lines into the sql stmt and executes 1 - 2
minutes

...FROM DT LEFT JOIN EKD0312S8 ON
FCDKEY8 = CHAR(INSERT(CHAR(THICTL), 1, 0, (REPEAT('0',
LENGTH(THICTL) - CHARACTER_LENGTH(THICTL)))), 9) AND
FCDCODE LIKE 'GBILL%' .... more stmts

was changed to (executes 1 - 2 seconds)

...FROM DT LEFT JOIN EKD0312 ON
digits(THICTL) = FCDKEY8 AND
FCDCODE LIKE 'GBILL%' ... more
stmts

Jim Franz



________________________________
From: "Needles,Stephen J" <SNEEDLES@xxxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Friday, March 14, 2014 4:27 PM
Subject: RE: when v7r1 SQE is slower than v6r1 cqe


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