Wednesday, September 27, 2017

Index Advisor helps performance

using index advisor build sql indexes to speed up your sql

As we all start using more SQL to get data from our tables and files, there are things we can do to ensure that our programs are not that "monster" that takes down a system. One of them is to ensure we use the best access path to get that data. Rather than hunt for the "best" logical file/index to match our key, we should always build our Selects, etc, over the physical file/table and allow the Db2 for i engine to find the best access path for us. Personally, I think that is remarkable that the Db2 for i engine will find the best logical file/index to use. If there is not an index that matches what I need, it builds a temporary access path. This temporary access will be reused, within the same job, whenever I run that SQL statement. You may have noticed if you execute a complex SQL statement many time it will get faster the more times it is run, within the same job.

Building temporary access paths can still result in a "monster" being created. The best way to improve the performance of the "monster" is to make sure that there are indexes that match what I need. Rather than having to identify all the indexes myself, I can use Index Advisor and have Db2 for i recommend the index I should build.

The Index Advisor provides an easy to understand interface for the information in the table SYSIXADV. When the Db2 for i engine is running SQL statements it logs information about the temporary access paths it builds into this table. You can go ahead and run a SQL Select over that table to find the advised indexes, but I think you will find the Index Advisor a lot easier to use.


Operations Navigator
icon

The Index Advisor is part of the Operations Navigator, which might be called the "System i Navigator" or "iSeries Navigator" on your system. To get to the Index Advisor:

  1. Open the Operations Navigator
  2. If you have more than one system select the one you want to see information for.
  3. Click on the plus ( + ) next to the Databases.
  4. Right click on the database, in the example below it is called E202307w.
  5. Select Index Advisor.
  6. Select Index Advisor, again.

Getting to the index advisor
Click to open a large image in a new window

When you open the Index Advisor it may not look the way mine does. You will have to click on the column headings to sort it the way you want.

I went looking for any of tables or files the Advisor makes recommendations for in my libraries, RPGPGMn. I found that it had made recommendations for the table I built for the post about Temporal Tables.


First few columns of the Index Advisor

  CREATE TABLE RPGPGM5.PERSON (
  PERSON_ID FOR UNIQUE NUMERIC (10,0)
            GENERATED ALWAYS AS IDENTITY
            (START WITH 1, INCREMENT BY 1, NOCYCLE),
  FIRST_NAME VARCHAR(25),
  MID_INITIAL CHAR(1),
  LAST_NAME VARCHAR(30),
  BEGIN_TS TIMESTAMP(12) NOT NULL
               GENERATED ALWAYS AS ROW BEGIN,
  END_TS TIMESTAMP(12) NOT NULL
             GENERATED ALWAYS AS ROW END,
  TS_ID TIMESTAMP(12) NOT NULL
            GENERATED ALWAYS AS TRANSACTION START ID,
  PERIOD SYSTEM_TIME (BEGIN_TS,END_TS)
  ) ;

There a lot more information that you can see about these advised indexes.


 

Click to open large images in a new window

I only look at the following columns when making the decision whether to build an index or not.

  1. Schema : library where the table/file is in.
  2. Short name : physical file/table name.
  3. Keys advised : the keys for the index.
  4. Times advised for query use : the larger the number the more times this index has been recommended.

So what next? I find the index I want to build, then right click on the line.


Getting to the SQL code

I can either create the index or I can show the SQL for the suggested index. Personally I always show the SQL as I copy the source, place it into a source member, make any changes, and compile it.


Suggested index's SQL code
Click to open a large image in a new window

As I built my SQL statements over the table PERSON the next time I run the "monster" program it will use the new index, and run faster than it did before.

If you are not able to use the Operations Navigator you can get the same information about advised index keys from the table SYSIXADV. I have just selected the columns I find useful:

01  SELECT SYS_DNAME AS LIBRARY,
02         SYS_TNAME AS TABLE,
03         FIRSTADV AS FIRST_ADVISED,
04         TIMESADV AS NBR_TIMES,
05         KEYSADV AS ADVISED_KEYS
06   FROM QSYS2.SYSIXADV
07  WHERE SYS_DNAME = 'RPGPGM5'
08    AND SYS_TNAME = 'PERSON'
09  ORDER BY TIMESADV DESC,FIRSTADV

Which gives me the same as what I saw in the Index Advisor:

LIBRARY   TABLE    FIRST_ADVISED                 NBR_TIMES
RPGPGM5   PERSON   2016-09-27-03.47.11.436098           33
RPGPGM5   PERSON   2016-09-30-03.38.24.281928           17
RPGPGM5   PERSON   2016-09-25-07.18.38.142613           14
RPGPGM5   PERSON   2016-09-30-04.33.32.139177            3

ADVISED_KEYS
PERSON_ID
BEGIN_TS
PERSON_ID
FIRST_NAME

I can then build my index statement manually with the keys recommended in the Advised Keys column.

You have found that the Index Advisor will advise indexes for physical files too. You can build indexes over physical files, or, if you desire, translate the index's information into a logical file.

I do not recommend that you build every index the Advisor suggests. If you do find one that has been advised, let's say, 100,000 times then I would definitely look into building it.

 

You can learn more about this from the IBM website:

 

This article was written for IBM i 7.3, and should work for earlier releases too.

6 comments:

  1. Hi Simon,

    Firstly, thanks for all the great articles you publish. I am constantly learning something new from your posts.

    I'm currently using index advisor found in Operations Navigator but keep meaning to take the time to use, and learn, the index advisor in ACS Schemas. Was there a reason for you not using the ACS Schemas version?

    - Richard

    ReplyDelete
    Replies
    1. I do not have access to the software, licenses, etc. to be able to download ACS on the free service I use.

      Delete
    2. Simon, ACS is free. You can download it from IBM website.

      Delete
    3. Satid S. IBM ThailandNovember 1, 2017 at 7:46 AM

      Actually, ACS itself does NOT contain System-wide Index Advisor tool. When you use this tool from ACS, ACS itself will invoke System-wide Index Advisor from Navigator for i tool for you. Here is more details : http://www-01.ibm.com/support/docview.wss?uid=nas8N1019797

      In my past experiences (as an IBMer, I deliver useful index creation service to many customers), I prefer using System-wide Index Advisor tool from IBM i Navigator for Windows tool (as described in this blog post) just becuase it is comparatively faster (on a properly tune Wintel PC). The only catch now is that the Windows-based tool does not support Windows 10 or any later releases.

      Delete
  2. Simon, excellent article as always. I would add that when tuning queries, its often best to sign off/on before running another test in order to start fresh with an empty cache. Otherwise one can sometimes get false results. This has been my experience...

    ReplyDelete
  3. Simon,

    If you have license to use Client Solutions you have a license to use Access Client Solutions - it's the same license. IBM no longer supports Client Access on Windows 10.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.