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.
I have put recursive queries in views to encapsulate the complexity of the query. The problem is that you can not use the order by clause in a view. I never thought about using a procedure to return a result set. Nice technique!