Back in DB2 version 8 IBM finally launched the possibility to write genuine recursive SQL. The funny thing about this is that Oracle has been able to do this since the beginning of the 1980s. DB2 has been way behind in this matter. The implementation of recursive SQL in Oracle is also simple and elegant. Unfortunately I cannot say the same thing about IBMs implementation in DB2. I hope I can explain it adequately to make my tip useful for you.

I have managed to create an example using a DB2 catalog table. This makes you able to copy the example to your installation and see it work. The recursive part of the statement may not be invoked at all. To have true recursion you need at least one view based on a SYSIBM table and a view based on one of these views. Some of the DB2 products creates stuff like this so maybe you are lucky and receives a recursive result. The SQL statement looks like this:

WITH RECURSIVERESULT (LEVEL, BNAME, BCREATOR, DNAME, DCREATOR, STARTNAME) AS
( SELECT 1, CHAR(V.BNAME, 18), CHAR(V.BCREATOR,8)
       , CHAR(V.DNAME, 18), CHAR(V.DCREATOR,8), V.DNAME
  FROM SYSIBM.SYSVIEWDEP V
  WHERE V.BCREATOR = 'SYSIBM'
  UNION ALL
  SELECT LEVEL + 1, CHAR(V.BNAME, 18), CHAR(V.BCREATOR,8)
       , CHAR(V.DNAME, 18), CHAR(V.DCREATOR,8), STARTNAME
  FROM RECURSIVERESULT R
     , SYSIBM.SYSVIEWDEP V
  WHERE R.DNAME = V.BNAME
    AND R.DCREATOR = V.BCREATOR
)
SELECT *
FROM RECURSIVERESULT
ORDER BY STARTNAME, LEVEL

If you have any interest in SQL syntax you have already noticed that this SELECT statement actually starts with the word WITH. If you have created any programs executing dynamic SQL which uses PREPARE for SELECT statements and EXECUTE for all other statement, you might need to change the program to accept WITH as a valid statement for PREPARE. I had to change this myself.

Starting with DB2 version 8 a SELECT statement is prefixed by an optional WITH part followed by the mandatory SELECT part. The description of WITH is located in the SELECT statement part of the SQL reference. There is no WITH statement. You can use the WITH part to build temporary tables to be used in the SELECT part. You must build a table in the WITH part to contain the result of your recursive SQL. The first line in the example defines a table called RECURSIVERESULT containing the specified columns. Following AS you define how the contents of RECURSIVERESULT is built using a subselect.

The subselect must be built as specified in the example in order to make the SQL statement recursive. In front of UNION ALL the SELECT statement extracting the initial set of rows is specified. In the example I extract all tables or views which are base tables for other views and having SYSIBM as creator. If no views are defined on SYSIBM tables this part of the UNION ALL will not return any rows and the final result will be an empty table.

Following UNION ALL the SELECT statement performing the recursive part is specified. This statement is performed over and over again until no more rows qualifies. If the SELECT in front of UNION ALL returns one or more rows these rows are stored in RECURSIVERESULT and are joined with SYSVIEWDEP in the SELECT following UNION ALL. It is the join criteria in this SELECT that defines the columns creating the recursion. In the example the name and creator of the view is used to find out which (if any) views are based of the current view.

The recursive execution is started by executing the SELECT in front of UNION ALL. Next the SELECT following UNION ALL is executed for all rows returned by the initial SELECT. If this SELECT returns any rows the SELECT following UNION ALL is executed once again. This process continues until no more rows are returned. All returned rows are stored automatically in RECURSIVERESULT.

If there exists a row in SYSVIEWDEP where BNAME = DNAME and BCREATOR = DCREATOR, then the SQL statement will go into an infinite loop. Fortunately this is not possible in the above example, because DB2 does not allow views to have the same name as the base table or any dependent view. I have included a column called LEVEL in order to monitor the number of executed recursions. It is a very good practise to test on such a column in the SELECT following UNION ALL to prevent a loop. For instance I could have included AND LEVEL < 10 in my example which would stop the recursion when a view is defined on top of nine other views.

The last part of the execution process is to carry out the SELECT part of the statement. In my example it extracts all the rows which has been stored in RECURSIVERESULT. I have included the column STARTNAME, which is assigned the name of the view extracted by the initial SELECT, in order to group connected rows. Without this column it would be almost impossible to interpret the result of the SQL statement. That was a lot of words about recursive SQL so now I will wish you luck with your own recursive SQL statements.