Exercise 1.3: Creating and building an SQL stored procedure

Before you begin, you must complete Exercise 1.2: Creating a connection to the DB2 SAMPLE database and copying it to your project.

In this exercise, you will use a wizard to create a DB2(R) SQL stored procedure. This simple stored procedure returns employee information from the EMPLOYEE table when the employee's department ID matches a value that the user enters. You will use another wizard to create the SQL statement, although you could also type the statement directly in to the wizard that creates the stored procedure.

As you are using the wizard, you can see more information about each field in the wizard by putting keyboard focus in the field and pressing F1.

To create a SQL stored procedure:

  1. In the Data Definition view, navigate to the SPSimple/SAMPLE/SCHEMANAME/Stored Procedures folder.
  2. Right-click the Stored Procedure folder, and select New > SQL Stored Procedure. The New SQL Stored Procedure wizard opens.
  3. In the Name field, type SPEmployee.
  4. Select the Build and Enable debugging check boxes, and click Next.

    In later steps, you will be debugging the stored procedure using the integrated SQL stored procedure debugger that is included with the workbench, so you are building it and enabling debugging here. When you build a SQL routine, the workbench registers the routine in the catalog table of the target database.

    If you did not select the build options here, you could build and enable the stored procedure for debugging later by right-clicking the stored procedure in the Data Definition view and selecting Build for Debug from the pop-up menu.

  5. Click SQL Assist.

    This launches the wizard that will guide you through creating an SQL statement.

  6. On the first page of the SQL Assist wizard, keep the defaults to create a SELECT statement using the wizard, and click Next.
  7. In the Available Tables list, navigate to the SCHEMANAME/Tables/SCHEMANAME.EMPLOYEE table.
  8. Click > to move the table to the Selected tables list.
  9. Click the Columns tab.
  10. Expand the SCHEMANAME.EMPLOYEE tree, and select EMPNO, FIRSTNME, LASTNAME, and WORKDEPT.
  11. Click > to move the columns to the Selected columns list.
  12. Click the Conditions tab.
  13. In the Column column, select EMPLOYEE.WORKDEPT.
  14. In the Operator column, select =.
  15. In the Value column, type :dept. This creates a host variable named "dept" that is used as an user input parameter for the stored procedure.
  16. Click Next to see the SQL statement that you created. The SQL statement should look something like this:
    SELECT SCHEMANAME.EMPLOYEE.EMPNO, SCHEMANAME.EMPLOYEE.FIRSTNME, SCHEMANAME.EMPLOYEE.LASTNAME, SCHEMANAME.EMPLOYEE.WORKDEPT FROM SCHEMANAME.EMPLOYEE WHERE SCHEMANAME.EMPLOYEE.WORKDEPT = :dept
  17. Click Finish to close the SQL Assist wizard, then click Next in the New SQL Stored Procedure wizard.
  18. On the Parameters page, in the SQL error handling code field, select SQLSTATE and SQLCODE.

    In the Parameters table, you should see the output parameters for SQLSTATE and SQLCODE, and the input parameter (dept) that you created. On this page you can optionally create other user parameters, but for the purposes of this tutorial you will keep the parameters as they are.

  19. Click Next. On the Code Fragments page of the wizard, you can optionally specify files that contain code fragments to insert in the stored procedure body, but in this case you will not do this.
  20. Click Next to review the stored procedure options, and click Finish to create and build the stored procedure.

The stored procedure, SPEmployee, appears in the Data Definition view in the Stored Procedures folder under the schema in which you created it. The stored procedure also opens in the editor, where you can view and edit the source code. Because you selected the Build and Enable debugging options, the stored procedure is automatically built and enabled for debugging. The icon next to the stored procedure in the Data Definition view should have a wrench on it to indicate that the stored procedure has been built. You can view the results of the build in the DB Output view.

At this point, you could optionally use the editor to make changes to the stored procedure depending on your business solution.

Now you are ready to begin Exercise 1.4: Debugging the stored procedure.

Terms of use | Feedback
(C) Copyright IBM Corporation 2000, 2005. All Rights Reserved.