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:
SPEmployee
.
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.
This launches the wizard that will guide you through creating an SQL statement.
:dept
. This creates a host variable named "dept" that is used as an
user input parameter for the stored procedure.
SELECT
SCHEMANAME.EMPLOYEE.EMPNO, SCHEMANAME.EMPLOYEE.FIRSTNME, SCHEMANAME.EMPLOYEE.LASTNAME, SCHEMANAME.EMPLOYEE.WORKDEPT FROM SCHEMANAME.EMPLOYEE WHERE SCHEMANAME.EMPLOYEE.WORKDEPT = :dept
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.
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.