DB2 Universal Database: Building Stored Procedures using the Database Definition View, Part 3 - Page 2
March 28, 2006
f. The Work with the SQL Statement page for this wizard opens. At this point, the SQL statement for your stored procedure is finished. (Note how the wizard generated schema and table qualifiers for the table and its columns this is considered a best practice.) Click Finish to return to the New SQL Stored Procedure wizard.
Before inserting the generated SQL statement into the New SQL Stored Procedure wizard, you can parse the generated statement by clicking Parse. If the statement fails to parse, an error will be returned to the Rational AD IDE.
For example, if you manually entered the SQL statement (or altered the generated statement) such that the SQL statement referenced a table that did not exist in the schema, Rational AD would return an error similar to this:
This feature greatly accelerates the application development design time experience because developers do not have to comb through build errors to find the source of database-related errors in their code.
Another rapid application development feature is the ability to run the generated SQL statement before inserting it into the stored procedure. When you click Execute, you have the opportunity to run the generated SQL statement (including passing the runtime engine any input parameters) and to view the results, as shown in the following figure:
Note: The Execute button will not be accessible if the generated SQL statement fails the parse operation.
After completing this step, the SQL Statements page for this wizard should look similar to this:
You can use the Add and Remove buttons to build more than one SQL statement for the stored procedure. You can import existing statements using the Import button.
4. The Parameters page opens. Ensure that your page looks similar to the following figure, and click Next.
You can add, change, and remove parameters using the corresponding buttons on the right side of this window. Because we specified the parameter INDEPT in the generation of the SQL statement, Rational AD automatically placed this parameter in this stored procedures parameter list.
You can also use this page to specify the type of error-code handling mechanism to associate with the stored procedure, as shown below:
5. The Code Fragments page opens.
You can see at this point that your stored procedure is really starting to take shape. Note how Rational AD shows you the data definition language (DDL) required to build this stored procedure throughout the entire process.
You can use this page to insert code fragments and make your stored procedures logic even more powerful. For example, you can enter an exception fragment that will launch other code to execute in the event of a certain error. You could attach a header fragment whereby you could execute some logic each time before the stored procedure is run. For example, many developers like to associate CREATE TABLE and INSERT INTO statements with their header fragments to automatically create and populate a table on which the stored procedure will be run. Typically, the logic will include an after-the-run fragment to delete the table and its contents. This type of methodology accelerates the application development cycle and minimizes complexity.
For this example, just click Next on this page.
6. The Summary page opens. Click Finish
To view the statement that will be used to generate this stored procedure, click Show SQL.
Note: Whenever the Finish button becomes active, you can click it to build the stored procedure. You may have noticed that as you progressed through the wizard, the Finish button became active before reaching the final page of this wizard, which indicates that the final pages in this wizard are optional items.
Since we specified that this stored procedure was to be built automatically by the Rational AD IDE when the wizard completed, Rational AD automatically builds the stored procedure when you click Finish. From the figure below, you can see that this operation was successful:
Since we instructed Rational AD to automatically build the stored procedure, there is no need to deploy it. At this point, you can test the stored procedure from the command line or the Rational AD IDE itself.
From the command line, just pass the argument (the department to which the employees belong) with the CALL statement, as shown below:
Alternatively, you can use the Rational AD IDE to test the stored procedure and pass an argument to it at run time by right-clicking the stored procedure and selecting Run, as shown below. (Simply enter a value for the INDEPT parameter as you did in the wizard in Step 3):
Wrapping it all up
In this article, I showed you how to build an SQL stored procedure that you can use to encapsulate business logic on the server side. One you have a stored procedure, there are all sorts of things you can do with it aside from just calling it from your application. The Rational AD IDE lets you wrap this stored procedure and expose it as a Web service with mere clicks of a button, and more.
In the next article, Ill focus on the Data Definition view again, but Ill show you how to create a user-defined function (UDF) that returns values, as well as a UDF that calls a Web service using the Rational AD integration with DB2 UDB. (Note that for Version 8 DB2 servers, you need the XML Extender. You also need to ensure that you have enabled your database for Web services for this section.)
About the Author
Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than ten years of experience with DB2 products and has written numerous magazine articles and books about it. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). Currently he is writing a book on the Apache Derby/IBM Derby database. You can reach him at: firstname.lastname@example.org.
IBM, DB2, DB2 Universal Database, i5/OS, Rational, WebSphere, and z/OS are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.
Windows is a trademark of Microsoft Corporation in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Linux is a trademark of Linus Torvalds in the United States, other countries, or both.
Other company, product, and service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2006. All rights reserved.
The opinions, solutions, and advice in this article are from the authors experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the authors knowledge at the time of writing.