Stored Procedure Builder | DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)


Team-Fly

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents

	Chapter 8. Stored Procedures

Stored Procedure Builder

The SPB (Figure 8-2) is a graphical Windows-based application that supports the rapid development of DB2 stored procedures written in either Java or SQL. It can work as a Microsoft Visual Studio plug-in, with Microsoft Visual Basic, with IBM VisualAge for Java, or standalone. It is used to generate a stored procedure, which DB2 will build into an external C language load module without requiring the user to log on to TSO. The SPB supports the entire DB2 family from a single development environment. There are many options, and we will examine each of them.

Figure 8-2. Using SPB to Create Stored Procedures

graphics/08fig02.gif

Using SPB, you can perform the following tasks :

Create new stored procedures.
Build stored procedures on local and remote DB2 servers.
Modify and rebuild existing stored procedures.
Run stored procedures to test the execution of installed stored procedures.

The SPB is implemented with Java, and all database connections are managed by using JDBC, so you can connect to any local DB2 alias or any other database for which you can specify a host name, port, and database name .

There is a stored procedure SmartGuide and SQL Assistant to help you develop stored procedures using the SPB. You launch the stored procedure SmartGuide from the Project window of the SPB. But the most important feature is that you can create stored procedures in Java and SQL. Creating stored procedures in Java and SQL produces stored procedures that are highly portable. You can use the code editor to modify the stored procedure so that it contains highly sophisticated stored procedure logic.

NOTE

Using the SPB is also a nice way to very rapidly develop a C program that can then be maintained and developed further simply as C source code.

When creating a stored procedure, you can choose to return a single result set, multiple result sets, or output parameters only. You can use the stored procedure SmartGuide to define input and output parameters for a stored procedure so that it receives values for host variables from the client application. When you finish building a stored procedure on a target database, the SPB process will drive the create procedure process in DB2, so there is nothing more to do other than use the SPB.

After successfully building a stored procedure, the SPB will allow you to work with existing stored procedures in order to modify, rebuild, run, or test a procedure. When you open a stored procedure, the stored procedure source code is displayed in the editor pane on the right side. The editor pane has a language-sensitive editor for stored procedures written by using Java. Colors are used to designate between user code, syntax, reserved words, and so on. To test a stored procedure using this tool, you can run it from within the SPB to make sure that it is correctly installed. Testing stored procedures this way makes programming a lot easier, and you know that the stored procedure is correctly installed on the DB2 database server. You can also drop a stored procedure or copy it to another database connection from within the SPB.


Team-Fly

Top