< Previous | Next >

Creating and deploying an SQL stored procedure

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:

  1. In the Data Project Explorer view, expand the SPDevelopment project to find the Stored Procedures folder.
  2. Right-click the Stored Procedures folder, and select New > Stored Procedure. The New Stored Procedure wizard opens.
  3. In the Name field, type SPEmployee.
  4. Keep the default language, which is SQL.
  5. Click Next.
  6. 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.
  7. On the first page of the New SQL Statement wizard, keep the defaults to create a SELECT statement using the wizard, and click Next.
  8. In the Available Tables list, navigate to the schemaname/schemaname.EMPLOYEE table.
  9. Click the > button to move the table to the Selected Tables list.
  10. Click the Columns tab.
  11. Expand the schemaname.EMPLOYEE tree, and select EMPNO, FIRSTNME, LASTNAME, and WORKDEPT.
  12. Click the > button to move the columns to the Selected Columns list.
  13. Click the Conditions tab.
  14. Double-click in the first row in the Column column. A drop-down box appears.
  15. Click on the down arrow and select EMPLOYEE.WORKDEPT, and press Enter to commit this selection.
  16. In the Operator column, accept the default operator, =.
  17. 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.
  18. 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
  19. Click Finish to close the New SQL Statement wizard, and click Next in the New Stored Procedure wizard.
  20. 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.
  21. Click Next.
  22. 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.
  23. 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.
  24. Click Next and review your selections on the Summary page of the wizard.
  25. 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.

< Previous | Next >