DB2 9.5 SQL Procedure Developer Certification Examination 735 preparation, 2nd part

Before you start

About this series

Are you trying to get DB2 SQL Procedure Developer authentication (Exam 735)? If so, then you come to the right place. This series of 6 DB2 certification Preparation Tutorials covers all the basics-what you need to understand before you look at the first exam topic. Even if you're not going to get certified right away, this tutorial is perfect for you to start learning about DB2 V9.5 database development.

Before you start

About this tutorial

In this tutorial, you will learn about the DB2 9.5 SQL process, including an introduction to stored procedures, the benefits of using stored procedures, and the differences between the SQL process and the external process. Learn different SQL procedure statements and see how to invoke and share nested stored procedures. Test and deploy stored procedures and find ways to ensure the security of your SQL processes.

This tutorial is the second tutorial in a series of 6 tutorials that can be used to prepare DB2 9.5 SQL Procedure Developer 735. The content in this tutorial covers the objectives of the "SQL procedures" part of the test 2nd.

Prerequisite

This tutorial is written for linux®, unix®, or windows® database developers or administrators who are primary to intermediate. You should be familiar with using UNIX or Windows command-line shells, and have some practical knowledge about DB2 and SQL commands.

System Requirements

The examples in this tutorial are designed for DB2 9.5, which runs on the Windows operating system. But the concepts and information here are related to the DB2 that run on any distributed platform.

You can complete this tutorial without installing DB2 9.5.

Stored Procedures

What is a stored procedure?

A stored procedure (also known as a routine) is a database object that contains a series of SQL statements and application control logic that is executed directly on the database server to develop business logic. Business logic can be encapsulated in stored procedures, which can be invoked by a client application, other stored procedures, user-defined functions, or triggers through an SQL statement. Stored procedures can accept parameter values and change the behavior of the business logic based on the input data. The stored procedure can then return the output value to the caller, or return multiple result sets to the caller.

Stored procedures can be implemented using SQL Procedure Language (SQL PL) or a programming language (such as java™ or C).

Stored procedures can significantly improve the performance of distributed applications (applications running on remote systems) in the following ways:

Reduce network traffic

Reduce the work of application developers writing code

Provides an easy way to invoke remote stored procedures from a distributed client

Figure 1 shows how the stored procedures are used:

Figure 1. Reduce network traffic by using stored procedures