Find A View Of A View Of A View. . .
October 8, 2014 Paul Tuohy
|
Note: The code accompanying this article is available for download here. SQL’s 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. 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 The following piece of code shows a list of a table (BASESALE) and a number of dependent views. 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 here. 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 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 10 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:
So, how can we determine which other views are affected when a view is deleted? Introducing the SQL stored procedure GET_VIEW_DEPENDENTS. Using GET_VIEW_DEPENDENTS The GET_VIEW_DEPENDENTS takes two parameters: the system name (10 characters) of a table or view and a schema. For example: CALL GET_VIEW_DEPENDENTS ('BASESALE', 'TESTSTUFF');
A call to the procedure results in a result set listing all dependents, as shown in the code below. The 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 The code above shows 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. Now look at this next snippet of code, which shows 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 Michael Sansoterra’s article Recursive Queries on the iSeries and System i. The following code shows 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 is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page. RELATED STORIES Recursive Queries on the iSeries and System i
|
