From 5.4 on, DB2 for IBM i has generated index advice, and System i Navigator has been enhanced every release to make it easier for you to see, understand, and act upon that advice. IBM i 6.1 and above also includes database statistical catalogs and SYSTOOLS, a repository for DB2 for i-supplied examples and tools. SYSTOOLS initially included two procedures that could be used to turn index advice into actual permanent indexes.
With the most recent update from DB2 for i, the REMOVE_INDEXES procedure has been added to SYSTOOLS. Given some basic guidance, this procedure finds and removes underutilized indexes that were created by other SYSTOOLS procedures.
The DB2 for i index advice is based upon current conditions and use of the query engine via SQL. What does an IBM i customer do with this advice? As with most good advice, if it's simply ignored, there are consequences: sub-optimal database performance. This article can help you better understand SYSTOOLS as it relates to index advice and whether to use it to implement a “hands-off” indexing solution or as a quick start to craft your own index strategy.
DBAs, Know Your A, B, Cs
The A, B, Cs of revising an index strategy include:
- reviewing index advice to identify index candidates.
- comparing the composition of the index candidate against existing permanent indexes.
- comparing the usage statistics of the permanent indexes if similar index keys are found.
- creating permanent indexes where needed or scheduling the creation if the based-upon table is large.
- removing underutilized indexes (being sure not to remove indexes that are directly used by applications written to use native database access against an index).
- resetting the index usage statistics of some or all indexes.
At this point, I need to give database administrators their fair due. Database performance analysis, query optimization, and index strategy maintenance are all very complex topics. DB2 for i has made many impressive improvements since 5.4 to position and enable the DBA to be efficient and successful.
Most DBAs are held to standards similar to a doctor-patient relationship. As stated in the Hippocratic Oath, the doctor makes a promise to “First, do no harm." If you look in the Find Out More box, you can find links to additional resources and a DB2 for i performance workshop that can help you to avoid doing harm.
So that said, with the addition of the SYSTOOLS.REMOVE_INDEXES() procedure, DB2 for i has provided tools that can be used to automate a simpler form of the A, B, C plan used by database performance analysts.
SYSTOOLS.REMOVE_INDEXES() Procedure Detail
The REMOVE_INDEXES procedure accepts three parameters as criteria to guide the review of permanent index utilization. The procedure does not consider all permanent indexes when reviewing index utilization, but rather only looks at indexes that were created using the naming scheme contained within the SYSTOOLS.ACT_ON_INDEX_ADVICE and SYSTOOLS.HARVEST_INDEX_ADVICE procedures. Applications that utilize native database I/O (e.g., RPG, COBOL, etc.) may have been coded to directly access keyed logical files, so you need to steer clear of possibly removing those types of database indexes.
Note: even if you have no interest in calling the SYSTOOLS procedures, the SYSINDEXSTAT catalog example can be used to identify low-use indexes related to large, frequently changing tables.
Now let's take a look at the REMOVE_INDEXES parameters:
- P_LIBRARY – CHAR(10): This is the system name of the library that contains the indexes that should be evaluated for pruning. If NULL is passed, the entire database is processed.
- P_TIMES_USED – BIGINT: If the number of times the index has been used by a query and used for statistics is less than this parameter, then the index is considered underutilized. This input parameter is required.
- P_INDEX_AGE – VARCHAR(100): This parameter guides the procedure to evaluate indexes that have existed longer than a certain amount of time. Similar to the P_LIBRARY parameter, this parameter limits the scope of indexes that are reviewed for acceptable utilization. This parameter can be used as a labeled-duration expression in a query against the QSYS2/SYSINDEXSTAT statistical catalog. Any valid SQL labeled-duration expression can be used. This input parameter is required.
As shown in the following example, you can examine indexes within CORPDB, removing indexes older than one month that haven't been used:
You can also examine indexes from all schemas, removing indexes older than one week that haven't been used at least 500 times, like this:
Resetting the Statistics
The QSYS2/SYSINDEXSTAT catalog returns query usage statistics contained within individual indexes. When an index strategy is formed and maintained based upon index advice and index usage statistics, the management of the query usage statistics is an important element to factor into the overall index solution.
Every index has an internal storage area that's used to maintain how often the index has been used by the SQL query engine for statistics and a separate counter of how many times the index has been used during the execution of the query. These counters climb over time for frequently used (i.e., high-value) indexes.
After creating and removing indexes, a best practice is to reset individual index statistics to eliminate the possibility that stale statistics can skew the perceived value of existing indexes.
Consider a simple example where an index strategy is reviewed monthly. Index PRODIDX1 is the first index created over SQL table PRODUCT_LINE. A month later, the query usage statistics look impressive for PRODIDX1, created with keys PRODID and QUANTITY, having been used 15,000 times. The index advisor tells a different story. A similar index, with keys PRODID, QUANTITY, and REGION was advised 5,000 times. Based upon this advice, PRODIDX2 was created. Another month passes and PRODIDX2 was used 15,000 times and PRODIDX1 had marginal use. If PRODIDX1’s query usage statistics hadn't been reset when PRODIDX2 was created, the two indexes would appear to have equal value to the optimizer when, in fact, PRODIDX2 is critical and PRODIDX1 has low value and is a candidate for removal.
The Change Object Description (CHGOBJD) command can be used to reset the query usage statistics for an existing file (table, view, index, physical file, logical file, keyed logical file). When *RESET is used on the USECOUNT parameter, the counts are changed to zero:
In Figure 1, you can see that System i Navigator has an easy graphical control to reset the query usage statistics over many objects.
Select one, some, or all of the indexes within a schema and choose the Reset Usage Counts option. System i Navigator handles the many calls to CHGOBJD for all of the selected objects. Resetting the index usage counts in mass is easier to initiate and won’t have any bad side effects if the index strategy is reviewed on a consistent cadence.
After you’ve selected the SYSTOOLS procedure parameter values that best fit your operation, deployment becomes the decision point. This section shows one deployment option for you to consider. The system startup program can be amended to include a call to the Submit Job (SBMJOB) command. The target command on the submit job invocation is the Run SQL Statements (RUNSQLSTM) command. The RUNSQLSTM command target is a source physical file member that contains two SQL CALL statements targeting ACT_ON_INDEX_ADVICE to create indexes and REMOVE_INDEXES to remove underutilized indexes. This may seem a bit complex, but the steps needed to achieve this automation are quite simple.
Why bother adding this instrumentation to the system startup program? As you can see in the example below, this IBM i installation has chosen to IPL once a week, starting the IPL late on a Friday night. As the system completes the IPL, the QSYS/QSTRUP program is called. Our assumption is that the QSTRUPPGM system value points to the system provided default. Use DSPSYSVAL SYSVAL(QSTRUPPGM) on your system to confirm the name and library of the system startup program.
As shown in the following steps, the calls to the SYSTOOLS index advisor procedures are enqueued during system startup. The SBMJOB command includes controls over when the job should run, and the example shows how to have the Run SQL statements initiated starting at 4 a.m. on Saturday. By initiating the calls to the procedures to begin during a system maintenance window, you can take full advantage of system capacity that may be available prior to bringing the applications back online.
To understand the ACT_ON_INDEX_ADVICE procedure, either directly look at the source code by using the System i Navigator Generate SQL feature, read one of the related SYSTOOLS article referenced in the Find Out More box, or read about it in the Database Performance and Optimization book for 7.1. This example shows how to call ACT_ON_INDEX_ADVICE to consider system-wide advice, looking for index advice where a Maintained Temporary Index (MTI) has been used more than 1,000 times. In this scenario, the query engine is giving a compelling reason to create a matching permanent index.
Here's how to set up IBM i to automatically maintain indexes:
STRSEU SRCFILE(QGPL/QSQLSRC) SRCMBR(INDEXMAINT)
CALL SYSTOOLS.REMOVE_INDEXES(NULL, 500, ' 7 DAYS ');
(F3 and Enter to exit and save.)
STRSEU SRCFILE(QGPL/QCLSRC) SRCMBR(QSTRUP) TYPE(CLP) OPTION(2)
CMD(RUNSQLSTM SRCFILE(QGPL/QCLSRC) SRCMBR(INDEXMAINT) +
COMMIT(*NONE) NAMING(*SQL) OUTPUT(*PRINT))
- Create the SQL source:
- Add these lines (substitute the parameter values where needed!):
- Retrieve and modify the system startup program source code:
- Immediately after the DONE: label, add the following three lines:
- Build and replace the system startup program:
After adopting the use of SYSTOOLS index advisor procedures, you may want to periodically review the set of permanent indexes that have been created. At any point, you can use the following query to find those indexes and return detailed information about the index composition and query engine use.
The SYSTOOLS procedures create indexes within the same schema as the target table. The index name includes the table name, followed by the index type ("_EVI_INDEX_" or "_RADIX_INDEX_") and a trailing counter in character form. To anticipate the effect of an upcoming call to REMOVE_INDEXES, compare the P_TIMES_USED parameter value against the Total_Query_Usage column and the P_INDEX_AGE parameter value versus the Index_Days_Old column using the query in Figure 2.
QUERY_STATISTICS_COUNT as Total_Query_Usage,
DAYOFYEAR(CURRENT TIMESTAMP) –
DAYOFYEAR(CREATE_TIMESTAMP) as Index_Days_Old,
FROM QSYS2.SYSINDEXSTAT A WHERE
INDEX_NAME LIKE '%_EVI_INDEX_%' OR
INDEX_NAME LIKE '%_RADIX_INDEX_%'
ORDER BY Total_Query_Usage DESC
The SYSTOOLS.REMOVE_INDEXES() procedure was delivered on IBM i 6.1 via DB2 for IBM i Group PTF SF99601 Version 21 (available on 10/9/2011) and on IBM i 7.1 via DB2 for IBM i Group PTF SF99701 Version 12 (planned). Visit the DB2 for IBM i Group PTF planning page for DB2 for i Group PTF details at ibm.com/developerworks/ibmi/techupdates/db2/groupptf.
The SYSTOOLS procedures are provided by DB2 for i on 6.1 and above to make it easier for customers to get full value from the database statistics and index advice. The procedures also demonstrate the robust and easy-to-use SQL support available when using DB2 for i. Your choice is three-fold:
- Use what DB2 for i provides “out of the box” in SYSTOOLS.
- Extract the SQL source for the SYSTOOLS procedures and modify or extend the logic.
- Understand the techniques involved in the SYSTOOLS procedures and incorporate them to improve your database operations.
The recent addition of the REMOVE_INDEXES procedures brings IBM i one step closer to having a proactive index advisor. I encourage you to test drive the SYSTOOLS procedures and send me your observations and ideas for additional SYSTOOLS SQL-based tools and examples.
Scott Forstie is a senior software engineer at IBM and is the SQL development leader for DB2 for IBM i in Rochester, Minnesota. Before working on DB2, he worked on UNIX(R) enablement for the AS/400(R) and S/390(R) systems.
Find Out More
The following resources and websites contain information related to this topic:
“SYSTOOLS Brings on the Advice”
"IBM DB2 for i Indexing Methods and Strategies"
“Database Performance and Query Optimization”
DB2 for i SQL performance workshop (highly recommended)
developerWorks for IBM i Technology Updates wiki page