 | Level: Intermediate Vasantha Jayakumar (vasantha@us.ibm.com), Advisory Software Engineer, IBM
16 May 2007 Using the IBM® Database Add-ins for Visual Studio 2005, you can now avoid the manual steps that have previously been required to deploy a stored procedure on a second target server. In addition, you can also easily view, create, and drop packages for stored procedures. Each package allows you to run SQL statements in stored procedures with different behaviors, and allows you to save these options for future use.
Introduction
Deployment: Typically DB2® for z/OS® users can deploy stored procedures on to a different target server either using a full build or by using some manual steps to deploy using just the stored procedure binaries. A full build of the stored procedure requires the source of the procedure for deploying on the second database, and a rebuild of the stored procedure is required. With binary deployment, the source is not required nor is a rebuild of the stored procedure. If no binaries are available, then a full rebuild is done.
Deploying stored procedures using the full build option, typically involves the following:
- Prepare the stored procedure data definition language (DDL) to be created on the target system. This involves, copying and modifying the SQL stored procedure DDL for changes due to SCHEMA name, collection ID, and Workload Manager (WLM) application environment.
- Defining the stored procedure on the target system by executing the modified DDL on the target system to catalog the SQL stored procedure.
- Granting execute authority on the stored procedure, as necessary.
Users can now avoid these manual steps by using the deployment option available in the IBM Database Add-ins for Visual Studio 2005.
Package variations: Users of DB2 for z/OS systems find it particularly useful to be able to run SQL statements in stored procedures with different behaviors and to save these options so they can be re-run. To serve this purpose, several packages, each specifying different bind options, can be created for a stored procedure. IBM Database Add-ins for Visual Studio 2005 allows creating and dropping packages as well as listing existing packages and viewing their properties. The details on creating and working with package variations are described later in this article.
Prerequisites
This article assumes that you are accessing DB2 for z/OS V9.1.
You'll need the IBM Database Add-Ins for Visual Studio 2005. Get the Add-Ins in one of the following ways:
For an introduction to the IBM Database Add-Ins for Visual Studio 2005, see the Resources section.
Deployment of SQL stored procedures
The IBM Database Add-ins for Visual Studio 2005 makes it easy to deploy stored procedures using the full build option and allows specifying the target DB2 for z/OS server for deployment. Deployment of native stored procedures is only supported in cases where both the source and target servers are DB2 for z/OS Version 9.1. To deploy stored procedures, you can right-click on the procedure node on the Server Explorer tree and choose the Deploy menu option, as shown in Figure 1.
Figure 1. Deploy menu in Server Explorer
Choosing the Deploy menu invokes the Deploy Routine screen that allows selecting the location of the target server. The target location can be:
- A connection that already exists in the Server Explorer. The drop-down menu for the target location lists the available connections in the Server Explorer.
- To create a new connection from the drop-down options for the target location, choose the New Connection option, which takes you through the steps to add a new connection after which you can continue working on the Deploy Routine screen.
The schema name to be used for the target connection can also be optionally specified. The bind options are also displayed in the deploy dialog.
Figure 2. Deploy routine screen
Once the target location is selected, click OK in the deploy dialog causes the selected stored procedure to be created and deployed on the target system. To deploy the SQL stored procedure, the IBM Database Add-ins modifies the SQL stored procedure DDL for necessary changes to SCHEMA name, collection ID, and WLM application environment. The modified stored procedure DDL is then copied to the target server location and executed to create the stored procedure on the target system.
One of the options to verify successful deployment of the stored procedure is to view the list of procedures in the target connection in the Server Explorer tree and make sure that the stored procedure has been created and deployed. The stored procedure can now be accessed and run on the target system similar to accessing it on the source system.
Package variations for SQL stored procedures
Several packages, with each specifying different bind options, can be created and saved for future use for a DB2 for z/OS SQL stored procedure and is referred to as package variations. This feature allows users to be able to run stored procedures with different bind options and to save these options so they can be re-run. IBM Database Add-ins for Visual Studio 2005 has the following package variation capabilities:
- Allows users to view the list of existing packages for a stored procedure and also view several properties for each of the packages.
- Allows creation of package variations for stored procedures.
- Allows dropping of existing packages.
Listing packages and viewing its properties
To view the list of existing packages for a SQL stored procedure, right-click on the stored procedure node in the Server Explorer tree, and choose Define Packages. Figure 3 shows a screen shot of the menu option in the Server Explorer.
Figure 3. Define Packages menu in Server Explorer
Choosing the Define Packages menu invokes the Packages screen, as shown in Figure 4. The left side of the screen contains a list box that lists the existing packages. The Package Properties grid on the right side of the screen, displays the corresponding package properties for the selected package in the list. Selecting a package in the list refreshes the properties grid to display the properties for the selected package. Note that for existing packages, several properties are disabled indicating that these properties are not applicable for existing packages. The package properties listed for existing packages are as follows:
- Bind Time
- Created By
- Server Bind Options
- Collection ID of the Source
- Package ID of the Source
- Version ID of the Source
Figure 4. Packages screen
Create packages
Packages can be created using the same Packages screen shown in Figure 4. To add a package, click + (the plus icon). Clicking this creates a new row entry under Packages List and displays a default package name that you can edit. Note that for newly added packages, several properties are disabled, indicating that these properties are not applicable for new packages. The following are some of the package properties that can be defined for a new package:
-
CollectionID: For the new package on the target system. The drop-down menu lists the collection ID on the target system to choose from.
-
BindOptions: For the new package. For a more user-friendly option to edit the bind options, select ... (the Ellipses) next to this property to display a Bind Options window that can be used to define the bind options.
-
Target location: For the new package. The target location contains a drop-down list for the existing connection, and additionally allows a new connection to be created and set as the target location. To create a new connection, the user can choose the New Connection option from the drop-down list that allows the user to add a new connection using the Add Connection screen.
Figure 5 shows a screen shot of the Packages screen when you choose to add a new package. Notice the list of enabled properties in the package properties window.
Figure 5. Add a new package
Multiple packages can be created at the same time. Once you have added the new package to the list in the Packages screen, and have completed editing the respective package properties, clicking OK causes the changes to be committed to the server and the new packages are created on the server.
Drop packages
The Packages screen is not only used to list and create packages, but also to drop existing packages. To drop an existing package for a stored procedure, invoke the Packages screen by choosing the Define Packages menu option. Then, on the Packages screen, select the package to be deleted from the list, and click - (the minus icon). Note that clicking the minus icon removes the selected package from the list of packages on the screen, but the changes are committed to the server only once you click OK.
In general, you can perform several add or delete package operations using the Packages screen, and these changes are executed on the database only when you click OK. If you click Cancel on the Packages screen, then all changes made on this screen are cancelled and no changes are committed to the server.
Summary
Automatic deployment and managing package variations of SQL stored procedures are very desirable features for DB2 for z/OS users, and the IBM Database Add-ins for Visual Studio 2005 has made it very easy for users interested in exploiting these features. It provides a one-click deployment option that helps you avoid several manual steps that are typically required to deploy stored procedures. You can easily view, create, and drop packages for stored procedures, and each package allows specifying and saving different bind options for the procedure.
Resources Learn
-
"Creating and working with DB2 for z/OS stored procedures, Part 1: Use IBM Database Add-Ins for Visual Studio 2005 to create, drop and add multiple versions of a native procedure on z/OS, Version 9.1" (developerWorks, March 2007): Learn how to use IBM Database Add-Ins for Visual Studio 2005 to create a native procedure, add versions, activate different versions, and more.
-
"Creating and working with DB2 for z/OS stored procedures, Part 2: Alter DB2 for z/OS V9.1 stored procedures using IBM Database Add-ins for Visual Studio 2005" (developerWorks, March 2007): Learn how to alter IBM DB2 for z/OS stored procedures using non-destructive alteration techniques now available in the Procedure Designer, part of the IBM Database Add-ins for Visual Studio 2005.
-
Develop proof-of-concept .NET applications series: Create proof-of-concept applications to access relational and XML data in IBM DB2 9, using Microsoft Visual Studio .NET 2005.
-
IBM Information Management and Visual Studio .NET: Find more resources for DB2 UDB .Net developers.
-
DB2 UDB for .Net: Read more information on the IBM Database add-ins for .NET
- "Application development with DB2 UDB" (developerWorks, June 2005): Developing applications for DB2 UDB using .Net, Java, SOA or open source such as PHP.
- Stay current with developerWorks technical events and Webcasts.
-
developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
Get products and technologies
Discuss
About the author | 
| |
Vasantha Jayakumar is an advisory software engineer at the IBM Silicon Valley Lab in San Jose. She has worked on several data management tools and currently works on developing tools for the various IBM servers on Visual Studio .NET. |
Rate this page
| |
