In this exercise, you use a wizard to create a DB2 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 use another wizard to create the SQL statement, although you could also
type the statement directly into the wizard that creates the stored procedure.
When you create a routine in the workbench, you save the source
code in your project workspace. When you deploy a routine, the workbench performs
the following actions:
- For Java routines, the source code is compiled on your client workstation.
- For SQL routines, the source code is compiled on the server.
- The source code is copied to the DB2 server.
- The routine is registered in the catalog table.
To create and deploy an SQL stored procedure:
- In the Data Project Explorer view, expand the SPDevelopment project
to find the Stored Procedures folder.
- Right-click the Stored Procedures folder,
and select . The New Stored Procedure wizard opens.
- In the Name field, type SPEmployee.
- Keep the default language, which is SQL.
- Click Next.
- On the SQL Statements page, click Create SQL. This action launches the New SQL Statement wizard that guides
you through the creation of an SQL statement.
- On the first page of the New SQL Statement wizard, keep the defaults
to create a SELECT statement using the wizard, and click Next.
- In the Available Tables list, navigate to
the schemaname/schemaname.EMPLOYEE table.
- Click the > button to move the table to the Selected
Tables list.
- Click the Columns tab.
- Expand the schemaname.EMPLOYEE tree, and select
EMPNO, FIRSTNME, LASTNAME, and WORKDEPT.
- Click the > button to move the columns to the Selected
Columns list.
- Click the Conditions tab.
- Double-click in the first row in the Column column. A drop-down box appears.
- Click on the down arrow and select EMPLOYEE.WORKDEPT, and press Enter to
commit this selection.
- In the Operator column, accept the default
operator, =.
- In the Value column, type :dept and
press Enter. This action creates a
host variable named dept that is used as an user input parameter for the stored
procedure.
- Click Next to see the SQL statement that
you created. The SQL statement looks something like this:
SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT FROM SCHEMANAME.EMPLOYEE WHERE WORKDEPT = :dept
- Click Finish to close the New SQL Statement
wizard, and click Next in the New Stored Procedure
wizard.
- On the Parameters page, in the SQL error handling code field,
select SQLSTATE and SQLCODE. In the Parameters table,
you see the input parameter, dept, that you created and the output parameters
for SQLSTATE and SQLCODE. On this page, you can optionally create other user
parameters, but for the purposes of this tutorial you keep the parameters
as they are.
- Click Next.
- On the Deploy Options page of the wizard, select the Deploy and Enable
debugging check boxes. In later steps, you will debug
the stored procedure by using the integrated SQL stored procedure debugger
that is included with the workbench, so you are deploying the procedure and
enabling debugging here. If you did not select the deploy and enable debug
options here, you could manually deploy and enable the stored
procedure for debugging later by right-clicking the stored procedure in the
Data Project Explorer view and selecting Deploy from
the pop-up menu.
- 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. In this case, do not specify code
fragments.
- Click Next and review your selections on
the Summary page of the wizard.
- Click Finish to create and deploy the stored
procedure.
The stored procedure, SPEmployee, appears in the Data Project Explorer
view in the Stored Procedures folder under the project
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 Deploy and Enable
debugging options, the stored procedure is automatically deployed
and enabled for debugging. The icon next to the stored procedure in the Data
Project Explorer view has a wrench on it to indicate that the stored procedure
is deployed. You can view the results of the deployment in the Data Output
view.
At this point, you could optionally use the editor to make changes
to the stored procedure depending on your business need.