Create, test, and deploy a Db2 SQL stored procedure
This tutorial teaches you how to create, test, and deploy a Db2® SQL stored procedure, which can improve application performance by reducing database access traffic. For each SQL statement, a database manager application must initiate a separate communication with Db2. Database performance is improved because the stored procedure runs the SQL statement on the server rather than on the client. Db2 stored procedures also help to centralize business logic by immediately changing a stored procedure available to all client applications.
An SQL stored procedure is a stored procedure whose source
code is part of the CREATE PROCEDURE statement or the stored procedure
body.
Learning objectives
These exercises teach you how to:- Set up the workbench environment for stored procedure development
- Connect to the Db2 SAMPLE database and create a data development project to work with the database
- Create an SQL stored procedure
- Deploy, debug, and run the stored procedure from the workbench
- Export and deploy the stored procedure from the file system
This tutorial takes approximately 60 minutes to finish.
If you explore other concepts that are related to this tutorial, it
can take longer to complete.
Skill level
AdvancedAudience
Database developersSystem requirements
- You must install and configure the SAMPLE database that is included with Db2 for Linux®, UNIX, and Windows. For more information about the SAMPLE database, see the Db2 documentation and First Steps.
- To follow the steps in this tutorial for debugging a stored procedure, you must have Db2 for Linux, UNIX, and Windows Version 9.1 or later.
Prerequisites
To complete this tutorial, you must be familiar with the following concepts:- Database development
- SQL and SQL stored procedure development
- Set up your environment
In this first exercise, you open the Data perspective, where all the work for this tutorial is done. - Create a connection to the Db2 SAMPLE database
The workbench provides a New Connection Wizard that makes it easy to connect and display the status of connections in both Db2 databases and other databases. In this tutorial, you connect to the Db2 SAMPLE database. - Create a project for stored procedure development
To create database objects, you must first create a data development project in which to store the objects. Each data development project is associated with a single database connection. In this tutorial, you use the connection information from theSAMPLE4tutorialconnection to create the data development project. - Create and deploy an SQL stored procedure
In this exercise, you use a wizard to create a Db2 SQL stored procedure. The stored procedure uses an employee's department ID that the user enters to return employee information from the EMPLOYEE table. You use a second wizard to create the SQL statement that queries the database. - Debug the stored procedure
The workbench includes an integrated stored procedure debugger for SQL or Java™ stored procedures. Support for specific languages depends on your operating system and Db2 version. For more information about debugger support, see the stored procedure debugger help topics in the information center. - Run the stored procedure
You can run the stored procedure to test the execution of the run, the existence of the run set, the logic of the routine, and the accuracy of the output arguments and result sets. Results of running a stored procedure from the workbench are displayed in the SQL Results view. - Export and deploy the stored procedure from the file system
In an earlier exercise, you deployed a stored procedure from the workbench. You can also deploy a stored procedure from an exported script on the file system. As you finish creating the stored procedure, export it to a script on the file system, run the exported script from an independent file system, and deploy the stored procedure to a local database or remote server. - Summary
You can now create, test, and deploy a Db2 SQL stored procedure.