News
Abstract
The list of key columns in the KEY_COLUMNS_ADVISED column of the system index advice table QSYS2.SYSIXADV includes ordering information.
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Database Engineer (DBE) Enhancements > Index advice ordering information in key column list
The list of key columns that is provided in the KEY_COLUMNS_ADVISED column of the system index advice table QSYS2.SYSIXADV is enhanced. The DESC keyword is appended to the column name when the key is advised in a context where descending ordering is needed. This allows the key list to be matched with the KEY_DEFINITION column returned by the QSYS2.MTI_INFO table function.
For example, index advice for the following query:
For example, index advice for the following query:
SELECT * FROM EMPLOYEES ORDER BY FIRSTNAME ASC, LASTNAME ASC, HIREDATE DESC
previously would have generated the following value in KEY_COLUMNS_ADVISED:
FIRSTNAME, LASTNAME, HIREDATE
Now it is formatted like this:
Now it is formatted like this:
FIRSTNAME, LASTNAME, HIREDATE DESC
This change will not affect the key columns already included in existing index advice. New instances of advice containing DESC columns will not be combined with existing advice, because the detail in the KEY_COLUMNS_ADVISED column will differ.
This change occurs with the following:
IBM i Db2 for i 7.6: PTF Group SF99960 level 2
IBM i Db2 for i 7.5: PTF Group SF99950 level 11
This change will not affect the key columns already included in existing index advice. New instances of advice containing DESC columns will not be combined with existing advice, because the detail in the KEY_COLUMNS_ADVISED column will differ.
This change occurs with the following:
IBM i Db2 for i 7.6: PTF Group SF99960 level 2
IBM i Db2 for i 7.5: PTF Group SF99950 level 11
The changed behavior allows the user to join the QSYS2/MTI_INFO() table function with QSYS2/SYSIXADV (raw index advice) or QSYS2/CONDIDXA (condensed index advice).
Figure 1. Discover MTIs that exist at this moment
Figure 2. Review historical index advice
Figure 3. Join MTIs to their corresponding index advice
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001go6AAA","label":"IBM i Db2-\u003ESQL Services \/ table functions"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.5.0;and future releases"}]
Was this topic helpful?
Document Information
Modified date:
07 October 2025
UID
ibm17246484