Guru: Generate SQL for Dependents

May 24, 2021 Paul Tuohy

Way back in October 2014, in the article Find A View Of A View Of A View. . ., I detailed a stored procedure that, given the name of a table or a view, provides the full list of dependent views and all of their dependents and all of their dependents, etc. This is a stored procedure that has served me well in the world of DDL, where it is common to have views of views of views.

In this article I will describe a follow-on stored procedure that will generate the DDL for a given table/view and all of its dependents. This means that when you are about to make a change to a table/view (add, change or delete one or more columns), you can immediately make any additional changes to dependents of the table/view.

For example, let’s start by issuing this call to our stored procedure:

call GENERATE_SQL_FOR_DEPENDENTS('B_L1USE', ‘TESTSTUFF');

You are then provided with the following DDL returned as a result set from the call:

--  Generate SQL                                                             
--  Version:                   	V7R4M0 190621 
--  Generated on:              	04/20/21 08:52:30 
--  Relational Database:       	IDEVELOP 
--  Standards Option:          	Db2 for i 
                                                                            
CREATE OR REPLACE VIEW B_L1USE (       
	DIVISION ,                                                       
	DEPARTMENT ,                                                   
	REP ,                                                                 
	SALES )                                                             
	AS                                                                       
	SELECT DIVISION, DEPARTMENT, REP, SALES  
		 FROM           BASESALE  
		 WHERE REGION = 'USE'   
	RCDFMT B_L1USE    ; 
  
LABEL ON TABLE B_L1USE 
	IS 'Sales for Region USE' ; 
  
LABEL ON COLUMN B_L1USE 
( DIVISION IS 'Division             ' , 
	DEPARTMENT IS 'Department           ' , 
	REP IS 'Rep                  ' , 
	SALES IS 'Sales                ' ) ; 
  
GRANT DELETE , SELECT , UPDATE   
ON B_L1USE TO PUBLIC ; 
  
GRANT ALTER , DELETE , REFERENCES , SELECT , UPDATE   
ON B_L1USE TO TUOHYP WITH GRANT OPTION ; 
  
CREATE OR REPLACE VIEW B_L2USE1 ( 
	DEPARTMENT , 
	REP , 
	SALES ) 
	AS 
	SELECT DEPARTMENT, REP, SALES  
		 FROM           B_L1USE  
		 WHERE DIVISION = '001'   
	RCDFMT B_L2USE1   ; 
  
LABEL ON TABLE B_L2USE1 
	IS 'Sales for Region USE, Division 001' ; 
  
LABEL ON COLUMN B_L2USE1 
( DEPARTMENT IS 'Department           ' , 
	REP IS 'Rep                  ' , 
	SALES IS 'Sales                ' ) ; 
  
GRANT DELETE , SELECT , UPDATE   
ON B_L2USE1 TO PUBLIC ; 
  
GRANT ALTER , DELETE , REFERENCES , SELECT , UPDATE   
ON B_L2USE1 TO TUOHYP WITH GRANT OPTION ; 
  
CREATE OR REPLACE VIEW B_L2USE2 ( 
	DEPARTMENT , 
	REP , 
	SALES ) 
	AS 
	SELECT DEPARTMENT, REP, SALES  
		 FROM           B_L1USE  
		 WHERE DIVISION = '002'   
	RCDFMT B_L2USE2   ; 
  
LABEL ON TABLE B_L2USE2 
	IS 'Sales for Region USE, Division 002' ; 
  
LABEL ON COLUMN B_L2USE2 
( DEPARTMENT IS 'Department           ' , 
	REP IS 'Rep                  ' , 
	SALES IS 'Sales                ' ) ; 
  
GRANT DELETE , SELECT , UPDATE   
ON B_L2USE2 TO PUBLIC ; 
  
GRANT ALTER , DELETE , REFERENCES , SELECT , UPDATE   
ON B_L2USE2 TO TUOHYP WITH GRANT OPTION ; 
  
CREATE OR REPLACE VIEW B_L3USE1_S ( 
	DEPARTMENT , 
	SALES ) 
	AS 
	SELECT DEPARTMENT, SUM(SALES) AS SALES  
		 FROM           B_L2USE1  
		 GROUP BY DEPARTMENT   
	RCDFMT B_L3USE1_S ; 
  
LABEL ON TABLE B_L3USE1_S 
	IS 'Sales for Region USE, Division 001, Summary' ; 
                                                                            
LABEL ON COLUMN B_L3USE1_S 
( DEPARTMENT IS 'Department           ' ) ; 
                                                                            
GRANT SELECT   
ON B_L3USE1_S TO PUBLIC ; 
                                                                            
GRANT ALTER , REFERENCES , SELECT   
ON B_L3USE1_S TO TUOHYP WITH GRANT OPTION ; 
  
CREATE OR REPLACE VIEW B_L3USE2_S ( 
	DEPARTMENT , 
	SALES ) 
	AS 
	SELECT DEPARTMENT, SUM(SALES) AS SALES  
		 FROM           B_L2USE2  
		 GROUP BY DEPARTMENT   
	RCDFMT B_L3USE2_S ; 
                                                         
LABEL ON TABLE B_L3USE2_S 
	IS 'Sales for Region USE, Division 002, Summary' ; 
                                                                            
LABEL ON COLUMN B_L3USE2_S 
( DEPARTMENT IS 'Department           ' ) ; 
                                                                            
GRANT SELECT   
ON B_L3USE2_S TO PUBLIC ; 
  
GRANT ALTER , REFERENCES , SELECT   
ON B_L3USE2_S TO TUOHYP WITH GRANT OPTION ;

But first, let’s look at what will be the two major components of this stored procedure:

Generating a recursive list of dependents
Generating SQL using the GENERATE_SQL_OBJECTS() Db2 for i Service

Generating A Recursive List of Dependents

This is the exact same concept as in the article quoted above, but we will be using a different syntax for generating the recursive list. The original stored procedure used a recursive Common Table Expression (CTE) but this stored procedure will use a hierarchical query instead. The exact same results, just a different syntax.

This story contains code, which you can download here.

All of the information we require is in the system catalog table QADBFDEP. We just need a recursive query to extract the information.

An excellent description of using recursive queries is provided in the IBM Documentation (formerly the IBM Knowledge Center). At the time of writing this link worked. Or a search for “db2 for i using recursive queries” should get you there.

The view B_L1USE has the following dependents:

Both B_L1USE1 and B_L1USE2 are dependents of B_L1USE and they also have their own dependents. B_L1USE1_S is a dependent of B_L1US1 and B_L1USE2_S is a dependent of B_L1USE2.

For the sake of comparison, this is the query from the original article that I would have used to generate the list:

WITH RECURSIVE DEPENDENTS 
( CALLLEVEL, DBFFIL, DBFLIB, DBFFDP, DBFLDP ) AS 
( SELECT 1 AS CALLLEVEL, DBFFIL, DBFLIB, DBFFDP, DBFLDP 
    FROM QADBFDEP 
   WHERE (DBFFIL, DBFLIB) = ('B_L1USE', 'TESTSTUFF') 
  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 DBFFIL, DBFLIB, DBFFDP, DBFLDP 
   FROM DEPENDENTS 
   ORDER BY DBFFDP, DBFLDP ;

Instead we will use this easier to read syntax to produce the list:

SELECT CONNECT_BY_ROOT DBFFIL, CONNECT_BY_ROOT DBFLIB, DBFFDP, DBFLDP
  FROM QADBFDEP
  START WITH (DBFFIL, DBFLIB) = ('B_L1USE', 'TESTSTUFF')
  CONNECT BY PRIOR DBFFDP = DBFFIL AND PRIOR DBFLDP = DBFLIB
ORDER BY DBFFDP , DBFLDP;

The IBM Documentation page, referenced above, provides an excellent description of both techniques.

The GENERATE_SQL_OBJECTS() Service

The GENERATE_SQL_OBJECTS() service is a stored procedure that generates the SQL statements required to create the database objects listed in a table

At the time of writing Run SQL Scripts does not provide an example for GENERATE_SQL_OBJECTS() but the procedure is described in detail in the IBM Documentation (https://www.ibm.com/docs/en/i/7.4?topic=services-generate-sql-objects-procedure).

This is what we see if we prompt the GENERATE_SQL_OBJECTS() procedure:

call QSYS2.GENERATE_SQL_OBJECTS(
                        SYSTEM_TABLE_NAME => , 
                        SYSTEM_TABLE_SCHEMA => , 
                        DATABASE_SOURCE_FILE_NAME => , 
                        DATABASE_SOURCE_FILE_LIBRARY_NAME => , 
                        DATABASE_SOURCE_FILE_MEMBER => , 
                        SEVERITY_LEVEL => , 
                        REPLACE_OPTION => , 
                        STATEMENT_FORMATTING_OPTION => , 
                        DATE_FORMAT => , 
                        DATE_SEPARATOR => , 
                        TIME_FORMAT => , 
                        TIME_SEPARATOR => , 
                        NAMING_OPTION => , 
                        DECIMAL_POINT => , 
                        STANDARDS_OPTION => , 
                        DROP_OPTION => , 
                        MESSAGE_LEVEL => , 
                        COMMENT_OPTION => , 
                        LABEL_OPTION => , 
                        HEADER_OPTION => , 
                        TRIGGER_OPTION => , 
                        CONSTRAINT_OPTION => , 
                        SYSTEM_NAME_OPTION => , 
                        PRIVILEGES_OPTION => , 
                        CCSID_OPTION => , 
                        CREATE_OR_REPLACE_OPTION => , 
                        OBFUSCATE_OPTION => , 
                        ACTIVATE_ROW_AND_COLUMN_ACCESS_CONTROL_OPTION => , 
                        MASK_AND_PERMISSION_OPTION => , 
                        QUALIFIED_NAME_OPTION => , 
                        ADDITIONAL_INDEX_OPTION => , 
                        INDEX_INSTEAD_OF_VIEW_OPTION => , 
                        TEMPORAL_OPTION => ,
                        SOURCE_STREAM_FILE => ,
SOURCE_STREAM_FILE_END_OF_LINE => , SOURCE_STREAM_FILE_CCSID => );

The main points to note are:

The first two parameters (SYSTEM_TABLE_NAME and SYSTEM_TABLE_SCHEMA) identify the table which contains the list of database object for which we will be generating SQL. (More about the format of this table in a moment.)
The next three parameters (DATABASE_SOURCE_FILE_NAME, DATABASE_SOURCE_FILE_LIBRARY_NAME and DATABASE_SOURCE_FILE_MEMBER) identify a source file member where the generated source is to be placed. These default to the member Q_GENSQOBJ in the source file Q_GENSQOBJ in QTEMP.
Providing a value of *STMF for DATABASE_SOURCE_FILE_NAME and providing appropriate values for SOURCE_STREAM_FILE, SOURCE_STREAM_FILE_END_OF_LINE and SOURCE_STREAM_FILE_CCSID means that the generated source will be placed in a file in the IFS instead of a member in a source physical file. The stream file parameters were introduced in IBM i 7.4 – TR4/IBM i 7.3 – TR10 Enhancements.
The rest of the parameters provide the standard Generate SQL options that allow you to customize, in some way, the DDL that is generated.

The table which contains the list of database object for which we will be generating SQL must contain the following columns. (The table is described in detail in the GENERATE_SQL_OBJECTS() documentation.)

object_schema varchar(258),
object_name varchar(258),
sql_object_type char(10)

The GENERATE_SQL_FOR_DEPENDENTS Procedure

The GENERATE_SQL_FOR_DEPENDENTS() procedure makes use of the two techniques described above to return a result set which is the generated DDL for the requested table/view and its dependents.

These are the main points to note in the procedure. (Please refer to the call outs.)

The parameters can be either the system table and schema names (10 characters each) or the SQL table and schema names
Define a temporary table to contain the list of database object for which we will be generating SQL. Note that the with replace means that the table will be replaced if it already exists.
If SQL names were provided as parameters get the corresponding system names. Or use the system names if they were provided.
Add the requested table/view to the list of database objects for which DDL will be generated. Note the CASE statement to generate the required SQL object type.
Return an error if the requested table/view was not found
Add each of the dependents to the list of database objects for which DDL will be generated. This is basically the recursive query described above with the addition of a join to the QADBXREF table so we can determine the SQL object type. Note that the encoding (for the object type) in QADBXREF is different from that in SYSTABLES used in 4 above.
Call the GENERATE_SQL_OBJECTS() procedure to generate the DDL. Since we do not specify otherwise, the DDL will be placed in the member Q_GENSQOBJ in the source file Q_GENSQOBJ in QTEMP. For customizing the DDL I am specifying that statements should be CREATE OR REPLACE, I do not want CCSIDs specified and I do not want qualified names.
Declare and return a cursor over the generated source member.

    CREATE OR REPLACE PROCEDURE TESTSTUFF/GENERATE_SQL_FOR_DEPENDENTS 
(1)   (IN P_DBNAME CHAR(10) DEFAULT '', 
       IN P_DBLIBRARY CHAR(10) DEFAULT '',
       IN P_SQL_DBNAME VARCHAR(256) DEFAULT '',
       IN P_SQL_SCHEMA VARCHAR(256) DEFAULT '')
            DYNAMIC RESULT SETS 1
            LANGUAGE SQL
            SPECIFIC TESTSTUFF/GENERATE_SQL_FOR_DEPENDENTS
            NOT DETERMINISTIC
            MODIFIES SQL DATA
            CALLED ON NULL INPUT
            PROGRAM TYPE SUB
            SET OPTION ALWBLK = *ALLREAD, ALWCPYDTA = *OPTIMIZE, COMMIT = *NONE,
                           DBGVIEW = *SOURCE, DECRESULT = (31, 31, 00), 
                           DFTRDBCOL = *NONE, DYNDFTCOL = *NO, DYNUSRPRF = *USER, 
                           SRTSEQ = *HEX
    BEGIN
    
    declare g_dbName char(10);
    declare g_dbLibrary char(10);
    declare g_local_sqlstate char(5);
    
    -- Create table for list of objects to be generated
(2) declare global temporary table q_perf_ord (
            object_schema varchar(258),
            object_name varchar(258),
            sql_object_type char(10)
          )
        with replace;
    -- Get the system name of the requested object
    --  Caller can specify object by system name or SQL Name
    --  Procedure uses system name to retrieve dependents
(3) if (p_dbName = '') then
      select system_table_name, system_table_schema
        into g_dbName, g_dbLibrary
        from systables 
        where (table_schema, table_name) = (p_SQL_Schema, p_SQL_DBName);
    else
      set g_dbName = p_dbName; 
      set g_dbLibrary = p_dbLibrary; 
    end if;  
    -- Add requested object to list of objects to be generated
(4) insert into q_perf_ord    
    (
     select table_schema, table_name, 
      case when table_type in ('P', 'T') then 'TABLE'
           else 'VIEW'
           end
      from qsys2.systables 
      where (system_table_name, system_table_schema) = (g_dbName, g_dbLibrary)
    );
    -- Error if requested object not found
(5) get diagnostics condition 1 g_local_sqlstate = returned_sqlstate;
    if (g_local_sqlstate = '02000') then
      signal sqlstate 'VV001' 
        set message_text = 'Table or view not found';
      return -1;
    end if;
    -- Add dependents to list of objects to be generated
(6) insert Into q_perf_ord
        (select distinct dbfldp, dbxlfi,
                case
                    when dbxatr = 'AL' Then 'ALIAS'
                    when dbxatr = 'IX' Then 'INDEX'
                    when dbxatr In ('TB', 'MQ', 'PF') Then 'TABLE'
                    when dbxatr In ('VW', 'LF') Then 'VIEW'
                    else dbxatr
                end as object_type
            from (
                  select CONNECT_BY_ROOT dbffil, CONNECT_BY_ROOT dbflib, 
                         dbffdp, dbfldp, LEVEL As DEPENDENCY_LEVEL
                    from QADBFDEP
                   START WITH (dbffil, dbflib) = (g_dbName, g_dbLibrary)
                   CONNECT BY prior dbffdp = dbffil and prior dbfldp = dbflib
                 ) As OBJECTS
                 inner join QADBXREF
                     On (dbffdp, dbfldp) = (dbxfil, dbxlib));
    -- Generate SQL for list of objects                 
(7)  call qsys2.generate_sql_objects(SYSTEM_TABLE_NAME => 'Q_PERF_ORD',  
                                    CREATE_OR_REPLACE_OPTION => '1', 
                                    CCSID_OPTION => '0',
                                    QUALIFIED_NAME_OPTION => '1');
    -- Set result set to generated source
      BEGIN
(8)     declare list_cursor cursor with return to caller for 
          select SRCDTA from QTEMP.Q_GENSQOBJ;
        open list_cursor;
      END;
    
    END;

If you prefer, instead of returning the DDL as a result set you could have parameters to identify a source member or IFS file, generate the DDL to that and then open it with Run SQL Scripts.

I hope you find this tip as useful as I do!

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search