IBM Integration Bus, Version 10.0.0.3 Operating Systems: AIX, HP-Itanium, Linux, Solaris, Windows, z/OS


Creating and deploying a stored procedure

Create and deploy a stored procedure to a DB2® database by using the IBM® Integration Toolkit.

Before you begin

Complete the tasks that are described in the following topics:
  1. Connecting to the database.
  2. Creating a data development project
Ensure that the Data Project Explorer view is displayed, and that you are still connected to the CLIENTS database.
  1. Click Window > Show View > Data Source Explorer if necessary.
  2. Reconnect to the CLIENTS database if necessary. Right-click CLIENTS, and then click Connect.

About this task

You create a stored procedure that takes an input value for Employee Number, and returns data for the selected employee from four different tables in the database. Within the procedure, selected values from one table are used to select rows from other tables. This stored procedure shows that you can make a single call to the database. The database server then completes a number of operations before returning the result.

The scenario demonstrates how to deploy a stored procedure from the IBM Integration Toolkit to a DB2 database. Typically, a database administrator creates and deploys stored procedures. The steps are included here to demonstrate the tools that IBM Integration Bus provides to interact with databases.

Procedure

  1. Expand CLIENTS > Schemas > ADDRESSBOOK > Stored Procedures.
  2. Right-click Stored Procedures, and then select New > With Routine Editor.
  3. In the window that opens, complete the following fields:
    Project
    StoredProceduresDDP
    Name
    EMPLOYEEDETAILS
    Language
    SQL
  4. Click Finish.

    The IBM Integration Toolkit creates the procedure, and opens the editor with a few lines already written. You also notice a new file EMPLOYEEDETAILS.spxmi in the Application Development view.

  5. Delete the existing contents of the editor, and paste in the following code.
    CREATE PROCEDURE EMPLOYEEDETAILS ( IN EmpLikeNoIn VARCHAR(50), OUT DescriptionOut VARCHAR(100) )
    LANGUAGE SQL
    	DYNAMIC RESULT SETS 4
    ------------------------------------------------------------------------
    -- SQL Stored Procedure 
    ------------------------------------------------------------------------
    P1: BEGIN
    
    	DECLARE WorkDep CHAR(50);
    	DECLARE EmpNum CHAR(50);
    	DECLARE ProjNum CHAR(50);
    	DECLARE depno CHAR(50);
    	DECLARE e_no CHAR(50);
    	DECLARE f_name CHAR(50);
    	DECLARE l_name CHAR(50);
    	DECLARE p_num CHAR(50);
    
    	-- Declare cursors --
    	-- 'cursor1' will return a result set of employee number, first name, last name
    	-- and the work department code from the EMPLOYEE table
    		
    	DECLARE cursor1 CURSOR WITH RETURN FOR
    		SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT FROM EMPLOYEE WHERE EMPNO IN( EmpLikeNoIn );
    
    	--'cursor2' returns department number and department name from table DEPARTMENT
    	-- the select statement uses the deparment code returned from cursor1
    	
    	DECLARE cursor2 CURSOR WITH RETURN FOR
    		SELECT DEPTNO, DEPTNAME  FROM DEPARTMENT WHERE DEPTNO IN( WorkDep );
    
    	--'cursor3' returns project number from table EMPPROJACT
    	-- the select statement uses the employee number returned from cursor1
    		
    	DECLARE cursor3 CURSOR WITH RETURN FOR
    		SELECT PROJNO FROM EMPPROJACT WHERE EMPNO IN( EmpNum );
    
    	--'cursor4' returns the project name from table PROJECT
    	-- the select statement uses the project number returned from cursor3
    
    	DECLARE cursor4 CURSOR WITH RETURN FOR 
    		SELECT PROJNAME FROM PROJECT WHERE PROJNO IN( ProjNum );
    
    	-- assign values to variables 
    
    		OPEN cursor1;
    		FETCH cursor1 INTO e_no, f_name, l_name, depno;
    		SET WorkDep = depno;
    		SET EmpNum = e_no;
    		CLOSE cursor1;
    
    		OPEN cursor3;
    		FETCH cursor3 INTO p_num;
    		SET ProjNum = p_num;
    		CLOSE cursor3;
    
    	-- Cursors left open for client application
    
    		OPEN cursor1;
    		OPEN cursor2;		
    		OPEN cursor3;
    		OPEN cursor4;
    
    	SET DescriptionOut = 'This is an IBM Integration Bus stored procedure.';
    END P1
    
    
  6. Save the procedure.
  7. In the Data Project Explorer view, right-click the procedure and then click Deploy.
  8. On the Deploy Options and Routine Options pages, click Next.
  9. On the summary window, click Finish. You see confirmation that the stored procedure is deployed to the database.
    Screen shot showing that the stored procedure is deployed to the CLIENTS database.
  10. Optional: To confirm deployment by using DB2 tools, take the following steps:
    1. Open a DB2 command prompt and create the database. Click Start > All Programs > IBM DB2 > DatabaseInstance > Command Line Tools, and select Command Window. DatabaseInstance is your DB2 instance name, which by default is DB2COPY1 (default).

      A DB2 - CLP window opens.

    2. Type db2 connect to CLIENTS to open a connection to the database.
    3. Type the following command to confirm that the stored procedure is installed:
      db2 "select procname from syscat.procedures where procschema = 'ADDRESSBOOK'"
      Details of the stored procedure are displayed:
      PROCNAME
      
      --------------------------------------------------------------------------------
      ------------------------------------------------
      EMPLOYEEDETAILS
    4. Type db2 terminate to close the DB2 connection.

What to do next

Importing an application

sp_01_15_.htm | Last updated 2015-11-25 08:25:32