Re: Index advisor (was RE: Interactive memory pool...how much to allocate per user?)

[Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>]

There are several good redbooks on SQL performance and tuning.

Start with :  SQL Performance Diagnoisis on IBM DB2 - SG24-6654-00.  
It's a bit old (2006) but still has very good information in it.

Another good one is :  IBM DB2 for i indexing methods and strategies by 
Michael Cain and Kent Milligan.  (2011)

If you google a bit you will find several more good references.

Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


On 12/10/2012 11:41 AM, Porterfield, Sean wrote:
> Interesting stuff.  It immediately informed me of a "new" SQLRPGLE program I didn't know about.  I'm not really sure of the rules the index advisor (or SQE) uses.  One suggestion on a key matches the first key in an existing logical file that has multiple keys and no select/omit.  It's odd to me that it wouldn't just use that file.  Though it does suggest "Binary Radix" for the index type in case that makes a difference.
>
> Some others match as well, but most I found are logicals over multiple physical files or contain select/omit that might not match.
>
> If it says "Estimated Index Creation Time" is 19:12:15, is that over 19 hours?
> --
> Sean Porterfield
>
> -----Original Message-----
> From:midrange-l-bounces@xxxxxxxxxxxx  [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Oberholtzer
> Sent: Monday, December 10, 2012 11:28
> To: Midrange Systems Technical Discussion
> Subject: Re: Interactive memory pool...how much to allocate per user?
>
> OK, that completes some of the picture.  The Quadrant and Compleo are most likely using quite a bit of SQL, as are the the stored procedures of course.  That would explain quite a bit of the *MACHINE and *BASE faulting.
>
> With Navigator, check out the advised indexes, and see if there are some indexes taking over about 10 seconds to build being created routinely.
> Consider building them permanently.  Also, the prestart jobs QSQSRVR and QZDASOINIT  should be moved into a shared pool away from *BASE.  That will help a bunch.
>
> Jim Oberholtzer
> Chief Technical Architect
> Agile Technology Architects