Simplify DB2 for i5/OS index advice

Learn how to use the new Index Advice Condenser to ease DB2 performance tuning

DB2 for i5/OS on V5R4 has been enhanced to include an index advice condenser feature to simplify the analysis of index advice from the query optimizer. Index advice is accumulated as it occurs based upon query and SQL activity. Much of the raw index advice generated by DB2 has some redundancy and overlap that should be considered when creating indexes based on the advice. This article explains the new index advisor condensing feature, as well as demonstrating how it can be used from the iSeries Navigator graphical interface or accessed directly with a custom user query.

Scott L. Forstie (forstie@us.ibm.com), Senior Software Engineer, IBM

Scott Forstie is a senior software engineer at IBM, and he is the SQL development leader for DB2 for IBM i in Rochester, MN. Before working on DB2, he worked on UNIX® enablement for the AS/400® and S/390® systems.



25 January 2007

Also available in Chinese

The DB2 for i5/OS Index Advisor

The Index Advisor, a feature introduced to DB2 for i5/OS V5R4, records statistics on a system's indexing needs. Each time a query or SQL statement is executed, the Index Advisor records any indexes suggested by the query optimizer to possibly improve performance. Those new to the topic of index advice on DB2 for i5/OS, can refer to "OnDemand Index Advice for DB2 for i5/OS." That basic knowledge will be helpful in understanding this new Index Advice Condenser.

Each time an index is recommended, a new row is added to the system index advice table, SYSIXADV in schema QSYS2. This assumes that the index has not been advised previously. The Index Advisor examines the attributes in the following columns to determine if an index has already been advised by the optimizer:

  • LEADING_COLUMN_KEYS
  • KEY_COLUMNS_ADVISED
  • TABLE_NAME
  • TABLE_SCHEMA
  • INDEX_TYPE
  • PARTITION_NAME
  • NLSS_TABLE_NAME
  • NLSS_TABLE_SCHEMA

The complete layout of the SYSIXADV table can be found in Table 3. If matching advice exists within the table, the existing row is updated. For example, the "Number of Times Advised" entry is incremented, and the "Last Advised for Query Use" is updated with the current time.

In its raw form, index advice can be unnecessarily verbose. Although all entries in the table are unique, some entries can be redundant. One good example of this occurs when the same columns are advised but in a different order and the order of these keys is unimportant. In this case, the columns can be arranged to create one index that covers both instances of advice. This opportunity for reordering of the advised keys is indicated via the "Leading Keys Order Independent" column. The keys listed in this column are the leading keys which could be reordered while still satisfying the index being advised.

Advised index examples

Review some index advice examples to set the stage for the simplification work done by the condenser. The following examples assume all advice is on the same table, same partition, with the same index type and sort sequence.

Table 1. Advised index example 1
Keys advisedLeading keys, Order independent
C1, C2, C3C1, C2, C3
C1, C3, C2C1

In this example, the second row of advice is not pliable. To satisfy the index advice, a permanent index would need to have keys in the order { C1, C3, C2 }. However, the first row of advice has great flexibility. The leading key order independent detail shows that the keys could be in any order, so you can condense the two rows of advice into a single permanent index by reordering { C1, C2, C3 } into { C1, C3, C2 }.

If you extend the example in Table 2 to include a third row of advice, you are still able to conclude that the addition of a single permanent index { C1, C3, C2, C4 } should provide the optimizer all it needs to permanently improve the performance of these queries.

Table 2. Advised index example 2
Keys advisedLeading keys, order independent
C1, C2, C3C1, C2, C3
C1, C3, C2C1
C1, C3, C2, C4C1, C2, C3, C4

Contrasting Index Advisor and condensed index advice

The condense index advice technology requires the following V5R4M0 5722-SS1 PTFs to be installed on the server: SI25391, SI25469, and SI25470.

When index advice is condensed, the raw index advice is placed into a summary form. Some fields within the raw advice are specific to the instance of advice and have no value in a summarized form. Other fields are quite important to preserve and weight the importance of the condensed advice. Table 3 explains, per column, what happens to the raw index advice during index advice condensation. The condensed index advice permits the same iSeries Navigator actions as those found under the Index Advisor, with one exception. Since the condensed index advice does not exist in a permanent form, there is no "Remove from List" action.

Table 3. Condenser column transitions
Column nameColumn headingCondensing action
TABLE_NAMETable over which an index is advisedPreserved without change
TABLE_SCHEMASchema containing the tablePreserved without change
SYSTEM_TABLE_NAMESystem table name on which the index is advisedPreserved without change
PARTITION_NAMEPartition detail for the indexPreserved without change
KEY_COLUMNS_ADVISEDColumn names for the advised indexGenerated
LEADING_COLUMN_KEYSLeading, order independent keys, the keys at the beginning of the Key_Columns_Advised field that could be reordered and still satisfy the index being advisedConsumed and discarded
INDEX_TYPERadix (default) or encoded vector index (EVI)Preserved without change
LAST_ADVISEDLast time this row was updatedMost recent advice timestamp used
TIMES_ADVISEDNumber of times this index has been advisedGenerated through summation
ESTIMATED_CREATION_TIMEEstimated number of seconds for index creationLargest value used
REASON_ADVISEDCoded reason why index was advisedDiscarded
LOGICAL_PAGE_SIZERecommended page size for indexLargest value used
MOST_EXPENSIVE_QUERYExecution time in seconds of the queryLargest value used
AVERAGE_QUERY_ESTIMATEAverage execution time in seconds of the queryGenerated through average
TABLE_SIZENumber of rows in table when the index was advisedValue from most recent advice used
NLSS_TABLE_NAMESort sequence table to use for the indexPreserved without change
NLSS_TABLE_SCHEMALibrary name of the sort sequence tablePreserved without change
MTI_USEDNumber of times an maintained temporary index (MTI) (autonomic index) that matched the advised definition was used because a matching permanent index did not existDiscarded
MTI_CREATEDNumber of times this specific index advice was used to create a MTIDiscarded
LAST_MTI_USEDLast time an MTI was used because a matching permanent index did not existDiscarded

The following SQL statements were executed to generate the raw index advice contained in Figure 1a and Figure 1b.

Listing 1. Database and queries used to generate index advice
 -- Create sample database in CONDENSE schema
 CALL QSYS.CREATE_SQL_SAMPLE('CONDENSE');
 SET SCHEMA Condense;

 SELECT e.firstnme, d.deptnmae FROM department d, employee e 
	WHERE e.job = 'DESIGNER' AND YEAR(e.birthdate) > 1950 AND e.sex = 'M';
 SELECT e.firstnme, d.deptnmae FROM department d, employee e 
	WHERE YEAR(e.birthdate) = 1953 AND e.job = 'DESIGNER' AND e.sex IN ('M');
 SELECT e.firstnme, d.deptnmae FROM department d, employee e
        ORDER BY e.job, e.sex, e.birthdate;

Figure 1b just contains the remaining index advice attributes that could not fit in Figure 1a. Note that the order of the columns under 'Keys Advised' is different.

Figure 1a. Advised index output
Advised index
Figure 1b. Advised index output (continued)
Advised index-cont

In this case, two of the advised indexes have enough key order flexibility to allow the advice to be condensed into a single index. Figure 2 displays the condensed index advice. Besides providing the user with the condensed column key order { JOB, SEX, BIRTHDATE }, the condensed advice includes contextual information to aide in determining the importance of the advice. Columns, such as "Times Advised for Query Use" and "Average of Query Estimates", provide a sense of how beneficial the permanent index could be to this environment. While the "Estimated Index Creation Time" column, helps to determine whether the creation of the index needs to be a scheduled activity.

Figure 2. Condensed index advice
Condensed advice

iSeries Navigator condenser interface

From iSeries Navigator, the condense index advice action can be found anywhere that the Index Advisor is found. Figure 3 displays the graphical interface for the condenser that was accessed by right-clicking on a schema object. The condenser can also be accessed from a table object.

Figure 3. iSeries Navigator condenser interface
Condenser interface

The ability to use the iSeries Navigator interface for the condenser requires that the latest V5R4M0 iSeries Access for Windows Service Pack be installed on your client.

Programmable condenser interface

In addition to the iSeries Navigator interface, the index advice condenser can be accessed programmatically with an SQL statement. DB2 for i5/OS provides a new view, CondensedIndexAdvice, in the QSYS2 schema. Listing 2 shows the data returned by the new CondensedIndexAdvice view. Since the CondensedIndexAdvice view is implemented with a user-defined table function (UDTF), the view itself is read-only. Any attempt to modify the view will fail with an error of SQL0150.

Listing 2. CondensedIndexAdvice view definition
QSYS2.CONDENSEDINDEXADVICE ( 	
TABLE_NAME              FOR COLUMN TABNAME      VARCHAR(258) CCSID 37 NOT NULL , 
TABLE_SCHEMA            FOR COLUMN TABSCHEMA    CHAR(10) CCSID 37 NOT NULL , 
SYSTEM_TABLE_NAME       FOR COLUMN SYS_TNAME    CHAR(10) CCSID 37 NOT NULL , 
PARTITION_NAME          FOR COLUMN TABPART      VARCHAR(128) CCSID 37 DEFAULT NULL , 
KEY_COLUMNS_ADVISED     FOR COLUMN KEYSADV      VARCHAR(16000) CCSID 37 DEFAULT NULL , 
INDEX_TYPE                                      CHAR(14) CCSID 37 DEFAULT NULL , 
LAST_ADVISED            FOR COLUMN LASTADV      TIMESTAMP DEFAULT NULL , 
TIMES_ADVISED           FOR COLUMN TIMESADV     BIGINT DEFAULT NULL , 
ESTIMATED_CREATION_TIME FOR COLUMN ESTTIME      INTEGER DEFAULT NULL , 
LOGICAL_PAGE_SIZE       FOR COLUMN "PAGESIZE"   INTEGER DEFAULT NULL , 
MOST_EXPENSIVE_QUERY    FOR COLUMN QUERYCOST    INTEGER DEFAULT NULL , 
AVERAGE_QUERY_ESTIMATE  FOR COLUMN QUERYEST     INTEGER DEFAULT NULL , 
TABLE_SIZE                                      BIGINT DEFAULT NULL , 
NLSS_TABLE_NAME         FOR COLUMN NLSSNAME     CHAR(10) CCSID 37 DEFAULT NULL , 
NLSS_TABLE_SCHEMA       FOR COLUMN NLSSSCHEMA   CHAR(10) CCSID 37 DEFAULT NULL )

As you can see with the following SELECT statements, writing SQL statements to access the condensed advice is straight forward. As mentioned earlier, the condensed index advice analysis can be scoped either to the table or schema level.

Listing 3. CondensedIndexAdvice query examples
-- Condensed index advice for an entire schema, named CONDENSE
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE WHERE table_schema = 'CONDENSE' ;

-- Condensed index advice for a specific table, named EMPLOYEE in CONDENSE schema
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE 
       WHERE table_name = 'EMPLOYEE'  AND table_schema = 'CONDENSE';

-- Condensed index advice for a range of schemas, where the average 
-- query estimate driving the index advice is greater than 10 seconds
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE 
   WHERE table_schema LIKE 'Q%' AND average_query_estimate > 10 ;

The Condense_Advice UDTF used by the new CondensedIndexAdvice view can also be accessed directly by user queries. Here's the definition of the table function along with a simple example demonstrating how to use the UDTF.

Listing 4. Condense_Advice UDTF
CREATE FUNCTION QSYS2.Condense_Advice(TABLE_SCHEMA VARCHAR(128), 
                                      TABLE_NAME VARCHAR(128) ) 
  RETURNS TABLE(TABLE_PARTITION VARCHAR(128), KEY_COLUMNS_ADVISED VARCHAR(16000), 
                INDEX_TYPE CHAR(14), LAST_ADVISED TIMESTAMP, TIMES_ADVISED BIGINT, 
                ESTIMATED_CREATION_TIME INTEGER, LOGICAL_PAGE_SIZE INTEGER,
                MOST_EXPENSIVE_QUERY INTEGER, AVERAGE_QUERY_ESTIMATE INTEGER,
                TABLE_SIZE BIGINT, NLSS_TABLE_NAME CHAR(10),NLSS_TABLE_SCHEMA CHAR(10))
  LANGUAGE C 
  NOT DETERMINISTIC 
  READS SQL DATA 
  CALLED ON NULL INPUT 
  SCRATCHPAD 325064 
  DISALLOW PARALLEL 	
  FINAL CALL 
  CARDINALITY 1 
  EXTERNAL NAME 'QSYS/QDBSSUDF2(CONDENSE_ADVICE)' 
  PARAMETER STYLE DB2SQL;   


-- Query the condenser UDTF directly, providing selection criteria and ordering 
-- the results of the 15 most important condensed entries
     SELECT * FROM TABLE(QSYS2.CONDENSE_ADVICE('CRPDTA','B123456')) AS a
       WHERE a.table_size >(1024*1024) AND MONTH(last_advised)= MONTH(CURRENT TIMESTAMP)
     ORDER BY average_query_estimate DESC 
     FETCH FIRST 15 ROWS ONLY ;

Summary

The use of index advice, whether condensed or raw, is a powerful resource to improve any index strategy. An important component to consider before acting upon this advice is to review the existing indexes and their usage statistics. Since an implied maintenance cost exists for any index, its common for performance tuning exercises to attempt to limit the number of permanent indexes. The morale of this story is to review the condensed index advice to identify opportunities for improvement, but also review index usage statistics before and after making any changes.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, IBM i
ArticleID=191992
ArticleTitle=Simplify DB2 for i5/OS index advice
publish-date=01252007