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.
- 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, 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 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, 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.
Tip: 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.
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 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, and you can see the stored procedure
in the Data Source Explorer. You can view the results of the deployment
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.