Guru Classic: Find a View of a View of a View. . .

February 5, 2020 Paul Tuohy

Author’s Note: This article was originally published in October 2014. This stored procedure is something I use a lot and has saved me from accidentally deleting dependent views more than once. Basically, I call it before I ever drop a view.

SQLs Data Definition Language (DDL) offers many great features, one of which is the ability to define a view of a view. This can lead to simple or complex structures making use of views of views of views of . . . you get the idea.

But one of the difficulties with this technique is that, once created, it is difficult to determine the depth of dependencies for views. The Display Database Relations (DSPDBR) command will tell you which views are immediately dependent on a view but it will not tell you what views are dependent on those views.

This story contains code, which you can download here.

In this article I will introduce you to a stored procedure that, given the name of a table or a view, will provide the full list of dependent views and all of their dependents and all of their dependents, etc.

Setting The Scene

This is a list showing a table (BASESALE) and a number of dependent views.

BASESALE     Base Sales Data                              	
B_L1USE      Sales for Region USE                              	
B_L1USW      Sales for Region USW                              	
B_L2USE1     Sales for Region USE, Division 001                	
B_L2USE2     Sales for Region USE, Division 002                	
B_L2USW1     Sales for Region USW, Division 001                	
B_L2USW2     Sales for Region USW, Division 002                	
B_L3USE1_S   Sales for Region USE, Division 001, Summary       	
B_L3USE2_S   Sales for Region USE, Division 002, Summary       	
B_L3USW1_S   Sales for Region USW, Division 001, Summary       	
B_L3USW2_S   Sales for Region USW, Division 002, Summary

The BASESALE_L1 views are created over the BASESALE table. The BASESALE_L2 views are created over the BASESALE_L1 views and the BASESALE_L3 views are created over the BASESALE_L2 views. The script to create this table and the dependent views can be found in the downloadable code.

An attempt to delete the table BASESALE using the Delete File (DLTF) command will fail because the file has dependents. The DSPDBR command will list the ten dependent views – which is what I need. As an aside, the Show Related option in System i Navigator only shows the two immediately dependent views.

But what happens if we decide to use the SQL DROP statement or decide to delete one of the dependent views (and you have to delete a view if you want to re-create it). Here are the problems we will confront in this scenario:

• Using the SQL DROP statement to delete the table BASESALE, will, by default, delete the table and all of the views.
• Using the SQL DROP statement to delete the view B_L1USE, will, by default, delete the views B_L2USE1, B_L2USE2, B_L3USE1_S and B_L3USE2_S.
• An attempt to delete one of the views using the DLTF command will fail because the file has dependents but the DSPDBR command will only list views that are immediately dependent on the view being deleted. For example, the DSPBR for view B_L1USE only shows B_L2USE1 and B_L2USE2 as dependents – it does not show the further dependents of B_L3USE1_S and B_L3USE2_S.

So, how can we determine which other view are effected when a view is deleted? Introducing the SQL stored procedure GET_VIEW_DEPENDENTS

Using GET_VIEW_DEPENDENTS

The GET_VIEW_DEPENDENTS stored procedure takes two parameters – the system names (10 characters) of a table or view and a schema, for example:

CALL GET_VIEW_DEPENDENTS ('BASESALE', ‘TESTSTUFF');

A call to the procedure results provides a result set shows the SQL name, text description, system table name, and system schema name.

B_L1USE     Sales for Region USE                         B_L1USE    TESTSTUFF 
B_L1USW     Sales for Region USW                         B_L1USW    TESTSTUFF 
B_L2USE1    Sales for Region USE, Division 001           B_L2USE1   TESTSTUFF 
B_L2USE2    Sales for Region USE, Division 002           B_L2USE2   TESTSTUFF 
B_L2USW1    Sales for Region USW, Division 001           B_L2USW1   TESTSTUFF 
B_L2USW2    Sales for Region USW, Division 002           B_L2USW2   TESTSTUFF 
B_L3USE1_S  Sales for Region USE, Division 001, Summary  B_L3USE1_S TESTSTUFF 
B_L3USE2_S  Sales for Region USE, Division 002, Summary  B_L3USE2_S TESTSTUFF 
B_L3USW1_S  Sales for Region USW, Division 001, Summary  B_L3USW1_S TESTSTUFF 
B_L3USW2_S  Sales for Region USW, Division 002, Summary  B_L3USW2_S TESTSTUFF

This is the same information as the DSPBBR command, for table BASESALE, with the added benefit that we see the text description. But GET_VIEW_DEPENDENTS comes into its own when we provide the name of a view. This is the result of calling GET_VIEW_DEPENDENTS for the view B_L1USE.

B_L2USE1    Sales for Region USE, Division 001           B_L2USE1   TESTSTUFF 
B_L2USE2    Sales for Region USE, Division 002           B_L2USE2   TESTSTUFF 
B_L3USE1_S  Sales for Region USE, Division 001, Summary  B_L3USE1_S TESTSTUFF 
B_L3USE2_S  Sales for Region USE, Division 002, Summary  B_L3USE2_S TESTSTUFF

The GET_VIEW_DEPENDENTS Procedure

As one might expect, the information we are looking for may be found in the system catalog. The table QSYS/QADBFDEP contains all of the dependencies of views upon views and the table SYSY/QADBXREF contains corresponding text descriptions. GET_VIEW_DEPENDENTS uses a recursive query to perform a recursive retrieve of a table or view and its dependents. An excellent description of recursive queries may be found in the article Recursive Queries on the iSeries and System i by Michael Sansoterra.

This is the CREATE statement for the GET_VIEW_DEPENDENTS procedure.

CREATE PROCEDURE GET_VIEW_DEPENDENTS ( 
	IN DBNAME CHAR(10) , 
	IN DBLIBRARY CHAR(10) ) 
	DYNAMIC RESULT SETS 1 
	LANGUAGE SQL 
	SPECIFIC GET_VIEW_DEPENDENTS 
	NOT DETERMINISTIC 
	READS SQL DATA 
	CALLED ON NULL INPUT 
	PROGRAM TYPE SUB 
	SET OPTION  ALWBLK = *ALLREAD , ALWCPYDTA = *OPTIMIZE , 
	    COMMIT = *NONE , CLOSQLCSR = *ENDMOD , 
	    DECRESULT = (31, 31, 00) , DFTRDBCOL = *NONE , 
	    DYNDFTCOL = *NO , DYNUSRPRF = *USER , SRTSEQ = *HEX   
BEGIN 
	DECLARE C1 CURSOR FOR 
	    WITH RECURSIVE DEPENDENTS ( CALLLEVEL , DBFFIL , DBFLIB , 
                                        DBFFDP , DBFLDP ) AS 
        ( SELECT 1 AS CALLLEVEL , DBFFIL , DBFLIB , DBFFDP , DBFLDP 
             FROM QADBFDEP 
             WHERE DBFFIL = DBNAME AND DBFLIB = DBLIBRARY 
          UNION ALL 
          SELECT CALLLEVEL + 1 AS CALLLEVEL , 
                 PR.DBFFIL , PR.DBFLIB , PR.DBFFDP , PR.DBFLDP 
             FROM DEPENDENTS PH INNER JOIN QADBFDEP PR 
                  ON ( PH.DBFFDP , PH.DBFLDP ) = ( PR.DBFFIL , PR.DBFLIB ) 
        ) 
        SELECT DISTINCT DBXLFI, DBXTXT, DBFFDP , DBFLDP 
           FROM DEPENDENTS INNER JOIN QADBXREF
                ON (DBFFDP , DBFLDP) = (DBXFIL , DBXLIB)
           ORDER BY DBFFDP , DBFLDP 
           FOR READ ONLY ; 
  
        OPEN C1 ; 
  
        SET RESULT SETS CURSOR C1 ; 
END  ;

Another Tool In The Belt

I hope you find the GET_VIEW_DEPENDENTS procedure as useful as I do — more than once it has saved me from causing havoc when dropping a view.

Paul Tuohy, IBM Champion and author of Re-engineering RPG Legacy Applications, is a prominent consultant and trainer for application modernization and development technologies on the IBM Midrange. He is currently CEO of ComCon, a consultancy firm in Dublin, Ireland, and partner at System i Developer. He hosts the RPG & DB2 Summit twice per year with partners Susan Gantner and Jon Paris.