Recursive SQL doesn't exist on mainframe DB2 platforms and many MVS and z/OS installations have problems presenting recursive reports. Bill of Materials applications (BOM), for example, are a common requirement in many business environments. It is impossible to resolve the whole picture with controlled depth structure, in a single SQL statement. To illustrate the capability of a recursive common table expression for BOM applications, consider a table of parts with associated subparts and the quantity of subparts required by the part.
For this example, create the table as follows.
CREATE TABLE PARTLIST
(PART VARCHAR(8),
SUBPART VARCHAR(8),
QUANTITY INTEGER);
In order to give query results for this example, assume the PARTLIST table is populated with the following values.
PART SUBPART QUANTITY -------- -------- ----------- 00 01 5 00 05 3 01 02 2 01 03 3 01 04 4 01 06 3 02 05 7 02 06 6 03 07 6 04 08 10 04 09 11 05 10 10 05 11 10 06 12 10 06 13 10 07 14 8 07 12 8
The example demonstrates the definition and use of a user-defined table function called RPLPART. This function is written in PL/I and returns a recursion report. The BOM is now resolved in an SQL statement with a table function.
CREATE FUNCTION SYSADM.RPLPART (NPART VARCHAR(8)) RETURNS TABLE (RID INTEGER ,PART CHAR(8) ,LEVEL CHAR(9) ,SUBPART CHAR(8) ,QUANTITY INTEGER) LANGUAGE PLI READS SQL DATA EXTERNAL NAME 'RPLPART' SCRATCHPAD 100 FINAL CALL COLLID RPLPART PROGRAM TYPE SUB WLM ENVIRONMENT DSNNWLM1;
The following select statement shows a recursive report and it answers the question: "What parts are needed to build the part identified by '01'?"
SELECT * FROM TABLE (SYSADM.RPLPART('01'))X
RID PART LEVEL SUBPART QUANTITY
----------- -------- --------- -------- -----------
1 01 1 02 2
2 02 2 05 7
3 05 3 10 10
4 05 3 11 10
5 02 2 06 6
6 06 3 12 10
7 06 3 13 10
8 01 1 03 3
9 03 2 07 6
10 07 3 12 8
11 07 3 14 8
12 01 1 04 4
13 04 2 08 10
14 04 2 09 11
15 01 1 06 3
16 06 2 12 10
17 06 2 13 10
Here is the source code:
* PROCESS SYSTEM(MVS);
RSQL: PROC(PKEY,RID,PART,LEVEL,SUBPART,QUANTITY,
PKEY_IV,RID_IV,PART_IND,LEVEL_IV,
SUBPART_IV,QUANTITY_IV,
UDF_SQLSTATE,UDF_NAME,UDF_SPEC_NAME,
UDF_DIAG_MSG,UDF_SCRATCHPAD,
UDF_CALL_TYPE,UDF_DBINFO)
OPTIONS(FETCHABLE REENTRANT);
DCL PKEY CHAR(8) VAR;
DCL (RID,HPQ) BIN FIXED(31);
DCL LEVEL CHAR(9);
DCL (PART,SUBPART) CHAR(8);
DCL QUANTITY BIN FIXED(31);
DCL (HPP,HPD,HPART) CHAR(8);
DCL 1 UDF_SCRATCHPAD,
3 UDF_SPAD_LEN BIN FIXED(31),
3 UDF_SPAD_TEXT CHAR(100),
5 COUNTR BIN FIXED(31);
%INCLUDE UDFINFO;
EXEC SQL INCLUDE SQLCA;
DCL RIDP(5000) BIN FIXED(31) STATIC;
DCL PARP(5000) CHAR(8) STATIC;
DCL LEVP(5000) CHAR(9) STATIC;
DCL SUBP(5000) CHAR(8) STATIC;
DCL QUAP(5000) BIN FIXED(31) STATIC;
DCL (K,IJ) BIN FIXED(15) STATIC;
IF COUNTR>5000 THEN UDF_SQLSTATE='02000';
IF UDF_CALL_TYPE=-2 THEN DO;
DCL LEVELP CHAR(9) INIT(' ');
IJ=0;K=0;COUNTR=0;
CALL RECURS(PKEY,1);
RECURS:PROC(PK,LEV) RECURSIVE;
DCL (I,NUMD) BIN FIXED(15);
DCL PK CHAR(8) VAR;
DCL LEV PIC '9';
HPART=PK;
EXEC SQL DECLARE C1 CURSOR WITH HOLD FOR
SELECT PART,SUBPART,QUANTITY
FROM PARTLIST
WHERE PART=:HPART
ORDER BY SUBPART;
EXEC SQL SELECT COUNT(*)
INTO :NUMD
FROM PARTLIST
WHERE PART=:HPART;
IF NUMD=0 THEN RETURN;
BEGIN;
DCL 1 TP(NUMD),
2 TSP CHAR(8),
2 TSD CHAR(8),
2 TSQ BIN FIXED(31);
EXEC SQL OPEN C1;
DO I=1 TO NUMD;
EXEC SQL FETCH C1 INTO :HPP,:HPD,:HPQ;
TSP(I)=HPP;TSD(I)=HPD;TSQ(I)=HPQ;
END;
EXEC SQL CLOSE C1;
DO I=1 TO NUMD;
K=K+1;
RIDP(K)=K;
SUBSTR(LEVELP,LEV,1)=LEV;
LEVP(K)=LEVELP;
PARP(K)=TSP(I);
SUBP(K)=TSD(I);
QUAP(K)=TSQ(I);
LEVELP=' ';
CALL RECURS(SUBP(K),LEV+1);
END;
END;
END RECURS;
END;
IF UDF_CALL_TYPE=0 THEN DO;
COUNTR=COUNTR+1;
IJ=IJ+1;
IF IJ<=K THEN DO;
RID=RIDP(IJ);
LEVEL=LEVP(IJ);
PART=PARP(IJ);
SUBPART=SUBP(IJ);
QUANTITY=QUAP(IJ);
END;
IF IJ>4999 ! IJ>K THEN UDF_SQLSTATE='02000';
END;
IF UDF_CALL_TYPE= 2 THEN UDF_SQLSTATE='02000';
END RSQL;
This was first published in May 2003