Building SOA-based Solutions for IBM System i Platform

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