Getting information about Views and Indexes

We have been given a number of SQL views that allow us to harvest information from our IBM i's, and many are unaware of their existance.

With the move from DDS files to SQL tables, indexes, and views I needed to update my toolset to be able to list information about the last two in a quick to run and easy to read way. Fortunately I can use some of these views to do so:

• SYSVIEWS – List of all Views
• SYSVIEWDEP – Dependencies of the Views
• SYSINDEXES – List of all Indexes
• SYSINDEXSTAT – Statistics of the Indexes

They are all found in the QSYS2 library. The view SYSINDEXSTAT is the logical file SYSIDXSTAT.

In these examples I want a list of all the views and indexes in the library MYLIB.

 

View of Views

What is the kind of information I would want to know about a view?

Data element	SQL column	Field name	SQL table
View name	SYSTEM_VIEW_NAME	SYS_VNAME	SYSVIEWS
View library	SYSTEM_VIEW_SCHEMA	SYS_VDNAME	SYSVIEWS
Can you update data in view?	IS_UPDATES	UPDATES	SYSVIEWS
Can you insert data into view?	IS_INSERTABLE_INTO	INSERTABLE	SYSVIEWS
Can you delete data from views?	IS_DELETABLE	DELETES	SYSVIEWS
Based on table	TABLE_NAME	BNAME	SYSVIEWDEP
Table's library	TABLE_SCHEMA	BDBNAME	SYSVIEWDEP
View definition	VIEW_DEFINITION	TEXT	SYSVIEWS

Note: For most purposes Schema is the same as library.

I can now create a view combining SYSVIEWS and SYSVIEWDEP to present the information the way I would like. I would create a source member and put the following code into it:

01 DROP VIEW MYLIB/TESTVIEW ; 02 CREATE VIEW MYLIB/TESTVIEW ( 03 VIEW_NAME,VIEW_LIB,UPDATES,INSERTS,DELETES,TABLE_NAME, 04 TABLE_LIB,TABLE_TYPE,VIEW_DEFINITION) 05 AS SELECT A.SYS_VNAME,A.SYS_VDNAME, 06 CASE WHEN A.UPDATES = 'N' THEN 'NO' 07 WHEN A.UPDATES = 'Y' THEN 'YES' 08 ELSE A.UPDATES 09 END, 10 A.INSERTABLE, 11 CASE WHEN A.DELETES = 'N' THEN 'NO' 12 WHEN A.DELETES = 'Y' THEN 'YES' 13 ELSE A.DELETES 14 END, 15 CAST(B.BNAME AS CHAR(10)), 16 CAST(B.BDBNAME AS CHAR(10)), 17 CASE WHEN B.BTYPE = 'P' THEN 'PHYSICAL FILE' 18 WHEN B.BTYPE = 'T' THEN 'TABLE' 19 WHEN B.BTYPE = 'M' THEN 'MATERIALIZED QUERY TABLE' 20 WHEN B.BTYPE = 'V' THEN 'VIEW' 21 WHEN B.BTYPE = 'L' THEN 'LOGICAL FILE' 22 ELSE B.BTYPE 23 END, 24 CAST(A.TEXT AS CHAR(200) CCSID 37) 25 FROM SYSVIEWS A LEFT OUTER JOIN SYSVIEWDEP B 26 ON A.NAME = B.DNAME 27 AND A.DBNAME = B.DDBNAME 28 WHERE A.DBNAME = 'MYLIB' ; 29 LABEL ON TABLE MYLIB/TESTVIEW IS 'SQL view over SYSVIEWS & SYSVIEWDEP' ;

Why does he do that? I used the field names for only one reason, it is quicker for me to type them than it would be for me to type the longer column names.

For those of you unfamiliar with the CASE read the post Creating derived columns in SQL View.

I used the CAST on lines 15 and 16 to convert the variable character fields to 10 long character fields.

The CAST on line 24 also does a character conversion to CCSID 37. I have to do this as without it the field in unreadable. I do not know if you would have to do the same on your IBM i.

I would create the view by using the RUNSQLSTM command.

When I run a Query over my created view the output looks like:

VIEW_NAME VIEW_LIB UPDATES INSERTS DELETES TABLE_NAME TABLE_LIB TESTFILEV MYLIB NO NO NO TESTFILE MYLIB TESTFILEV MYLIB NO NO NO TESTFILE2 MYLIB TESTVIEW MYLIB NO NO NO SYSVIEWS QSYS2 TESTVIEW MYLIB NO NO NO SYSVIEWDEP QSYS2

And when I move to the right (F20):

TABLE_TYPE VIEW_DEFINITION PHYSICAL FILE SELECT A.FLD001,A.FLD002,B.AFLD01,B.AFLD02 PHYSICAL FILE SELECT A.FLD001,A.FLD002,B.AFLD01,B.AFLD02 VIEW SELECT A.SYS_VNAME,A.SYS_VDNAME, CASE WHEN VIEW SELECT A.SYS_VNAME,A.SYS_VDNAME, CASE WHEN

Even though SQL treats DDS logical files as views they are not included in SYSVIEWS.

A full list of all the columns in the SYSVIEWS and SYSVIEWDEP views can be found in the links at the bottom of this post.

 

View of Indexes

The information I would be interested to learn about an index would be:

Data element	SQL column	Field name	SQL table
Index name	SYSTEM_INDEX_NAME	SYS_IXNAME	SYSINDEXES
Index library	SYSTEM_INDEX_SCHEMA	SYS_IDNAME	SYSINDEXES
Unique key?	IS_UNIQUE	UNIQUERULE	SYSINDEXES
Number of columns	COLUMN_COUNT	COLCOUNT	SYSINDEXES
Has WHERE clause	INDEX_HAS_SEARCH_CONDITION	IXHASWHERE	SYSINDEXES
Has INCLUDE clause	INDEX_HAS_INCLUDE_EXPRESSION	IXHASINCEX	SYSINDEXES
Table name	SYSTEM_TABLE_NAME	SYS_TNAME	SYSINDEXES
Table library	SYSTEM_TABLE_SCHEMA	SYS_DNAME	SYSINDEXES
Type of unique key	UNIQUE	UNIQUE	SYSINDEXSTAT
Number of index columns	NUMBER_OF_KEY_COLUMNS	INDKEYS	SYSINDEXSTAT
Sort sequence	SORT_SEQUENCE	SRTSEQ	SYSINDEXSTAT
Index key columns	COLUMN_NAMES	COLNAMES	SYSINDEXSTAT

My source for this view would be:

01 DROP VIEW MYLIB/TESTVIEW2 ; 02 CREATE VIEW MYLIB/TESTVIEW2 ( 03 INDEX_NAME,INDEX_LIB,UNIQUE_RULE,COLUMN_COUNT,HAS_WHERE, HAS_INCLUDE, 04 TABLE_NAME,TABLE_LIB,UNIQUE_KEY,INDEX_KEYS,SORT_SEQ, COLUMN_NAMES) 06 AS SELECT A.SYS_IXNAME,A.SYS_IDNAME, 07 CASE WHEN A.UNIQUERULE = 'D' THEN 'DUPLICATES ALLOWED' 08 WHEN A.UNIQUERULE = 'U' THEN 'UNIQUE (NO NULLS)' 09 WHEN A.UNIQUERULE = 'V' THEN 'UNIQUE NULLS ALLOWED' 10 WHEN A.UNIQUERULE = 'E' THEN 'ENCODED VECTOR INDEX' 12 ELSE A.UNIQUERULE 13 END, 14 A.COLCOUNT, 15 CASE WHEN A.IXHASWHERE = 'N' THEN 'NO' 16 WHEN A.IXHASWHERE = 'Y' THEN 'YES' 17 ELSE A.IXHASWHERE 18 END, 19 CASE WHEN A.IXHASINCEX = 'N' THEN 'NO' 20 WHEN A.IXHASINCEX = 'Y' THEN 'YES' 21 ELSE A.IXHASINCEX 22 END, 23 A.SYS_TNAME,A.SYS_DNAME,B.UNIQUE,B.INDKEYS,B.SRTSEQ,B.COLNAMES 24 FROM SYSINDEXES A LEFT OUTER JOIN SYSINDEXSTAT B 25 ON A.DBNAME = B.INDSCHEMA 26 AND A.NAME = B.INDNAME 27 WHERE A.DBNAME = 'MYLIB' ; 28 LABEL ON TABLE MYLIB/TESTVIEW2 IS 'SQL view over SYSINDEXES & SYSINDEXSTAT' ;

I would compile the member containing the above code using the RUNSQLSTM command.

When I run Query over the view the output looks like:

INDEX_NAME INDEX_LIB UNIQUE_RULE COLUMN_COUNT HAS_WHERE HAS_INCLUDE TESTFILEI MYLIB DUPLICATES ALLOWED 2 NO NO

And the rest of the output looks like:

TABLE_NAME TABLE_LIB UNIQUE_KEY INDEX_KEYS SORT_SEQ COLUMN_NAMES TESTFILE MYLIB FIFO 2 BY HEX VALUE FLD002, FLD001

A full list of all the columns in the SYSINDEXES and SYSINDEXSTAT views can be found in the links below.

 

In both of these examples I have just selected the views and indexes in one library, MYLIB. If I want to search in more than one library then I would replace the views' statement line 28 and the indexes' line 27 with something like:

WHERE A.DBNAME IN ('MYLIB','YOURLIB') ;

 

You can learn more about these from the IBM web site:

• SYSVIEWS
• SYSVIEWDEP
• SYSINDEXES
• SYSINDEXSTAT

 

This article was written for IBM i 7.2, and it should work with earlier releases too.