Wednesday, May 27, 2015

Getting information about Views and Indexes

sql views sysviews sysviewdep sysindexes sysindexstat

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:

 

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

6 comments:

  1. why use the sql views and not the actual physicals and logicals in qsys? They are useful too.

    ReplyDelete
  2. Check the contents of the QADB* files in QSYS, these contain all file and view information you like (and more)

    ReplyDelete
  3. Answer to AnonymousMay 28, 2015 at 11:13 AM

    I assume with that "Actuals files" you ment DDS defined files?
    The SQL files have a lot more functionality, that's why IBM alreday try us to convince to use them alredy since V4Rx.

    ReplyDelete
  4. System I Navigator does all of this already

    ReplyDelete
  5. Re using F20 to move to the right, you could use iNav Run SQL Scripts to query your views, which can show more than 80 columns of query output.
    Also the view creation source could be stored in the IFS (or other shared PC server storage) as alternative to source files.

    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.