Since the middle of 2006, Ive been writing about all
the great integration features in IBM DB2 9 for the Microsoft Visual Studio 2005
integrated development environment (IDE) and the accompanying ADO.NET 2.0 API.
In an article called Building
Tables with the DB2 Table Designer and Visual Studio 2005 I showed you
how to build a table using a feature unique to DB2 9 called a designer.
As you may recall, I mentioned that there are designers to build and create all
sorts of schema objects such as views and stored procedures, as well as
non-schema objects such as scripts and more. In this article, I want to show
you how to create a stored procedure using the SQL/PL Stored Procedure designer
and show you how you can leverage that stored procedure in a simple WinForm application.
To perform the steps in this
article, ensure that youve created the SAMPLE database by entering the db2sampl
command from your operating systems command prompt. In addition, ensure that
youve added a connection to this database in the Server Explorer view.
Building an SQL/PL stored procedure using a designer
To build an SQL/PL stored procedure that selects the
employees of a specific department based on using the DB2 9 SQL/PL Stored
Procedure designer in Visual Studio 2005, perform the following steps:
the entry for the SAMPLE database in the Server Explorer, select the Procedures
folder, and then Add New SQL Procedure with Designer
Procedure designer opens. Call this procedure SELECTSTAFFBYDEPT in the Procedure
identification section of this designer. Optionally, specify the schema
name under which this routine will be created and a comment describing it.
Since I dont show you the rich
debug integration between Visual Studio 2005 and the IBM add-ins for Visual
Studio 2005, you can leave the Debug mode setting as is, or set it to Allow
if you want to use the integrated debugging capabilities of this IDE for
the Procedure Parameters window, add a parameter to this routine by clicking Add
the Name field and rename this parameter to DEPTNUMB.
Import Parameters ()
to discover the data type of the parameter you want to pass to the routine that
retrieves employees within the STAFF table based on the DEPT column.
Import window opens. Optionally, use Schema name to filter the tables in
this window, find the STAFF table, select the DEPT column, move
it to the Selected list by clicking Add column to selected list (),
and click OK.
The IBM add-ins for Visual Studio
2005 give you some unique features that enhance the rapid application
development (RAD) experience of the Visual Studio 2005 IDE. The ability to discover
parameter data types is one of them. This prevents you from choosing a wrong
data type for the input parameter to be used by the stored procedure you are
building, which in turn reduces potential coding errors.
There are a host of other unique DB2
9 RAD features for Visual Studio 2005, some of which I covered in previous
articles. These features include column discovery for table creation,
IntelliSense hookup to the underlying DB2 schema (which Ill show you later in
this article), a specific IBM Output Message Pane that details specific DB2 data
server activity and error messages, and more.
The Procedure Parameters portion
of this designer should now look similar to this:
the DEPTNUMB parameter by selecting it and clicking Delete Parameter
Rename the DEPT parameter to DEPTNUMB.
Obviously, you can choose to
specify or discover the correct parameter immediately; I took you through these
extra steps to illustrate this designers multiple features.
the Procedure Identification and Procedure Parameters sections of
this designer by clicking their associated toggle ()
designer should now look like this:
select ROUTINENAME, ROUTINESCHEMA from SYSIBM.SYSROUTINES;
select * from STAFF where dept = deptnumb;
Note: When you enter
this new statement, you can leverage the IntelliSense hookup to the data
servers schema by specifying the schema name before the STAFF table, and the
schema and table names before the DEPT column, as shown below:
10. Build this stored procedure
by clicking Ctrl+S, and then Yes.
The many other features of the SQL/PL
Stored Procedure designer are outside the scope of this article. I recommend that
you experiment by building other routines to learn all about the capabilities
of this wonderful and unique tool.
11. Refresh the Procedures folders
view in the Server Explorer by right-clicking this folder, and selecting Refresh
if you need to.
12. Test your stored procedure
by passing it a value of 20 to the DEPTNUMB parameter as follows:
the SELECTSTAFFBYDEPT stored procedure and select Run. The Run
Options window opens.
the Null check box.
Value and enter 20.
These steps are shown
Notice that when you run a
procedure using the IBM add-ins for Visual Studio 2005, you can leverage options
for pre- and post-run script processing. For example, you may want a pre-run
script to create and populate a table with data, while a post-run script may
delete the table. A Commit after run script may check the routines code into
Visual Source safe, and so on. In addition, the next time you run this routine,
the stored procedure will cache the entered parameters value to make it easier
Your results are returned in a DB2
data grid and should be similar to this:
The DB2 data grid in Visual Studio
2005 is feature-rich and capable of many operations such as import and export
of data (including XML), projections, restrictions, row-level filtering,
sorting, and more.
Using an SQL/PL stored procedure in your application
Now that youve quickly built a DB2
SQL/PL stored procedure, Ill show you how to quickly incorporate it into your
Note: While this
article showed you how to build a stored procedure that resides in a DB2 data
server running Linux, UNIX, or Windows, you could easily extend this capability
to DB2 for i5/OS or DB2 for z/OS using DB2 Connect. The process would be pretty
much the same.
a new Visual Basic application called CallMySPProject by selecting File>New Project>Windows Application, selecting Visual Basic (which
might be located under Other Languages), entering the project
name in the Name field, and clicking OK:
Note: Unlike other
applications built in this series, this application will be built in Visual
that the Add Data Source view is displayed in the IDE by pressing Shift+Alt+Delete
or selecting Data>Show
Data Sources from the menu bar.
Add New Data Source in the Data Sources view, select Database,
and click Next:
the SAMPLE database from the Which data connection should your application
use to connect to the database? drop-down list.
the Yes, include sensitive data in the connection string radio button
and click Next. (Im assuming that youre working on your own test
machine here and notice that Visual Studio 2005 collapses the Connection
String field automatically).
the defaults on the next page of this wizard. Click Next.
the Stored Procedures toggle, select the SELECTSTAFFBYDEPT stored
procedure, and then click Finish:
Note: This step is a
little different in previous articles from what Ive shown below since we are populating
a data set from a stored procedure instead of directly from a table.
The designer palette should now
look similar to the following one. (You may need to expand the SAMPLEDataSet
the SELECTSTAFFBYDEPT stored procedure from the Data Sources view
and drop it on your WinForm; it should automatically add a data grid and tool
strips and look like this:
You can see that two different
tool strips were automatically created after you performed this function. (This
is different from what I showed you in the Using
the FILLBY Method to Filter DB2 9 Data Sets...with a Twist article. Now, this function will be handled
by the stored procedure code that you built earlier in this article.
on the pull-down menu to the right of the Fill tool strip, and select Label.
Note: If there is no
drop-down menu beside the Fill tool strip, click Fill and it
The toolStripLabel1 (or
whatever it is named in your IDE) should now appear in the Fill tool
10. Right-click the toolStripLabel1
object, select Properties, and then change its Text property
to Department Number:
11. Create a field where you can
specify the input parameter for your stored procedure by clicking the Fill drop-down
box and selecting Textbox.
12. Double-click Fill to
see all the code that was generated for you:
You can see that the DEPTNUMB
variable was automatically dimensioned (DIMed) for you by Visual Studio 2005.
13. Change the code as
Dim DEPTNUMB As Object = New Object
Dim DEPTNUMB As Object = ToolstripTextBox1.Text
14. Press F5 to build the
application. The application opens:
15. Enter a valid department
number for the STAFF table in the Department Number field, and press Fill.
The WinForm passes the parameter value in the Department Number field to
the SELECTSTAFFBYDEPT stored procedure and populates the data grid with the
You can use any valid parameter
for department number in the STAFF table. If you pass the application a
department number that doesnt exist in the STAFF table, it will simply return
an empty result set.
The following figure shows the passing
of department number 20 to the SELECTSTAFFBYDEPT stored procedure:
Wrapping it up...
In this article, I showed you how to use the SQL/PL designer
to create a stored procedure that takes as input a parameter used to represent
the DEPT column in the STAFF table. I showed you some of the great design-time
enhancement features of the SQL/PL Procedure designer with its IntelliSense
hookup to the database schema and the ability to import the input parameter
from the table itself (both of which reduce design time and help prevent costly
errors). I then showed you how to include your stored procedure in an
See All Articles by Columnist Paul C. Zikopoulos
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 UDB and has written over one hundred magazine articles and several
books about it. Paul has co-authored the books: DB2 9 New Features (available
soon), 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). In his spare time,
he enjoys all sorts of sporting activities, running with his dog Chachi, and
trying to figure out the world according to Chloë his new daughter. You can
reach him at: email@example.com.
IBM, DB2, DB2
Connect, DB2 Universal Database, i5/OS, and z/OS are trademarks or registered trademarks
of International Business Machines Corporation in the United States, other countries, or both.
Microsoft and Windows
are trademarks 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 registered
trademark of Linus Torvalds in the United States,
other countries, or both.
product, and service names may be trademarks or service marks of others.
International Business Machines Corporation, 2007. All rights reserved.
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.