• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guruclassic, Data Definition Language, DDL, Display Database Relations, DSPDBR, FHGC, Four Hundred Guru Classic, IBM i, SQL

    Sponsored by
    ARCAD Software

    Embrace VS Code for IBM i Development

    The IBM i development landscape is evolving with modern tools that enhance efficiency and collaboration. Ready to make the move to VS Code for IBM i?

    Join us for this webinar where we’ll showcase how VS Code can serve as a powerful editor for native IBM i code and explore the essential extensions that make it possible.

    In this session, you’ll discover:

    • How ARCAD’s integration with VS Code provides deep metadata insights, allowing developers to assess the impact of their changes upfront.
    • The role of Git in enabling seamless collaboration between developers using tools like SEU, RDi, and VS Code.
    • Powerful extensions for code quality, security, impact analysis, smart build, and automated RPG conversion to Free Form.
    • How non-IBM i developers can now contribute to IBM i projects without prior knowledge of its specifics, while ensuring full control over their changes.

    The future of IBM i development is here. Let ARCAD be your guide!

    Watch Now

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Guru Classic: A Bevy Of BIFs — Updates We Need Your Help Gathering Some Intelligence

    2 thoughts on “Guru Classic: Find a View of a View of a View. . .”

    • Doug Bridwell says:
      February 5, 2020 at 11:17 am

      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!

      Reply
      • Juraj Sranko says:
        April 28, 2020 at 10:47 am

        Hi Doug, you can also use a UDTF https://gist.github.com/jsranko/debeecdc94efb90cf1e85ef43b9a022b

        Reply

    Leave a Reply Cancel reply

TFH Volume: 30 Issue: 9

This Issue Sponsored By

  • RPG & DB2 Summit
  • RPG & DB2 Summit
  • RPG & DB2 Summit

Table of Contents

  • Guru Classic: Find a View of a View of a View. . .
  • Guru Classic: A Bevy Of BIFs — Updates
  • Guru Classic: Looking For Stuff With iSphere

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24
  • Big Blue Raises IBM i License Transfer Fees, Other Prices
  • Keep The IBM i Youth Movement Going With More Training, Better Tools
  • Remain Begins Migrating DevOps Tools To VS Code
  • IBM Readies LTO-10 Tape Drives And Libraries
  • IBM i PTF Guide, Volume 27, Number 23

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle