In this exercise, you will use a wizard to create a DB2® SQL stored procedure to return
employee information from the EMPLOYEE table based on the employee's
department ID the user enters. You will use a second wizard to create
the SQL statement that queries the database.
Creating a routine in the workbench saves the source code
to the project workspace. When the routine is deployed, the workbench
performs the following actions:
- The source code is compiled on your client workstation (JAVA)
or on the server (SQL)
- 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 SPDevelopment.
- Right-click on Stored Procedures,
and select . The New Stored
Procedure wizard opens.
- In the Name field, type SPEmployee.
- In the Language field, select 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.
- Ensure that the, Be guided through creating
a SQL statement, radio button is selected. Click Next.
- In the Available Tables list, expand schemaname and
select the schemaname.EMPLOYEE table.
- Click the > button to move this table to the Selected
Tables list.
- Click the Columns tab from the top
of the Construct an SQL Statement page of the
wizard.
- 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. The
= operator should appear automatically in the Operator column to the
right.
- In the Value column, type :dept and
press Enter. This action
creates a host variable,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.
- 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 will 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, keep the parameters as they are.
- Click Next.
- On the Deploy Options page of the
wizard, select the Deploy on Finish and Enable
debugging check boxes.
Note: Later
in this tutorial, you will debug this stored procedure by using the
integrated SQL stored procedure debugger included in the workbench.
Deploying the procedure and enable debugging here allows for you to
later use the procedure debugger. If you did not select the deploy
and enable debugging 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 context 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.
Tip: Click Show
SQL to preview the SQL for the new stored procedure before
you create it.
- 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 wrench icon
next to the stored
procedure in the Data Project Explorer view
indicates the stored procedure is deployed and can be viewed in the Data
Source Explorer. The results of the deployment can be
viewed in the SQL Results view.
At
this point, you could optionally use the editor to make changes to
the stored procedure, depending on your business need.