A good quiz question for this article could have been: “When was the Index Advisor introduced?”. When searching the Internet I could find information going back to V3R6, which was released back in 1995. I think it is fair to say, we have all heard about it, but unfortunately we can not say that of the use of it. In most shops the Index Advisor is ignored, which is a pity. Recently I witnessed that after the creation of an advised index, the runtime of a SQL select request went down from nearly 28 seconds, to 115 ms.

Below a screen taken from an advised index of an environment running JDEdwards:

In most cases we simply press “Run All” button, maybe we decide to change the library or should I say collection in which the index is created, but that is about it. In this case I would like you to have a second look at the image and have a look at the sentence on line 3, which states: “When creating this index the database connection should have a sort sequence of *HEX.”.
Now you may wonder, is this important and if *HEX is an option, are there more flavours. To answer this question, we need to have a look at the ODBC connection details. In order to do so, we select the option “Edit JDBC Configuration – default” from the Connection menu in the bar. When doing so, we select the tab “Language”. The default value for the “Sort sequence table” value is “None (Sort by Hexadecimal value)”. After using the pull down arrow, can select two other options: “Shared-weight table” and “Unique-weight table”. The blue question mark button in the bottom will help us filling in what they mean.

If we select one of them, we have the option to select a language as shown below:

When changing the Sort Sequence table, please be aware that when doing so we need to reconnect if we already are connected. ACS does this for us with the popup screen below:

The Sort Sequence table value is also available on a job level and can be found by using the DSPJOB command. When using the CHGJOB command and changing the parameters “SRTSEQ” and “LANGID”, we have the options “*LANGIDUNQ” and “*LANGIDSHR” for the first parameter and a list of languages for the second.

If you try using the SQL statement CL:CHGJOB;” and decide to prompt it you will see that your change of the sort sequence is not picked up. This is most likely a bug for which a case has been opened already.

I hope the information given here will help you with using the full potential of the Index Advisor. Within the option Advised Indexes by starting from Schemas in ACS you have the option to select more then one advise. When doing so one SQL script is generated, so please keep an eye on the sort sequence advised for every index.