Creating the database

Create the database, create and populate the database tables, and create the stored procedure.

Before you begin

You must have installed a supported database server; see IBM® App Connect Enterprise system requirements. This scenario uses a DB2® database server.
Download a copy of the createdbtablesclients.zip file. This file contains an SQL script that creates and populates the database tables and the stored procedure that are needed in this scenario.

Tip: Depending on the security requirements of your database installation, you might need additional authorities to create a database. You might also need to edit the SQL script. Consult your database administrator.

Procedure

Complete the following steps to set up the sample DB2 database configuration that was used to develop the scenario:

Create a database that is named CLIENTS.
1. Open a DB2 command prompt and create the database. Click Start > All Programs > IBM DB2 > DatabaseInstance > Command Line Tools, and click Command Window. DatabaseInstance is your DB2 instance name, which by default is DB2COPY1 (default).
  
  A DB2 - CLP window opens.
2. Create the CLIENTS database. Run the following command: DB2 CREATE DB CLIENTS
  
  You receive the following message: DB20000I The CREATE DATABASE command completed successfully.
3. Test the database connection. Run the following command: DB2 CONNECT TO CLIENTS
Create the tables and stored procedure by using the SQL createdbtablesclients.sql script that is provided in the scenario.
1. Extract the file createdbtablesclients.zip.
2. From the DB2 command prompt, run the following command: db2 -vf Sqlscriptdirectory\createdbtablesclients.sql, where Sqlscriptdirectory\ is the directory where you extract createdbtablesclients.zip.

Results

You have a database, named CLIENTS, which contains the database tables, data, and stored procedure that you need to complete the scenario.

The stored procedure takes an input value for Employee Number, and returns data for the selected employee from four different tables in the database. Within the procedure, selected values from one table are used to select rows from other tables. By using the stored procedure, you can make a single call to the database. The database server then completes a number of operations before returning the result.

What to do next

Follow the steps in Configuring an integration solution to use a database stored procedure