Recursive SQL

An example of how to use recursive SQL for "bill of material" applications on mainframe DB2 platforms.

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

Dig deeper on iSeries SQL commands and statements

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchEnterpriseLinux

SearchDataCenter

Close