Featured Database Articles
Posted May 19, 2008
DB2 9.5 and IBM Data Studio - Part 10: Building Stored Procedures - Page 2
By Paul Zikopoulos
SP_FEMALEPERSONNEL, right-click, and select Deploy to build this
stored procedure on the target SAMPLE database:
Deploy Routines wizard opens. On the Select Connection page, ensure that the Use
current database radio button is selected in the Target database
you want to deploy this routine on a different database from the one your
database development project was bound to when it was created, select the Use
different database radio button and use the Database drop-down list
to select a database where this stored procedure should be built:
the previous figure, you can see that I could optionally deploy this SQL/PL
procedure on a database called STLEC1. STLEC1 happens to be a DB2 for z/OS data
server, and since the SQL API (and SQL/PL language) is more than ninety percent
common across the DB2 data server family, I could definitely deploy this
procedure on that data server if I wanted to. You can obviously use this feature
to deploy business logic built in a data development project thats bound to a
different data server (such as a development database) compared to the
you didnt have a database connection object defined for the target database,
you could define it in this window using the Connection button. This
launches you into the Database Connection wizard that I introduced you to in Part
1 of this series.
can use the Schema field in the Target schema for deploying an
unqualified routine box to specify the schema for the stored procedure. For
this example you can ignore this field.
the Treat duplicates as errors radio button in the Duplicate handling
can tell IBM Data Studio what to do when you try to deploy a stored procedure
on a database where a stored procedure with the same name within the same
schema already exists. If you select the Drop duplicates radio button,
IBM Data Studio will drop the existing stored procedure and create a new one
with the same name using the SQL/PL code in your project.
can deploy multiple stored procedures in a single deployment using IBM Data
Studio as well. If you select the Ignore duplicates and continue to the next
routine radio button, IBM Data Studio will simply skip any stored
procedures with the same name and build those that are unique on the target
the Deploy by building the source radio button to do a full build of the
stored procedure and deploy the SQL/PL source and the binaries to the database.
Now click Next.
The Deploy by building the source option instructs IBM Data
Studio to fully build the routine and deploy both the source and the binaries
to the database.
The Deploy using binaries if available in the database radio
button deploys binaries that youve built on the database instead of
doing a full build. If the routines binaries are not available, IBM Data
Studio will do a full build of the routine before deploying it. To simply
deploy the routines source to a database, select Deploy source to the
The Deploy Routines wizard opens. On the Routines Options page, select
the Enable debugging check box so you can leverage the distributed
debugging capabilities that are part of IBM Data Studio. Click Next.
You can use the Precompile options field to provide any options
for the compilation of your routine.
On the Summary page, click Finish.
You can use the Data Output view to see the results of the
requested build operation. If your routine built successfully, you will see
messages similar to the following ones:
After we used the wizard to create our stored procedure in step 8, the
routine didnt appear in the database because it wasnt yet built and deployed
on it. If you look in the Database Explorer view now, you should see that the routine
is indeed deployed on the server. (If you dont see it, right-click the Stored
Procedures folder and click Refresh):
At this point, any options that are available in the pop-up menu for objects in
the Database Explorer view are applicable to your routine. For example, to run
it, right-click the routine and select Run:
You can also generate a script file that would create and deploy this
procedure by selecting the Generate DDL option:
Wrapping it all up
article, I showed you how to take an SQL statement that you built using either
the SQL Editor or the SQL Builder and quickly turn it into a deployed SQL/PL stored
procedure. In addition, I also showed you how to move a data development
project between servers. In my next article, Ill show you how to build a
stored procedure from scratch and dive deeper into some of the options I didnt
cover in this article.