96 Building SOA-based Solutions for IBM System i Platform
6.4.1 Setting up the environment
Before you can begin developing stored procedures in WebSphere Development Studio client
for iSeries, you must enable the database development capabilities. These capabilities are
groups of functions that can be disabled or enabled as you need them. When you start the
workbench for the first time, the database development capabilities are disabled.
Follow these steps to enable the database development capabilities:
1. Start WebSphere Development Studio client for iSeries.
2. From the menu bar select Window Preferences.
3. Expand the Workbench node, and click Capabilities.
4. Expand the Database Developer node in the Capabilities list.
5. Select Core Database Development and Stored Procedure and User-Defined
Function Development as shown in Figure 6-23.
6. Click OK.
Figure 6-23 Enabling database capabilities
6.4.2 Creating and building an SQL stored procedure
In the previous example, you created a dynamic Web project and a database connection. So
you can skip these steps and move forward to the steps to create a stored procedure.
This section explains how to use a wizard to create a DB2 SQL stored procedure. This simple
procedure receives two parameters and returns a result set.
Chapter 6. DB2 UDB Web service 97
Follow these steps to create the stored procedure:
1. If you followed the steps in the previous example, your workbench should display the
J2EE perspective. Now, select Window Open Perspective Data to switch to the
Data perspective.
2. In the Data Definition view navigate to the Stored Procedures folder: expand
db2dadxws WebContent db2dadxws <connection name> FLGHT400 (see
Figure 6-24).
Figure 6-24 Data Definition view
3. Right-click the Stored Procedure folder and select New SQL Stored Procedure.
4. The new window displays. In the name field, enter a name for the stored procedure, such
as spSelectFlights in our example. Select the “Build” and “Enable for use in DADX Web
services” check boxes. Click Next.
Figure 6-25 Creating a new SQL stored procedure
98 Building SOA-based Solutions for IBM System i Platform
5. On the New SQL stored procedure panel, select One in the Result set drop-down
selection list. Then click SQL Assist as shown in Figure 6-26.
Figure 6-26 Select Result set and SQL Assist
6. In the Create A New SQL Statement panel, select the value SELECT in the SQL
statement drop-down list and select the “Be guided through creating an SQL statement”
option (see Figure 6-27). Click Next.
Figure 6-27 Specify SQL statement information
Chapter 6. DB2 UDB Web service 99
7. In the next panel, we compose the actual SQL statement. On the Tables tab, drill down in
the FLGHT400 database until you can select the FLGHT400.FLIGHTS table. Click the >
button to add it to the selected tables window (see Figure 6-28).
Figure 6-28 Select the FLIGHTS table on the Tables tab page
100 Building SOA-based Solutions for IBM System i Platform
8. You do not need to select individual columns, and you do not need to create table joins.
So, click the Conditions tab page and perform the following actions on this tab:
a. Enter these values on the first line (see Figure 6-29):
i. In the Column field, double-click the first cell and select
FLIGHTS.DEPARTURE_INITIALS from the drop-down list.
ii. Double-click in the first cell of the Operator column. Select the equal sign (=) from
the drop-down list.
iii. Double-click the first cell in the Value column and enter :dept in the Value column.
iv. Double-click the first cell in the And/Or column and select AND.
Figure 6-29 Adding the conditions

Get Building SOA-based Solutions for IBM System i Platform now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.