The world is complex enough as it is without IT. That is why I have taken it upon myself to explain what AutoDBE is used for on an IBM i system, so everyone can understand it. Please read this article and let me know if I have succeeded in doing so.
IBM i has an SQL Query Engine (SQE). The word engine says it all: the SQE is the engine that gets, collects and processes all requests for data from files. When it processes these requests, there is a process that checks whether requests for data from files would work faster if an index could be used.
What is an index?
Put simply: imagine the IBM i database as a library full of books. Each book in this library represents one file in the database. Let’s take a book that lists where club members live and what their email addresses are, for example. The person who wrote the book put in an effort to enter all the members in alphabetical order. The only thing they failed to do is adding a table of contents. Just like a table of contents is important for quickly finding information in the book, an index is important for quickly finding information in a database file. So, the index basically is the table of contents of the book.
What does the IBM i Index Advisor do?
So, in the SQE there is a process that says, “I am missing the table of contents in the book in alphabetical order by member name.” The SQE will then generate a file showing what that index should include and for what book it should be used. That file is the Index Advisor table.
The SQE keeps track of how often the same book is used to look up club members, and if the same index is missing time and time again, a process will then create that index in IBM i’s temporary memory. Such an index is called a “Maintained Temporary Index” (MTI).
How long will this Maintained Temporary Index (MTI) be stored?
Since it is an index created in IBM i’s temporary memory, it is not difficult to guess what happens when IBM i is restarted: that MTI is deleted.
The SQE remembers all database data requests for as long as possible. That is, until there is no free space left in the memory. If the SQE runs out of space to remember a previous request, the MTI associated with that request is also deleted.
If the request to collect data from the file is deleted, there is no point in retaining the corresponding MTI, as there is a clear relationship between the request and the MTI. It does not make much sense to forget the request to deliver data, but still remember the index needed to do so. That would also be a waste of space.
The total amount of space SQE uses is also temporary memory, which means this will also be deleted when the system is restarted. The temporary SQE space and the temporary space taken up by MTIs are two separate parts, but both are used temporarily: an IPL/restart of the system will delete both of them.
What is the role of AutoDBE in all of this?
AutoDBE looks at all indexes recommended to be created and will automatically create them (or not) based on settings in the AutoDBE control table. The advantage of this is that MTIs are no longer needed and no indexes are lost during a restart. In addition, AutoDBE also checks whether the index created is used often enough. Again, this is based on the AutoDBE control table settings.
Why would I want to delete an index that has been created?
Here is an example to explain what could be a reason for deleting an index recommended by the SQE.
Suppose we are dealing with a girls’ school and the field gender exists in the file that contains all pupils. It is not hard to imagine that at a girls’ school everyone is a girl. The SQE makes an assumption based on the gender field, which can either be “male” or “female”. The table definition only includes “M” or “F” values for the gender field, and based on this information the SQE makes the assumption that the distribution is 50/50. For a girls’ school, this is obviously wrong, which means that an index on the gender field does not benefit the performance. If the names of all girls at that school were to be queried, that index could be used, but the net result would still be that every record in the file would have to be touched. In that case the index offers no advantage and creating it makes no sense. The index will not be used and it is better to delete it.
Under normal circumstances, following up and optimising these processes is part of a database engineer’s job, resulting in many manual tasks. AutoDBE will automate the database engineer’s manual tasks, so that they can focus on other ones.