Right - I was interested in what looked like a connection you made between being on V5R4 and using SQE. And since my post was about EVIs, I didn't see an intrinsic connection, since their by the optimizer in V5R1 (what became CQE) was available then, if not before.

I was on the team at IBM that was testing SQE when it was being developed - had a different name internally, of course. V5R2 was the first release to benefit, and that was relatively limited, compared to what is covered by SQE now. Even at V5R4, you had to modify an entry in QAQQINI to allow use of SQE when there was a select/omit logical on the table being queried. As we've moved forward, SQE has taken on more and more of the work, and with some of the nice low-level stuff, the potential for better performance with SQE is really high. I have seen cases where CQE does run very well, when it makes a good guess, I suppose. That was probably on an earlier release, like V5R3.

Vern

On 3/8/2012 7:53 AM, Jim Oberholtzer wrote:
Vern;

The SQE engine has all the improvements in performance and utilization
of the database in it. The old query engine, CQE, was stablized and
does not have the performance the SQE does. I had one customer running
a Power 5 550 4 way box pushing three processors fairly hard. Once we
built indexes, and got the development staff to create the SQL
(primarily from .Net applications) to allow the SQE to run the queries,
we dropped back to using 1 sometimes 2 processors. With an upgrade to
V7 (from 5.4) they dropped to 50% of CPU on ONE processor. Same
workload on the system, just way more efficient.

Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


On 3/8/2012 7:44 AM, Vern Hamberg wrote:
EVIs have been around at least since V5R1, can you say more about what
matters about V5R4 and SQE? CQE also uses EVIs where it deems it
appropriate.

On 3/8/2012 7:10 AM, Jim Oberholtzer wrote:
It is V5R4 so be sure you are using the SQE instead of the CQE!.

Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


On 3/8/2012 6:26 AM, Vern Hamberg wrote:
Eric

Feel free to create the EVI indexes - they are useful for selectivity
and joining - they don't do anything for sorting. The optimizer uses
them for bitmap processing.

Vern

On 3/7/2012 5:30 PM,elehti@xxxxxxxxxxxxxxxxxx wrote:
On our V5R4M5 system (IBM i o POWER6), finally using System i
Navigator: SQL Plan Cache Snapshots; using Visual Explain for Index
Advisor.
When it recommends a Binary Radix index, I am creating one.
When it recommends an encoded vector index (EVI), I am not doing so yet,
but am researching the matter in the
IBM White Paper " IBM DB2 for I indexing methods and strategies - Learn
how to use DB2 indexes to boost performance"
Excellent.

This white paper lays the foundation for an indexing strategy and design
that delivers high-performance queries and SQL applications on IBM DB2
for i. Both, programmers and database administrators can find
information on indexing to make their jobs easier and improve the
performance of their DB2 for i servers. This in-depth discussion on DB2
for i indexing includes a description of the technology along with
coverage of the DB2 performance tools available to assist with index
analysis and SQL performance tuning.

--
--

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].