RE: *LANGIDSHR and SQE -- MIDRANGE-L

Vern speaks the gospel.

CQE has been running thousands of businesses critical applications for 20+
years so don't think that just because your query is not using SQE it's
going to perform poorly. It will use an index where it makes sense.

BTW, I'm curious, when you ran visual explain, did your query use an index?
That's the question you should be asking yourself if you're after better
performance. Not to say that table scan won't perform better in some cases.

Actually, in almost every case where your query gets more than 20% of the
data.

Normally, if you want your query to take advantage of an index (regardless
of CQE vs SQE) you'd have to avoid using functions on the field. However in
the case of UPPER I think you have a chance of it using an index as long as
it's created with appropriate sort sequence table (see "Using UPPER SQL
scalar function" section in that article for details).

Don't despair, good indexing strategy will ensure your application performs
well, and don't get stuck in the low level details unnecessarily (i.e. CQE
vs SQE).

Elvis

Celebrating 10-Years of SQL Performance Excellence

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of vhamberg@xxxxxxxxxxx
Sent: Wednesday, May 02, 2007 8:46 AM
To: Midrange Systems Technical Discussion
Subject: Re: *LANGIDSHR and SQE

I suggest that you do the shared weight indexes - CQE is not a bad thing -
SQE was intended to speed up complex queries and it won't matter which
engine is used for many kinds of queries - your mileage WILL vary.

The presence of a supporting index ALWAYS helps, whether SQE or CQE. Without
the index, a table scan will take place, AND the translation will be done.
You can, of course, always use the UPPER function - but the results are the
same - use of a function slows things down. Better if that work is done in
the index.

And read the docs on that QAQQINI setting to see what it relates to.

Eventually I believe IBM will bring everything under the new engine - it all
takes time and resources. This is not an official statement, perhaps a wish,
but it makes sense to this civilian.

HTH
Vern

-------------- Original message --------------
From: "eftimios pantzopoulos" <eftimios@xxxxxxxxxxx>

I came across an excellent tip in a Centerfield Technology Newsletter(Vol

Issue 2) concerning the use of *LANGIDSHR. I actually got it working and

was

able to retrieve records via SQL with the same column value regardless of
case. This involved creating an index while the STRSQL task was set to
*LANGIDSHR.

I was researching this in order to try and overcome the problem of v5r4
sending the SQL to the CQE and I optimistically thought that this might
overcome that problem and thus despatch it to the SQE. Alas and alack!

When

I looked at the Visual Explain, the 'Reason SQE was not used' was -
'Translation Required'.

I presume this is something to do with the upper/lower case process in

using

the index?

Is it worth pursuing this path? Or is there no way of overcoming the UPPER

barrier to the SQE? Something like the IGNORE_DERIVED_INDEX setting in
QAQQINI?

This is a continuation of a post I made a few weeks ago so I'm not hopeful

as *LANGIDSHR came up in that topic as well.

I tried telling my superiors that we could change every program which
populates the table to force an upper, however this is not really feasible

as it involves names which are used in communications.

Looks like we're stuck with the CQE which means there's no point in

creating

indexes to assist the SQL as they'll be ignored. Does this mean that CQE
will always do a full table scan? Or should we retain the logical file in
the SQL as it's being despatched to the CQE anyway?

My head is spinning!

Thanks.........

_________________________________________________________________
Advertisement: Silverchair: Young Modern - listen to full tracks plus win
gig tickets!

http://ninemsn.com.au/share/redir/adTrack.asp?mode=click&clientID=772&referr
al=c

ompetitionpage&URL=http://music.ninemsn.com.au/silverchair