DB2 9.5 and IBM Data Studio - Part 10: Building Stored Procedures - Page 2

May 19, 2008

9.  Select SP_FEMALEPERSONNEL, right-click, and select Deploy to build this stored procedure on the target SAMPLE database:

10.  The Deploy Routines wizard opens. On the Select Connection page, ensure that the Use current database radio button is selected in the Target database frame.

If 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:

In 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 that’s bound to a different data server (such as a development database) compared to the production database.

If you didn’t 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.

11.   You 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.

12.   Select the Treat duplicates as errors radio button in the Duplicate handling box.

You 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.

You 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 database.

13.   Select 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 you’ve built on the database instead of doing a full build. If the routine’s binaries are not available, IBM Data Studio will do a full build of the routine before deploying it. To simply deploy the routine’s source to a database, select Deploy source to the database.

14.  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.

15.  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 didn’t appear in the database because it wasn’t 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 don’t 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

In this 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, I’ll show you how to build a stored procedure from scratch and dive deeper into some of the options I didn’t cover in this article.