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