Rational Developer for System z


< Previous | Next >

Creating and deploying an SQL stored procedure

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:

  1. In the Data Project Explorer view, expand SPDevelopment.
  2. Right-click on Stored Procedures, and select New > Stored Procedure. The New Stored Procedure wizard opens.
  3. In the Name field, type SPEmployee.
  4. In the Language field, select 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.
  8. Ensure that the, Be guided through creating a SQL statement, radio button is selected. Click Next.
  9. In the Available Tables list, expand schemaname and select the schemaname.EMPLOYEE table.
  10. Click the > button to move this table to the Selected Tables list.
  11. Click the Columns tab from the top of the Construct an SQL Statement page of the wizard.
  12. Expand the schemaname.EMPLOYEE tree, and select EMPNO, FIRSTNME, LASTNAME, and WORKDEPT.
  13. Click the > button to move the columns to the Selected Columns list.
  14. Click the Conditions tab.
  15. Double-click in the first row in the Column column. A drop-down box appears.
  16. Click on the down arrow and select EMPLOYEE.WORKDEPT. The = operator should appear automatically in the Operator column to the right.
  17. 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.
  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.
  20. Click Next in the New Stored Procedure wizard.
  21. 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.
  22. Click Next.
  23. 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.
    .
  24. 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.
  25. 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.
  26. 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.

< Previous | Next >

Feedback