Wednesday, July 15, 2020

Listing SQL Indexes' keys

using sysindexes and syskeys to make a list of index keys

Most of the time I do not care what the keys are for any SQL Indexes I have built over DDL tables or DDS tables. When I want to extract data from a table or file using SQL I build the statement using the table or physical file, and Db2 of i is smart enough to find the best access paths (logical file or SQL Index) for the statement.

I recently encountered a company where their programmers had been building Indexes. Building Indexes is not a problem, but each programmer had been building their own Indexes without consulting with the other members of the team. The team leader was concerned that they now had many indexes that had the same keys. He asked me if I could help him identify the following:

  1. Indexes built over which table or file
  2. The keys of those Indexes

Fortunately the solution can be achieved by joining two SQL Views together. I have written about the SYSINDEXES View to get information about Indexes before. I could join this with a View I have not written before: SYSKEYS. This View returns a row for a every key an Index has.

But before I can use either of those Views I need a Table over which I can build my Indexes.

01  CREATE TABLE MYLIB.TEST_TABLE
02    FOR SYSTEM NAME "TABLETEST"
03  (FIRST_COLUMN FOR COLUMN "FIRST" VARCHAR(1),
04   SECOND_COLUMN FOR COLUMN "SECOND" VARCHAR(2),
05   THIRD_COLUMN FOR COLUMN "THIRD" VARCHAR(3),
06   FOURTH_COLUMN FOR COLUMN "FOURTH" VARCHAR(4),
07   FIFTH_COLUMN FOR COLUMN "FIFTH" VARCHAR(5),
08   SIXTH_COLUMN FOR COLUMN "SIXTH" VARCHAR(6),
09   SEVENTH_COLUMN FOR COLUMN "SEVENTH" VARCHAR(7),
10   EIGHTTH_COLUMN FOR COLUMN "EIGHTH" VARCHAR(8),
11   NINTH_COLUMN FOR COLUMN "NINTH" VARCHAR(9)
12  )

Line 2: My Table's name is not more than ten characters long, but I am still going to give it a different System Name so that its name will match that of the Indexes I will be building.

Lines 3 – 11: I am also giving the Table's columns short system names too.

Having built the Table I can build the first of the Indexes I will be using:

01  CREATE UNIQUE INDEX MYLIB.TEST_TABLE_INDEX_1
02    FOR SYSTEM NAME = 'TABLETEST1"
03    ON MYLIB.TEST_TABLE
04    (FIRST_COLUMN,SECOND_COLUMN)

I can now use the SYSKEYS View to show the details of the key fields in this index:

01  SELECT INDEX_SCHEMA AS "Idx schema",
02         INDEX_NAME AS "Idx name",
03         COLUMN_NAME AS "Col name",
04         COLUMN_POSITION AS "Col pos",
05         ORDINAL_POSITION AS "Key pos",
06         ORDERING AS "Order",
07         SYSTEM_COLUMN_NAME AS "Sys Col Nme",
08         SYSTEM_INDEX_SCHEMA AS "Lib",
09         SYSTEM_INDEX_NAME AS "Sys Idx Nme"
10    FROM QSYS2.SYSKEYS
11   WHERE INDEX_SCHEMA = 'MYLIB'
12     AND INDEX_NAME = 'TEST_TABLE_INDEX_1'

I am only interested in some of View's columns:

INDEX_SCHEMA: Name of the SQL schema the Index is in. For most of us this is the same as the library's name.

INDEX_NAME: Long name of the Index.

COLUMN_NAME: Long name of key column.

COLUMN_POSITION: Which number column in the Index is this column.

ORDINAL_POSITION: Position of the column in the Index's key.

ORDERING: Sort order, A for ascending and D for descending.

SYSTEM_COLUMN_NAME: System name of the column.

SYSTEM_INDEX_SCHEMA: System library name.

SYSTEM_INDEX_NAME:: System name of the Index.

I gave all of the columns short column heading so that the results would fit the width of this page. The results for the Index I just created looks like:

Idx                                     Col Key       Sys
schema Idx name           Col name      pos pos Order Col Name 
------ ------------------ ------------- --- --- ----- --------
MYLIB  TEST_TABLE_INDEX_1 FIRST_COLUMN    1   1   A   FIRST
MYLIB  TEST_TABLE_INDEX_1 SECOND_COLUMN   2   2   A   SECOND


      Sys
Lib   Idx Nme
----- ----------
MYLIB TABLETEST1
MYLIB TABLETEST1

The second Index just has and additional third key field, THIRD_COLUMN, that is sorted in descending order.

01  CREATE UNIQUE INDEX MYLIB.TEST_TABLE_INDEX_2
02    FOR SYSTEM NAME = 'TABLETEST2"
03    ON MYLIB.TEST_TABLE
04  (FIRST_COLUMN,SECOND_COLUMN,THIRD_COLUMN DESC)

Now I can build my final SQL statement to return to me the following information:

  1. Table schema (from SYSINDEXES)
  2. Table name (from SYSINDEXES)
  3. Index schema
  4. Index name
  5. Position in key
  6. Key column name
  7. Sort order

Therefore, my statement looks like:

01  SELECT B.TABLE_SCHEMA AS "Table lib",
02         B.TABLE_NAME AS "Table",
03         A.INDEX_SCHEMA "Index lib",
04         A.INDEX_NAME AS "Index",
05         A.ORDINAL_POSITION As "Pos",
06         A.COLUMN_NAME AS "Column",
07         A.ORDERING As "Order"
08    FROM QSYS2.SYSKEYS A CROSS JOIN QSYS2.SYSINDEXES B
09   WHERE A.INDEX_NAME = B.INDEX_NAME
10     AND A.INDEX_SCHEMA = B.INDEX_SCHEMA
11     AND A.INDEX_SCHEMA = 'MYLIB'
12   ORDER BY 1,2,3,4,5 ;

Line 8: I join the two Views.

Line 9 and 10: The Index schema and name are the columns that can join the two Views together.

Line 12: Rather than list all of the columns' names I want to order the results by I can a number to denote that column's place in the results.

My results look like:

Table            Index
lib   Table      lib   Index              Pos Column        Order
----- ---------- ----- ------------------ --- ------------- -----
MYLIB TEST_TABLE MYLIB TEST_TABLE_INDEX_1   1 FIRST_COLUMN   A
MYLIB TEST_TABLE MYLIB TEST_TABLE_INDEX_1   2 SECOND_COLUMN  A
MYLIB TEST_TABLE MYLIB TEST_TABLE_INDEX_2   1 FIRST_COLUMN   A
MYLIB TEST_TABLE MYLIB TEST_TABLE_INDEX_2   2 SECOND_COLUMN  A
MYLIB TEST_TABLE MYLIB TEST_TABLE_INDEX_2   3 THIRD_COLUMN   D

To make this more practical for the person who asked for this information I can turn that SQL statement into the one below, that generates an outfile containing the results.

CREATE TABLE MYLIB.OUTFILE AS
(SELECT B.TABLE_SCHEMA,
         B.TABLE_NAME,
         A.INDEX_SCHEMA,
         A.INDEX_NAME,
         A.ORDINAL_POSITION,
         A.COLUMN_NAME,
         A.ORDERING
    FROM QSYS2.SYSKEYS A CROSS JOIN QSYS2.SYSINDEXES B
   WHERE A.INDEX_NAME = B.INDEX_NAME
     AND A.INDEX_SCHEMA = B.INDEX_SCHEMA
     AND A.INDEX_SCHEMA = 'MYLIB')
WITH DATA

In turn this file could then be copied to the IFS and emailed.

 

You can learn more about the SYSKEYS View from the IBM website here.

 

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

9 comments:

  1. You can also use ACS (IBM i Access Client Solutions) and under Schemas expand the Library and select Indexes to view them all.

    ReplyDelete
  2. Why use the cross join and where clauses instead of an inner join on fields? Is it faster? Uses system resources better? Or just personal preference?

    ReplyDelete
    Replies
    1. I had not thought of using an INNER JOIN, so I will have to answer personal preference.

      Delete
  3. Ha, ok. I just wanted to make sure I wasn't missing a subtle trick . Thanks for all of the great articles!

    ReplyDelete
  4. One could ask why it would matter if there were many index or logical files with the same access path cos AS/400 only creates one.
    yes, its untidy, but doesnt affect performance.

    Simon. Have you covered the technique where you run some embedded SQL in debug. After the first fetch, look at the job log. There will be a massage that says which logical/index it has used. if it hasnt used one, it will suggest creating one. I always do that when developing an SQLRPGLE program. Apologies if you have mentioned this in one of your excellent articles.

    ReplyDelete
  5. Simon, thanks for sharing, great read. Thanks for sharing . You’re correct indexes are important but too many over the same PF can cause problems..

    ReplyDelete
  6. If you only want to see a list of the keys (and other information about an index) you can also access the SYSINDEXSTAT view:
    Select Table_Schema, Table_Name, Index_Name, INdex_Schema,
    Column_Names, AccPth_Type
    From SysIndexStat a
    Where Table_Schema = 'YOURSCHEMA';

    The SYSPARTITIONINDEXSTAT View will not only provide the keys for SQL indexes, but also for Physical Files, Logical Files and Key Constraints.

    Select Table_Schema, Table_Name, Index_Name, Index_Schema,
    Column_Names, Index_Type
    From SysPartitionIndexStat a
    Where Table_Schema = 'YOURSCHEMA'

    ReplyDelete
    Replies
    1. Just used this in an SQL to combine with SYSCOLUMNS2 to show which are the key fields.
      CASE WHEN LOCATE(COLUMN_NAME, ST.COLUMN_NAMES) <> 0 THEN 'K'
      ELSE ' '
      END

      Delete
  7. Simon, thanks for sharing. Very useful information with great examples. Its just another great teaching moment. We need all the help we can get. Again, thanks for teaching us useful functions that we can use daily..

    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.