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!