Bruce makes excellent points.
One thing that is unclear from the original post is why exactly his query
goes down CQE. To figure that out I encourage OP to collect dbmon data and
run the query listed on page 6 here:
http://www.centerfieldtechnology.com/publications/archive/April%202006.pdf
Once you know why exactly it goes down CQE path, addressing the issue may be
easier.
That said, I wouldn't bother addressing it if it performs well in CQE path.
Elvis
Celebrating 10-Years of SQL Performance Excellence
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[
mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of R Bruce Hoffman
Sent: Tuesday, April 17, 2007 4:27 PM
To: Midrange Systems Technical Discussion
Subject: Re: Using a view to force SQL to SQE
I should be more to the point... IGNORE_DERIVED_INDEX does not force SQE,
it only prevents SQE from seeing such logicals during the optimizer
phase.
You don't force SQE, if the dispatcher can go there it does. It can then
change it's mind.
R Bruce Hoffman wrote:
Yes, it's IGNORE_DERIVED_INDEX, but you can't avoid CQE if you directly
reference the logical, which you would have to do to use the mapped
field. Even if you don't directly reference the field, the SQE optimizer
will never be able to use it, so what would be the point?
vhamberg@xxxxxxxxxxx wrote:
Good point on the logical not being SQL-base - but I think one can use a
setting in QAQQINI to force it to go to SQE. I could be mistaken here, so
check the archives for SQE and QAQQINI.
-------------- Original message --------------
From: R Bruce Hoffman <bruce.hoffman@xxxxxxxxxxxxxxxxx>
Well, first, the digits function does not force CQE. CCSID translation
might, but digits by itself does not. You can check this yourself with
Visual Explain in OpsNav. Run the query with Visual explain with and
without the digits function. Scroll all the way down the right hand panel
with the "Final Select" chosen and you will see which engine processed it.
On the logical, yes you can remap, but then if you point the query at the
logical, that will force CQE since the logical is not SQL based.
Vernon Hamberg wrote:
Sorry Mike, no, it won't. A view is nothing more than a container for
an SQL SELECT statement that is executed at the time you use it. It
probably contains the access plan - not access path, like a non-SQL
logical file. The cast to character is performed at runtime, not
ongoing, as it is with a regular LF.
I believe - it has been sooooooo long - that you can use an SST
keyword in an LF to convert a zoned field to character. That would do
what you want. But it won't work with packed field.
Vern
At 05:55 AM 4/17/2007, you wrote:
We have an SQL which uses the digits function to convert a field to
character format for use in a where clause. I understand that this forces
the SQL to be despatched to the CQE. Would creating a view which performs
this transformation overcome this problem? In other words create a new
column which incorporates the digits function on the original table column
and then use the view instead of the original table in the SQL?
Regards, Mike Pantzopoulos
midrange.com
