Find the number of times an SQL Index had been used

When you are building SQL DDL Indexes to improve performance it is also imperative to find Indexes that are not being used. Having identified those a decision can be made on whether to delete these unused indexes, or not.

Finding this information introduced me to a SQL View I had not used before: SYSTABLEINDEXSTAT

SYSTABLEINDEXSTAT contains the columns I want, the number of times the index has been used and the date it was last used. Alas, the view SYSINDEXES does not contain that information.

I always recommend that, on the partition you use, you run the following statement at least once to see all of the information that is available to you:

SELECT * FROM QSYS2.SYSTABLEINDEXSTAT LIMIT 10 ;

Using something better than DSPDBR

I am sure we have all used the Display Database Relations command, DSPDBR, to establish any file's or table's dependent objects. We can also use a SQL Table Function to give us more (better) columns of data than the DSPSBR command does.

But before I start giving examples of using this Table function I need something to work with. Let me start with a very simple DDL table:

01 CREATE TABLE MYLIB.TABLE1 02 (FIRST VARCHAR(20))

I do not need to insert data into the table.

Next I will create an Index over my Table:

Getting information about MTI using SQL

Maintained Temporary Indexes, MTI, are SQL Indexes that have been created automatically by the Db2 optimizer, without any outside intervention. The reason an MTI is created is there is no suitable existing Index to meet the requirements of a SQL query. MTI is just like any other SQL Index in my IBM i systems. MTI are temporary, as is suggested by their name, and are deleted when the partition is IPL-ed. A MTI might not be recreated the first time the SQL query is executed, it may take several times before the Db2 optimizer decides it is advantageous to create it. Therefore, to optimize your systems it is a good idea to review the Index Advisor on a regular basis, to determine if there are Indexes that should be created to alleviate the need for a MTI.

This begs the question: How can I see what MTI currently exist on my partition?

Fortunately there is a way using the MTI_INFO Table function.

The Table function has two optional parameters:

1. Table schema
2. Table name

A better way to find which file an Alias was built for

Earlier this year I described a way I could retrieve the schema/library and table/file that a SQL Alias had been built over. over the last few months I have discovered several other ways that will allow me to easily get to that information, and even the member too.

I tend to use SQL Alias for coping with SQL's inability to easily handle multi member files. I could use the Override Database command, OVRDBF, too but I prefer the ease of using the Alias as I can create, use, and then delete the alias all within the same program.

Before I start showing examples, I am going to need to create an Alias, ALIAS_3. Here I am going to create an Alias that will based on the source file DEVSRC, in my library MYLIB, and the source member TESTRPG:

CREATE OR REPLACE ALIAS MYLIB.ALIAS_3 FOR MYLIB.DEVSRC (TESTRPG)

Copying multiple spool files into one PDF

The task came in: I needed to take many spool files from an output queue and convert them into just one PDF. I have written about converting individual spool files into PDF, but now there was the need for the to be only one PDF.

My solution would need to:

1. Merge all of the spool files into one spool file
2. Convert the one spool file into a PDF in the IFS

Both of these I have done separately on many occasions; this was the first time to do them together.

I cannot use the spool files I used for this task, but I have two spool files in my personal output queue: