Tuesday, November 8, 2011

A simple example about creating DB2 SQL stored procedure to return multiple Result Set

There are plenty of examples showing how to get multiple result sets in languages like Java or php. But, there are not so many simple stored procedure examples, which return multiple result sets. Here is a very simple DB2 stored procedure, which return two result set. Just following the steps, you will have a multiple results DB2 stored procedure for testing.

Step 1: Create a table:
CREATE TABLE DEPT
     (DEPTNO   CHAR(3)     NOT NULL,
      DEPTNAME VARCHAR(36) NOT NULL,
      PRIMARY KEY(DEPTNO))

Step 2: Populate data into table:
INSERT INTO DEPT (DEPTNO, DEPTNAME)
     VALUES ('B11', 'PURCHASING'),
            ('E41', 'DATABASE ADMINISTRATION') ;

Step 3: Create SQL store procedure:
CREATE PROCEDURE TESTMULTIRS
--We do not use this input parameter.
(IN i_cmacct CHARACTER(5)) 
RESULT SETS 2
LANGUAGE SQL
BEGIN 

DECLARE csnum INTEGER;

--Declare serial cursors as serial cursor consume less resources
--and we do not need rollable cursor.
DECLARE getDeptNo CHAR(50); --Be careful about estimated length here.
DECLARE getDeptName CHAR(200);
DECLARE c1 CURSOR WITH RETURN FOR s1; 
DECLARE c2 CURSOR WITH RETURN FOR s2;

SET getDeptNo = 'SELECT DEPTNO FROM DEPT';
SET getDeptName = 'SELECT DEPTNAME FROM DEPT'; 

PREPARE s1 FROM getDeptNo;
OPEN c1;

PREPARE s2 FROM getDeptName;
OPEN c2;

END;

Step 4: Call stored procedure in iNavigator:
call testmultirs('jia');

Then we can see outpu as below in inavigator:
and

11 comments:

  1. Hi, Yiyu,

    Thanks for the example above. The technique of defining multiple result sets in a DB2 stored procedure can speed up IBM i database queries from PHP.

    Keep sharing your discoveries,

    Alan

    ReplyDelete
  2. Thanks.. it works great..

    ReplyDelete
  3. Super... This was really Sweet and Simple example... Thanks a lot

    ReplyDelete
  4. Thank You!

    This example helped me write my first ever Db2 procedure.

    ReplyDelete
  5. can you tell me steps to execute the above stored procedure in DB2 9.7 Control center ?

    i mean should i save the above procedure with .sql extension and then call it or directly write above code in the Control center.

    ReplyDelete
  6. THANKS FINALLY I EXECUTED THIS CODE TODAY !!
    THANKSSSSS

    ReplyDelete
    Replies
    1. After creating the procedure how to ecexute that can you send the code for that.

      Delete
  7. Thank You!

    This example helped me write my first ever Db2 procedure. :)

    ReplyDelete
  8. how to find count of rows fetched through cursor

    ReplyDelete
  9. Works great was having a hard time finding a DB2 SP Simple Example

    ReplyDelete
  10. how to see the logic for already created procedure?

    ReplyDelete