sql - heirarchical query with CONNECT_BY_ROOT with join on DB2/400

Question

I want to iterate through a table that contains recursive list of programs and tables to find all programs and tables used by specific program MYPGM and it's children. So far this works:

 SELECT CONNECT_BY_ROOT WHONAM AS TOP_LEVEL, WHONAM AS REF_A, 
 WHRNAM AS REF_B, 
 WHRTYP AS Obj_Type, WHRATR OBJ_ATTTR, WHUSG AS USAGE, 
 LEVEL WHONAM 
 FROM MYLIB/MYREF 
 START WITH WHONAM = 'MYPGM' 
 CONNECT BY PRIOR WHRNAM = WHONAM                               

Now I want to do the same for all programs in table.field MYLIST.MY_PGM_NAME instead of just program 'MYPGM'. I am having trouble incorporating a JOIN clause and don't know what to do with START WITH clause.Hope I am clear.

Thank you,

Answer 1

Consider using the SQL standard syntax for recursion. There is a Bill of materials example in the Db2 for i manual here https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/db2/rbafzrecurse.htm

 WITH RPL (PART, SUBPART, QUANTITY) AS
    (  SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
          FROM PARTLIST ROOT
          WHERE ROOT.PART = '01'
      UNION ALL
       SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
          FROM RPL PARENT, PARTLIST CHILD
          WHERE PARENT.SUBPART = CHILD.PART
     )
 SELECT DISTINCT PART, SUBPART, QUANTITY
 FROM RPL
 ORDER BY PART, SUBPART, QUANTITY