Pages

Wednesday, April 24, 2024

More about getting information about SQL Views

Several years ago I wrote a post about the Db2 for i (SQL) Views about Views:

  • SYSVIEWS:  Information about Views themselves
  • SYSVIEWDEP:  Information about Views' dependencies

One of the things I talk about during presentations I give to Local User Groups, LUG, is the use of SQL Views and how they make my programming simpler. Therefore, I wanted to revisit these two Views and explain how they help me learn more about the Views in the partitions I use.

Before I can show what kinds of information I can get from SYSVIEWS and SYSVIEWSDEP I am going to need some Views to play with.

I am going to join a DDS file, TESTFILE, and a DDL table, TESTTABLE to create my first View. While it is not essential to know what the file and table look like I am just going to include their source code below.

First TESTFILE:

01 A                                      UNIQUE
02 A          R TESTFILER
03 A            FILEKEY       10A         ALIAS(FILE_KEY)
04 A            FILEFLD01      1A         ALIAS(FILE_FIELD_1)
05 A            FILEFLD02      1A         ALIAS(FILE_FIELD_2)
06 A            FILEFLD03      1A         ALIAS(FILE_FIELD_3)
07 A          K FILEKEY

Line 1: I always like my physical files to have unique keys.

Lines 3 – 6: For many years I have coded my physical files with Alias names. Originally that was so these could be the column headings when I download the file to my PC, and COBOL programs could use these as the field names. In modern RPG I can use the ALIAS keyword in my file definition to use the alias names rather than the short field names.

And then TESTTABLE:

01  CREATE TABLE MYLIB.TESTTABLE
02  (TABLE_KEY FOR COLUMN "TABLEKEY" CHAR(10) NOT NULL WITH DEFAULT,
03   TABLE_COLUMN_1 FOR COLUMN "TABLECOL01" CHAR(1),
04   TABLE_COLUMN_2 FOR COLUMN "TABLECOL02" CHAR(1),
05   TABLE_COLUMN_3 FOR COLUMN "TABLECOL03" CHAR(1),
06   PRIMARY KEY(TABLE_KEY)) ;

Line 6: The PRIMARY KEY creates a unique key for the Table.

This is the SQL statement I used to create the View TESTVIEW1:

01  CREATE OR REPLACE VIEW MYLIB.TESTVIEW1 AS
02  SELECT A.*,B.TABLE_COLUMN_1,B.TABLE_COLUMN_2
03    FROM TESTFILE A JOIN TESTTABLE B
04      ON A.FILE_KEY = B.TABLE_KEY
05   WHERE B.TABLE_COLUMN_3 = 'A' ;

Line 1: I am creating this View in my library with the name TESTVIEW1.

Lines 2 – 5: This is the Select statement used to build the View.

Line 2: I am selecting all the fields from TESTFILE, A.*, and the first two columns from the Table.

Lines 3 and 4: The two files I want to get the data from are joined by the FILE_KEY field from TESTFILE, and the TABLE_KEY column from TESTABLE.

Line 5: And only include those matching rows where the TESTTABLE column TABLE_COLUMN_3 is equal to 'A'.

After I have created the View I can start investigating it with the two Views I mentioned at the top of this post. Let me start with the results from SYSVIEWS:

01  SELECT TABLE_NAME,VIEW_DEFINITION,SYSTEM_VIEW_NAME
02    FROM QSYS2.SYSVIEWS
03   WHERE SYSTEM_VIEW_SCHEMA = 'MYLIB' 
04     AND TABLE_NAME = 'TESTVIEW1'

Line 1: These are the columns I find that I am the most interested in:

  • TABLE_NAME:  For some reason this is the column that contains the View's name. I have not idea why this column is TABLE_NAME, rather than VIEW_NAME
  • VIEW_DEFINITION:  This is the SQL statement that created the View
  • SYSTEM_VIEW_NAME:  I always like to know the system name for the View, as it could be something that is unrelated to the long name

Line 2: SYSVIEWS View is found in the library QSYS2.

The results are:

TABLE_                                    SYSTEM_VIEW
NAME       VIEW_DEFINITION                _NAME
---------  ----------------------------   -----------
TESTVIEW1  SELECT A.*,B.TABLE_COLUMN...   TESTVIEW1

The value in the TABLE_NAME and the SYSTEM_VIEW_NAME are the same as I did not use the FOR SYSTEM NAME when creating the View.

I can expand the VIEW_DESCRIPTION and see the statement that was used to create this View. This also means is I can copy that and use it to modify and recreate the View.

I use the following statement with the SYSVIEWDEP View to see my View's dependent objects:

SELECT VIEW_NAME,SYSTEM_VIEW_NAME,SYSTEM_TABLE_NAME,SYSTEM_TABLE_SCHEMA,
       OBJECT_TYPE,TABLE_TYPE
  FROM QSYS2.SYSVIEWDEP
 WHERE SYSTEM_VIEW_SCHEMA = 'MYLIB' 
   AND VIEW_NAME LIKE 'TESTVIEW1'
 ORDER BY VIEW_NAME,OBJECT_NAME

The results are:

VIEW_      SYSTEM_    SYSTEM_     SYSTEM_       OBJECT  TABLE
NAME       VIEW_NAME  TABLE_NAME  TABLE_SCHEMA  _TYPE   _TYPE
---------  ---------  ----------  ------------  ------  -----
TESTVIEW1  TESTVIEW1  TESTTABLE   MYLIB         TYPE    C
TESTVIEW1  TESTVIEW1  TESTFILE    MYLIB         TABLE   P
TESTVIEW1  TESTVIEW1  TESTTABLE   MYLIB         TABLE   T

The first row of the results confuse me. According to IBM's documentation an object type of 'TYPE' that this is object is a "distinct type", without further explanation. The 'C' is not explained in the documentation.

The second and third rows show the objects that were used to create the view. TESTFILE is regarded as a Table, with a table type that denotes it as a physical file. TESTTABLE is a table in object and table type.

Next example is to build a View over the View I just created:

01  CREATE OR REPLACE VIEW MYLIB.TESTVIEW2
02  (VIEW_NOT_KEY,VIEW_COLUMN_1,VIEW_COLUMN_2,VIEW_COLUMN_3,
03   VIEW_COLUMN_4,VIEW_COLUMN_5,VIEW_COLUMN_6) AS
04  SELECT A.*,B.FILE_FIELD_3
05    FROM TESTVIEW1 A JOIN TESTFILE B
06      ON A.FILE_KEY = B.FILE_KEY
07     AND A.FILE_FIELD_1 = '9' ;

Line 1: This View is called TESTVIEW2.

Lines 2 and 3: I need to give a column list for the View as there are two columns with the same name in the results of the SQL Select statement.

Lines 4 – 7: SQL Select statement for defining the View.

Line 5: I have joined the View I created above, TESTVIEW1, with TESTABLE.

This is the statement I would use to get the same information for this View from SYSVIEWS as I did for the first View.

01  SELECT TABLE_NAME,VIEW_DEFINITION,SYSTEM_VIEW_NAME
02    FROM QSYS2.SYSVIEWS
03   WHERE SYSTEM_VIEW_SCHEMA = 'MYLIB' 
04     AND TABLE_NAME = 'TESTVIEW2'

The results are:

TABLE_                                    SYSTEM_VIEW
NAME       VIEW_DEFINITION                _NAME
---------  ----------------------------   -----------
TESTVIEW2  SELECT A.*,B.FILE_FIELD_3...   TESTVIEW2

I can expand the VIEW_DEFINITION column to see the statement used to create this View.

SELECT VIEW_NAME,SYSTEM_TABLE_NAME,SYSTEM_TABLE_SCHEMA,
       OBJECT_TYPE,TABLE_TYPE
  FROM QSYS2.SYSVIEWDEP
 WHERE SYSTEM_VIEW_SCHEMA = 'MYLIB' 
   AND VIEW_NAME LIKE 'TESTVIEW2'
 ORDER BY VIEW_NAME,OBJECT_NAME

Line 1: For this example I have dropped the SYSTEM_VIEW_NAME, as I don't need it here.

The results are:

VIEW_      SYSTEM_     SYSTEM_       OBJECT  TABLE
NAME       TABLE_NAME  TABLE_SCHEMA  _TYPE   _TYPE
---------  ----------  ------------  ------  ------
TESTVIEW2  TESTFILE    MYLIB         TABLE   P
TESTVIEW2  TESTVIEW1   MYLIB         VIEW    V

I find it interesting that for this this View there is not a result with an OBJECT_TYPE of 'TYPE'.

I can see the two objects I used to create this View clearly identified as a physical file and a View.

In my last example I decided to do something a little more complicated. I decide to join the View SYSVIEWS with the OBJECT_STATISTICS Table function to get when the View was created.

01  CREATE OR REPLACE VIEW MYLIB.TESTVIEW3 AS
02  SELECT TABLE_NAME,B.OBJCREATED
03    FROM QSYS2.SYSVIEWS,
04  LATERAL
05  (SELECT OBJCREATED FROM TABLE(QSYS2.OBJECT_STATISTICS(           
06                                        OBJECT_SCHEMA => SYSTEM_VIEW_SCHEMA,
07                                        OBJTYPELIST => 'FILE',            
08                                        OBJECT_NAME => SYSTEM_VIEW_NAME))) B
09   WHERE SYSTEM_VIEW_SCHEMA = 'MYLIB' 
10     AND TABLE_NAME LIKE 'TESTVIEW%' ;

After TESTVIEW3 is created I can use SYSVIEWSDEP to retrieve its dependencies:

01  SELECT VIEW_NAME,SYSTEM_TABLE_NAME,SYSTEM_TABLE_SCHEMA,
02         OBJECT_TYPE,TABLE_TYPE
03    FROM QSYS2.SYSVIEWDEP
04   WHERE SYSTEM_VIEW_SCHEMA = 'MYLIB' 
05     AND VIEW_NAME LIKE 'TESTVIEW3'
06   ORDER BY VIEW_NAME,OBJECT_NAME

The results are:

VIEW_      SYSTEM_     SYSTEM_       OBJECT  TABLE
NAME       TABLE_NAME  TABLE_SCHEMA  _TYPE   _TYPE
---------  ----------  ------------  ------  ------
TESTVIEW3  <NULL>      FUNCTION      <NULL>  <NULL>
TESTVIEW3  SYSVIEWS    QSYS2         VIEW    V

The first row returned is for the OBJECT_STATISTICS Table function. I checked all of the columns in SYSVIEWSDEP and there is no place where the name of the Table function is returned. The second row is for the SYSVIEWS View. This demonstrates that if a View includes data from a Table function I need to look at the statement that created it.

I am sure you are all implementing Views within your work, and these two Views make it easy to get useful information about your Views quickly.

You can learn more about this from the IBM website:

 

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

1 comment:

  1. These are great examples. I have done something similar to find views to ensure they point to the correct sources. However, running through the examples, I did not get the result row with the object type = 'C' and I could not create the view over the QSYS2. tables. The system said I don't have enough authority even though the manual states I only need USAGE authority.
    -Matt

    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.