To use a stored procedure, you write two programs: the stored procedure itself, which runs on a database server, and a client application, which runs on a client workstation. The client application calls the stored procedure by using one of the available API methods.
IBM DB2 Stored Procedure Builder (SPB) assists you with creating a stored procedure that runs on a database server. You must write the client application separately.
SPB is a graphical application that supports the rapid development of DB2 stored procedures. Using SPB, you can perform the following tasks:
SPB provides a single development environment that supports the entire DB2 family ranging from the workstation to System/390. You can launch SPB as a separate application from the DB2 Universal Database program group, or you can launch SPB from any of the following development applications:
SPB is implemented with Java and all database connections are managed by using Java Database Connectivity (JDBC). Using a JDBC driver, you can connect to any local DB2 alias or any other database for which you can specify a host, port, and database name. Several JDBC drivers are installed with SPB.
| Note: | To use SPB, you must be connected to a DB2 database for development. |
For more information about using SPB, refer to the IBM DB2 Stored Procedure Builder Help.
SPB provides an easy-to-use development environment for creating, installing, and testing stored procedures, so that you can focus on creating your stored procedure logic rather than the details of registering, building, and installing stored procedures on a DB2 server. Additionally, with SPB, you can develop stored procedures on one operating system and build them on other server operating systems.
Using SPB greatly simplifies the process of creating and installing stored procedures on a DB2 database server. The new procedure SmartGuide and SQL Assistant facilitate the development of stored procedures.
In SPB, you create stored procedures in Java. As a result, the stored procedures are highly portable among operating systems. Using the new procedure SmartGuide, you create your basic SQL structure; then, you can use your editor to modify the stored procedure so that it contains highly sophisticated stored procedure logic.
When creating a stored procedure, you can choose to return a single result set, multiple result sets, or output parameters only. You might choose not to return a result set when your stored procedure creates or updates database tables. You can use the new procedure SmartGuide to define input and output parameters for a stored procedure so that it receives values for host variables from the client application. Additionally, you can create multiple SQL statements in a stored procedure; the stored procedure receives a case value and selects one of a number of queries.
Building a stored procedure on a target database is as simple as clicking Finish in the new procedure SmartGuide. You no longer have to manually register the stored procedure with DB2 by using the CREATE PROCEDURE statement.
After you successfully build a stored procedure on a database server, you are ready to modify, rebuild, run, and test the procedure. By modifying a stored procedure, you can add methods to the code to include sophisticated stored procedure logic. When you open a stored procedure, the stored procedure source code is displayed in the details pane on the right side. The details pane has a language sensitive editor for stored procedures written by using Java.
Running a stored procedure from within SPB allows you to test the procedure to make sure that it is correctly installed. When you run a stored procedure, depending on how you set it up, it can return result sets based on test input parameter values that you enter. Testing stored procedures makes programming the client application easier because you know that the stored procedure is correctly installed on the DB2 database server; you can focus on writing and debugging the client application.
From the Project window, you can easily drop a stored procedure or copy it to another database connection.
When you open a new or existing SPB project, the Project window shows all the stored procedures that reside on the DB2 database to which you are connected. You can choose to filter stored procedures so that you view procedures based on their name or schema. An SPB project saves only connection information and stored procedure objects that have not been successfully built.
Using SPB and the debug client, you can remotely debug a stored procedure installed on a DB2 server. To debug a stored procedure, you run the stored procedure in debug mode. You are not required to debug the stored procedures from within an application program. You can separate testing your stored procedure from testing the calling application program.
Using the Debug Settings notebook in SPB, you can view all the stored procedures that you have the authority to change, add, or remove debug entries in the stored procedures debug table. If you are a DBA or you created the selected stored procedure, you can grant authorization to other users to debug the stored procedure.