Introduction

Prior to DB2 for z/OS V9.1, SQL procedures were all external. The body was written in SQL, and DB2 supported these procedures by generating and associating a C program for each procedure. Now, DB2 for z/OS V9.1 introduces support for creating native SQL procedures. The body of a native SQL procedure is written in SQL, but DB2 does not generate an associated C program for native procedures.

Native SQL statements usually provide better performance and support more functions than external SQL statements. Additionally, DB2 permits you to manage several versions of a native procedure. Hence several versions of native procedure can exist while only one version can be made the active version at any given time. This article describes in detail how to create a native procedure, add versions, and activate different version, using the IBM Database Add-Ins for Visual Studio 2005.

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:

• Download the DB2 Client.
• Download DB2 9 for Linux, UNIX®, and Windows® and apply Fix Pack 2 (FP2).

For an introduction to the IBM Database Add-Ins for Visual Studio 2005, please see the Resources section of this article.

Create native procedures in z/OS V9.1

To create a connection to a z/OS server, use the Add Connection option in the Server Explorer. When creating the connection, you can choose to specify a schema filter to refine the objects that will be visible in your connection. After creating a connection to the z/OS V9.1 server in the Server Explorer, choose the Procedures node in the tree and invoke the IBM Procedure Designer using the Add New Procedure with Designer Context menu option as shown in Figure 1:

While the designer allows creation of both external as well as native procedures, the default procedure type is native. To create external procedures, change the selection in the procedure type combo box to External. The z/OS options section applies only for external procedures, and therefore is enabled for external procedures only. It's disabled and automatically collapsed for native procedures. Figure 2 shows the various z/OS options. When a procedure is created for the first time, the default version ID used is V1, but the user can edit and change this ID in the version ID field. Various procedure attributes such as the debug mode for the procedure, the number of result sets, ASU time limit, and the Workload Manager (WLM) environment can be specified using the fields in the procedure definition section of the designer:

You can add parameters for the procedure using the Procedure Parameters section. You can also choose to import parameters using the Import Parameters button, available in this section. You can edit the SQL body of the procedure using the SQL body section. To insert template code to create additional cursors with return, use the Context menu Insert SQL > Cursor with return options. This inserts the template code at the current cursor location.

Before saving the procedure, you can view the CREATE PROCEDURE Data Definition Language (DDL) that will be executed on the server once the procedure is saved. To see the CREATE PROCEDURE DDL, switch to the Show Script view of the designer. The Show Script allows a script header and a script trailer to be added. The SQL in the script header is executed prior to the script execution, and the SQL in the script trailer is executed after the script has been executed. Figure 4 shows the Show Script view and contains an example of a script that is generated for a native procedure.

To create the procedure on the database, choose the Save button or use the Visual Studio IDE menu. A status message on the execution operation is added to the Output View of the integrated development environment (IDE). On successful completion of the script execution, a node for the newly created procedure is added under the Procedures node in the Server Explorer.

Create external procedures in z/OS

To create external procedures using the IBM Procedure designer, change the selection in the procedure type combo box to External. The z/OS options section applies only for external procedures, and therefore is only enabled for external procedures. It is disabled and automatically collapsed for native procedures. Figure 4.1 shows the various z/OS options, which include the collection ID, external name, build options and runtime options:

Create multiple versions of a native procedure

You can create and manage multiple versions of a native procedure, but only one procedure is considered the active version at any point. When a procedure is first created, the initial version is considered the active version. You can create and maintain several versions of a procedure concurrently, so that you can create a new version of a procedure while the original one still exists. You can create and test new versions of a native procedure, and it's easy to make a newer versions the active version. One advantage of this setup is that if you encounter problems with a newer version, you can always fall back to a previous version of a procedure by activating it. You can also delete any unwanted versions of a procedure.

To create a new version of an existing native procedure, select the native procedure in the Server Explorer tree and choose the Add New Version menu option as shown in Figure 5:

When creating a new version of a native SQL procedure, you can modify the procedure body, procedure options and parameter names while all the other values of the procedure remain identical to the original procedure. The number of parameters, parameter data types and parameter type (IN, OUT, INOUT) have to be the same as that of the original native procedure in order to add a new version for a native procedure. If a change to any of these is required, you can create a new native procedure instead of creating a new version.

When adding a new version of a native procedure, certain fields and sections in the IBM Procedure designer are disabled since newer versions cannot differ in the values for these fields. An example of this is shown in Figure 6:

Initially when a native procedure is created, the DDL that is stored in the catalog for the procedure is the CREATE PROCEDURE script. All additional versions of a native procedure are created using the ALTER PROCEDURE DDL, and thus the catalog stores an alter script for all additional versions. Figure 7 shows an example of the ALTER PROCEDURE script generated and displayed in the designer's Show Script view in order to add a new version of a native procedure.

The version of the procedure that is currently active is displayed as the Main Procedure node. Any additional versions of a native procedure that are created are displayed under the Versions node of the respective procedure in the Server Explorer tree. In Figure 8, the active version of the UPDATE_BALANCE procedure is version V1, which is represented by the UPDATE_BALANCE node. An additional version (V2) was created for this procedure and is represented by the V2 node under the Versions node.

Activate different versions of a native procedure

When multiple versions of a native procedure exist, any of the versions of the procedure can be made the active version by simply right-clicking on the specific version node and choosing the Activate Version menu option. In the example shown in Figure 9, version V2 of the procedure is chosen to be the active version. This is an example of the DDL executed in this case, when choosing this menu option:

ALTER PROCEDURE UPDATE_BALANCE
ACTIVATE VERSION V2;

Upon successful activation of the selected version of the native procedure, the nodes in the Server Explorer tree are automatically refreshed to reflect the changes in the active version. Now, the newly activated version (V2) is represented by the UPDATE_BALANCE node in the tree, and the previously active version (V1) is be represented by the node VI under the Versions node of the procedure, as shown in Figure 10:

Delete native procedures

Deleting a non-active version of a native procedure

To delete a specific version of a native procedure that is not the active version, right -click on the specific procedure version node in the tree and choose the Drop menu option. This causes the specific version of the procedure to be deleted from the database. Figure 11 shows a screenshot of the menu option used to delete a specific version of a native procedure:

When you want to delete a specific version of a native procedure, the following confirmation dialog is displayed, and the user can choose to either continue of cancel the deletion:

Deleting an active version of a native procedure

If only a single version of a native procedure exists, by default that version of the procedure is the active version. Deleting an active version of a procedure will cause the active version as well as all additional versions of the procedure to be deleted. Figure 12 shows a screenshot of the menu option used to delete the active version of a native procedure:

When deleting the active version, the following confirmation dialog is displayed, stating that deleting the active version will cause all the other versions to be dropped, and the user is given the opportunity to either continue or cancel the operation:

View DDL for native procedures

The CREATE DDL or the ALTER DDL (as applicable) can be viewed for the native procedures using the Show Script menu option available on the active procedure node, as well as additional procedure version nodes. The script displays the CREATE DDL for the first procedure that was created. For all additional versions created, the script displays the ALTER DDL that was used to create that specific version of the procedure.

Enable debugging for procedures

To enable debugging of a procedure, the procedure should be built on the server for debug. Debugging can be turned on and off easily using the Enable Debugging and the Disable Debugging menu options available on the procedure node as well as on the specific procedure node in the tree.

Run native procedures

You can run an active version of the native procedure by right-clicking on the active procedure node and choosing the Run menu option. By default, the active version of the procedure is used when running the procedure. To run a non-active version of the procedure, the active version would have to be temporarily overriden with the specific non-active version. In this case, you would run the procedure and then revert back the active version to point to the originally active procedure. The IBM Add-Ins handles all of this and simplifies this process. To run a non-active version of a procedure, the user simply right-clicks the specific version of the procedure and chooses the Run menu option.

Summary

The ability to create native procedures as well as create and manage multiple versions of a procedure is a very powerful feature introduced with z/OS Version 9.1. Native SQL statements usually provide better performance and support more functions than external SQL statements. Using IBM Database Add-Ins for Visual Studio 2005, you can use and exploit these newly introduced features with just a few simple steps, as detailed in this article. You can easily create native procedures, add and delete multiple versions of procedures, as well as activate and view DDL for the various versions. Debugging can also be easily turned on and off for procedures. Working with native procedures for z/OS Version 9.1 has become very easy with the IBM Database Add-Ins for Visual Studio 2005.

Resources

Learn

• Read "What's new with the IBM Database Add-Ins for Visual Studio 2005" (developerWorks, May 2006): Get an introduction to the Add-Ins discussed in this article.
  
  
• "Learn to validate XML data using IBM Database Add-Ins for Visual Studio 2005" (developerWorks, November 2006) will help you grow your skills in .NET programming to access XML data.
  
  
• Read the developerWorks tutorial series "Develop proof-of-concept .NET applications" to grow your skills on using DB2 with .NET.
  
  
• Find more resources for DB2 .NET developers at the IBM Information Management and Visual Studio .NET page on developerWorks.
  
  
• Read more information on the IBM Database Add-Ins for .NET at the DB2 for .NET page.
  
  
• Learn more about DB2 for z/OS at the developerWorks DB2 for z/OS resource page.
  
  
• Get more details on DB2 Version 9.1 for z/OS.
  
  
• Read e-books on "Application development with DB2" (developerWorks, June 2005). These e-books provide a foundation for developing applications for DB2 using .NET, Java, and open source tools such as PHP.
  
  
• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Get the latest IBM Database Add-Ins for Visual Studio 2005 from when you download the DB2 Client from the Application development with DB2 page.
  
  
• Download a free trial version of DB2 Enterprise 9.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Connect to the community with developerWorks Information Management blogs.
  
  

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.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Prerequisites
• Create native procedures in z/OS V9.1
• Create external procedures in z/OS
• Create multiple versions of a native procedure
• Activate different versions of a native procedure
• Delete native procedures
• View DDL for native procedures
• Enable debugging for procedures
• Run native procedures
• Summary
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

New Offerings

Speed innovation with an affordable, dynamic infrastructure for software delivery

Special offers