char(thictl) will zero suppress so 001234567 become 1234567
and FCDKEY8 = 001234567
two solutions
digits(thictl)=fcdkey8 :MS Visual Studio not like (its only in DB2)
right('00000000' concat varchar(thictl),9)=fcdkey8 :Vis Studio required diff syntax
We found a way to pass the whole sql string to i without client editing.
Jim
________________________________
From: Steve Landess <sjl_abc@xxxxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Sent: Tuesday, March 18, 2014 11:11 AM
Subject: Re: when v7r1 SQE is slower than v6r1 cqe
How 'bout:
where CHAR(thictl) = FCDKEY8
- sjl
"J Franz" wrote in message
news:mailman.5507.1395079041.25182.midrange-l@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
midrange.com
