Midrange News for the IBM i Community


Posted by: clbirk
maybe you all knew this...
has no ratings.
Published: 14 Sep 2016
Revised: 14 Sep 2016 - 250 days ago
Last viewed on: 22 May 2017 (498 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

maybe you all knew this... Published by: clbirk on 14 Sep 2016 view comments

as you know, we have an index advisor on the ibm i, to know what indexes we maybe should create. You can view it in like i navigator and navigator for i and it tells you all that.

Well if you have hundreds of tables, bringing up each table and seeing the index advisor to see if it is warranted can be time consuming.

A simple way is to query the system index advisor db table, call sysixadv, and you can use a command like LSTFFD (from bvstools, free) or dspffd (the ibm version, etc.) to see all the fields, but what I wanted to do, was come up with a quick way to find those tables I needed to explore more ignoring those that had little use.

So a simple query like:

select sum(timesadv), tbname from sysixadv
where dbname='SHIPPING'
group by tbname order by tbname

gives me that info, and the result is:  (the reason tbname is second is because it is 260 long and...)

SUM ( TIMESADV ) TABLE_NAME
9                           AB_CODES
10,941                   EXPORT
116                       ORDERS

 

so table export needs to be looked at further and you can look at it with SQL and this sysixadv but it might be easier in "inav" and click to creat the index (if so desired).

The other two tables since this is like 2 years of info, it is not worth considering a table (in my opinion).

 

There are many fields in sysixadv so there could be other values.  I have heard over the years alot about index advisor and have used it in inav but no one (recently, yes there are old (2008 and older articles elsewhere) about sysixadv table.

You would think someone might mention that one. Maybe all of you knew all about this and I was the only one in the dark. 

 

chris

 

 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS