Reducing the amount of unnecessary Index Advisor data

I do check the Index Advisor on a regular basis to see what is recommended. I have found that this perspective can become overwhelmed by old data. Who cares what index were advised over a year ago? I either addressed that advice, or the program(s) were changed that used the files in that way.

Every six months I go into the tool and clear the data. This way I will only see the recent recommendations. I have an entry in my calendar to remind me to do this. Wouldn't it be nice if this could be automated.

As part of the latest Technology Refreshes, IBM i 7.6 TR1 and 7.5 TR7 a new Global variable is introduced that will automate the purging for me.

This Global variable is called QIBM_SYSIXADV_BY_DAYS, and is found in the library SYSIBMADM. It contains an integer for the number of days to retain the Index recommendations for. The default is 365 days.

I can view the number days the data will be kept by using the following SQL statement:

01 VALUES SYSIBMADM.QIBM_SYSIXADV_BY_DAYS

Which will return the following:

00001 ------ 365

If I change the value within the Global variable that number will only be retained for this job. All other jobs see the default, 365.

To make the change permanent I must re-create the Global variable with a new default. Six months is approximately 180 days, and I only want to retain that duration's data. To accomplish this I must recreate the Global variable:

01 CREATE OR REPLACE VARIABLE SYSIBMADM.QIBM_SYSIXADV_BY_DAYS 02 INTEGER DEFAULT 180

I can check that the change was successful using the VALUES statement again:

01 VALUES SYSIBMADM.QIBM_SYSIXADV_BY_DAYS

The result shows success:

00001 ------ 180

If I want to change the time period of index recommendation I can execute the CREATE OR REPLACE VARIABLE statement again with a different integer value.

 

You can learn more about the QIBM_SYSIXADV_BY_DAYS Global variable from the IBM website here.

 

This article was written for IBM i 7.6 TR1 and 7.5 TR7.