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|
|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|
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 FROM MYLIB.TE PHYSICAL FILE SELECT A.FLD001,A.FLD002,B.AFLD01,B.AFLD02 FROM MYLIB.TE VIEW SELECT A.SYS_VNAME,A.SYS_VDNAME, CASE WHEN A.UPDATES = ' VIEW SELECT A.SYS_VNAME,A.SYS_VDNAME, CASE WHEN A.UPDATES = '
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|
|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|
|Type of unique key||UNIQUE||UNIQUE||SYSINDEXSTAT|
|Number of index columns||NUMBER_OF_KEY_COLUMNS||INDKEYS||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:
This article was written for IBM i 7.2, and it should work with earlier releases too.