This tutorial teaches you how to create, test, and
deploy a DB2 SQL stored procedure. Stored procedures can help improve application
performance and reduce database access traffic. All database access must go
across the network, which, in some cases, can result in poor performance.
For each SQL statement, a database manager application must initiate a separate
communication with DB2. To improve application performance, you can create
stored procedures that run on your database server. A client application can
then simply call the stored procedures to obtain results of the SQL statements
that are contained in the procedure. Because the stored procedure runs the
SQL statement on the server for you, database performance is improved. In
addition, stored procedures can help to centralize business logic. If you
make changes to a stored procedure, the changes are immediately available
to all client applications that use it.
An SQL stored procedure is a stored procedure in which the source
code is part of the CREATE PROCEDURE statement. The part of the CREATE PROCEDURE
statement that contains the code is called the stored procedure body.
Learning objectives
When
you complete the exercises, you will know how to do the following tasks:
- 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 should take approximately 60 minutes to finish.
If you explore other concepts related to this tutorial, it could take longer
to complete.
Skill level
Advanced
Audience
Database developers
System requirements
- You must install and configure the SAMPLE database that is included with
DB2 for Linux, UNIX, and Windows. See the DB2 documentation and First Steps
for more information
Prerequisites
In order to complete this tutorial,
you should be familiar with the following concepts:
- Database development
- SQL and SQL stored procedure development