Guru: Generate SQL for Dependents
May 24, 2021
In October 2014, in the article Find A View Of A View Of A View. . ., I’ve detailed a stored procedure which, given the name of a table or view, provides the complete list of dependent views and all their dependents and all their dependents, etc. This is a stored procedure that has served me well in the DDL world, where it is common to have views from views of views.
In this article, I will describe a trace 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, modify or delete one or more columns), you can immediately make additional changes to the dependents of the table / view.
For example, let’s start by making this call to our stored procedure:
call GENERATE_SQL_FOR_DEPENDENTS('B_L1USE', ‘TESTSTUFF');
The following DDL is then returned to you as the result set of 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 take a look at what the two main components of this stored procedure will be:
- Generate a recursive list of dependents
- Generating SQL using the GENERATE_SQL_OBJECTS () Db2 for i service
Generating a recursive list of dependents
This is exactly the same concept as in the article cited above, but we will use a different syntax to generate 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 same results, just a different syntax.
This story contains code that you can download here.
All the information we need is in the QADBFDEP system catalog table. We just need a recursive query to extract the information.
An excellent description of the use of recursive queries is provided in the IBM documentation (formerly IBM Knowledge Center). At the time of writing, this link is working. Or a search for “db2 for i using recursive queries” should get you there.
The B_L1USE view 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, here is the query for 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’ll 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 ask 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 that contains the list of database objects for which we will generate SQL. (More information on the format of this table in a moment.)
- The following three parameters (DATABASE_SOURCE_FILE_NAME, DATABASE_SOURCE_FILE_LIBRARY_NAME and DATABASE_SOURCE_FILE_MEMBER) identify a member of the source file where the generated source should be placed. These are by default 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 physical source file. 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 generated DDL.
The table that contains the list of database objects for which we are going to generate 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 uses the two techniques described above to return a result set that 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 calls.)
- Parameters can be either system table and schema names (10 characters each), or SQL table and schema names
- Define a temporary table to contain the list of database objects for which we will generate SQL. Note that the with replace means that the table will be replaced if it already exists.
- If SQL names were supplied as parameters, obtain the corresponding system names. Or use the system names if they have been provided.
- Add the requested table / view to the list of database objects for which the 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 the DDL will be generated. This is basically the recursive query described above with the addition of a join to the QADBXREF table so that we can determine the type of SQL object. Note that the encoding (for the object type) in QADBXREF is different from that of SYSTABLES used in 4 above.
- Call the GENERATE_SQL_OBJECTS () procedure to generate the DDL. As we do not specify otherwise, the DDL will be placed in the Q_GENSQOBJ member in the Q_GENSQOBJ source file in QTEMP. To customize DDL, I specify that the statements should be CREATE OR REPLACE, I don’t want any CCSIDs specified, and I don’t want qualified names.
- Declare and return a cursor on 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 can have parameters to identify a source member or an IFS file, generate the DDL for it, and then open it with Execute SQL Scripts.
Hope you find this tip as useful as I did!
Find a view of a view of a view. . .
IBM documentation: using recursive queries