Stored Procedure Builder provides an easy-to-use development environment for creating, installing, and testing stored procedures, helping you to focus on creating your stored procedure logic rather than the details of registering, building, and installing stored procedures on a DB2 server. Stored Procedure Builder helps you develop cross-platform applications by enabling you to build a stored procedure on server platforms that differ from the platform on which you develop the stored procedure.
Using Stored Procedure Builder greatly simplifies the process of creating and installing stored procedures on a DB2 database server. The stored procedure wizards and the SQL Assistant facilitate the development of stored procedures.
In Stored Procedure Builder you can create highly portable stored procedures written in Java or SQL. Using the stored procedure wizards, you create your basic SQL structure and then use the source code editor to modify the stored procedure to contain 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 stored procedure wizards 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, allowing the stored procedure to receive a case value and then to select one of a number of queries.
To build a stored procedure on a target database, simply click Finish in the stored procedure wizards. You do not 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 in Stored Procedure Builder, the source code is displayed in the editor. The editor is language sensitive for stored procedures written in Java or SQL.
Running a stored procedure from within Stored Procedure Builder allows you to test the procedure to make sure that it is correctly installed. When you run a stored procedure, it can return result sets based on test input parameter values that you enter, depending on how you set up the stored procedure. 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 then focus on writing and debugging the client application
From the Project window in Stored Procedure Builder, you can also easily drop a stored procedure or copy it to another database connection.
When you open a new or existing Stored Procedure Builder 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 to view the procedures based on their name or schema. A Stored Procedure Builder project saves only connection information and stored procedure objects that have not been successfully built to the database.
Using Stored Procedure Builder and the IBM Distributed Debugger (available separately), you can remotely debug a stored procedure installed on a DB2 server. To debug a stored procedure, you build the stored procedure in debug mode, add a debug entry for your client IP address, and run the stored procedure. 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 Stored Procedure Builder, you can view all the stored procedures that you have the authority to change, add, or remove debug entries for in the stored procedures debug table. If you are a database administrator or the creator of the selected stored procedure, you can grant authorization to other users to debug the stored procedure.