RE: Using a view to force SQL to SQE

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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