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:

• SYSVIEWS View
• SYSVIEWDEP View

 

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