Continuing with our series on recursive queries in DB2 (and continuing with an example from IBM's SQL Reference "Appendix M. Recursion Example"), let's look at how to display a specific number of levels in a recursive query parts explosion (in other words, how to display all subparts of a part up to and including the second level of depth). To do this, you add a Level column to the RPL common table expression that will add 1 with each subsequent level returned. An example of this is shown below:
The table is defined as follows:
CREATE TABLE PARTLIST (PART VARCHAR(8), SUBPART VARCHAR(8), QUANTITY INTEGER);
The common table expression is
WITH RPL (Level, PART, SUBPART, QUANTITY) AS
( SELECT 1, ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
  FROM PARTLIST ROOT
  WHERE ROOT.PART = '01'
 UNION ALL
  SELECT PARENT.LEVEL + 1, CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
  FROM RPL PARENT, PARTLIST CHILD
  WHERE PARENT.SUBPART = CHILD.PART
   AND PARENT.LEVEL < 2
)
SELECT PART, LEVEL, SUBPART, QUANTITY FROM RPL;