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.
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