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:
- Indexes built over which table or file
- 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:
- Table schema (from SYSINDEXES)
- Table name (from SYSINDEXES)
- Index schema
- Index name
- Position in key
- Key column name
- 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.
You can also use ACS (IBM i Access Client Solutions) and under Schemas expand the Library and select Indexes to view them all.
ReplyDeleteWhy 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?
ReplyDeleteI had not thought of using an INNER JOIN, so I will have to answer personal preference.
DeleteHa, ok. I just wanted to make sure I wasn't missing a subtle trick . Thanks for all of the great articles!
ReplyDeleteOne 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.
ReplyDeleteyes, 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.
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..
ReplyDeleteIf you only want to see a list of the keys (and other information about an index) you can also access the SYSINDEXSTAT view:
ReplyDeleteSelect 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'
Just used this in an SQL to combine with SYSCOLUMNS2 to show which are the key fields.
DeleteCASE WHEN LOCATE(COLUMN_NAME, ST.COLUMN_NAMES) <> 0 THEN 'K'
ELSE ' '
END
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