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

A non-destructive method for altering native and external procedures

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. You can use the Procedure Designer to alter both external and native SQL procedures, as well as creating and managing several versions of native procedures, giving you an easy way to create, update, and manage your DB2 for z/OS stored procedures.

Share:

Vasantha Jayakumar (vasantha@us.ibm.com), Software Developer, IBM, Software Group

Vasantha Jayakumar photoVasantha Jayakumar is an advisory software engineer at the IBM Silicon Valley Lab in San Jose. Her expertise and focus has been on data management and application development tools for various IBM servers including DB2 LUW, z/OS, iSeries and IDS with special focus on the Microsoft .NET platform.



22 March 2007

Introduction

DB2 stored procedures created on a z/OS server typically have associated build options, WLM environment settings, compile options, and privileges, in addition to the SQL procedure definition. If a user chooses to drop and re-create a procedure in order to change its definition, the process would destroy peripheral definitions such as privilege grants, static references, and so on. It would also cause the original source in the catalog tables to be altered.

For these reasons, the drop and re-create method of alteration is not desirable in many user situations. A non-destructive alter mechanism is the preferred way to alter procedures. The ALTER PROCEDURE statement is a non-destructive mechanism and can be used to perform the change.

The Procedure Designer feature of the IBM Database Add-ins for Visual Studio 2005 has been enhanced to allow non-destructive alteration of both external as well as native SQL procedures. It also allows creation and management of several versions of native procedures. This article discusses the various alterations you can make to procedures, including procedure options, procedure parameters, and the procedure body, as well as illustrating the SQL scripts that are generated by the designer for these alteration scenarios.

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, please see the Resources section of this article.

Creating a connection to a z/OS database

To work with database objects in a DB2 for z/OS database, you can use the IBM Database Add-ins for Visual Studio 2005.

Open the Server Explorer window and choose the Add Connection menu option. In the Add Connection dialog, specify the server name, database name, and user authentication information to create the connection. In addition, you can set several user options using this dialog, and can test the connection using the Test Connection button.

If your database is large, as many DB2 for z/OS databases are, you'll find the schema filter option very useful when designing applications. Specifying a schema filter will help filter and refine the objects that will be visible in your connection in the Server Explorer. You can modify the schema filter at any time using the Modify Connection menu option on the connection. Choosing a different schema filter will cause the objects in your tree to be refreshed and display only objects that belong to the specified schema.

Figure 1 shows the Add Connection dialog highlighting the schema filter option:

Figure 1. Add connection dialog and schema filter option
Add connection dialog and schema filter option

External and native procedures

DB2 for z/OS V9.1 introduces support for creating or altering native stored procedures in a single step. These native procedure now have more capabilities than external procedures. Prior to DB2 for z/OS V9.1, SQL procedures were what we refer to as external SQL procedures. DB2 supported these by generating and associating a C program for each procedure.

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. In addition, DB2 allows you to create and manage several versions of a native procedure. While several versions of native procedure can exist, only one version can be made the active version at any given time.

Altering procedures

To alter a procedure from within IBM Database Add-ins for Visual Studio 2005, right click on the procedure in the Server Explorer tree and choose Open Definition from the menu. This process launches the Procedure Designer with the procedure definition pre-filled in the designer. You can now make alterations to the procedure definition. After making your changes, clicking the Save button will cause the script to be executed on the database to perform the alteration.

Since different alterations to the procedure result in different types of scripts to be generated, it is extremely useful to preview the alter script before clicking on the Save button. To preview the alter script, simply click on the Show Script button to switch to the Show Script view that displays the alter script. In cases where the build utility is used to perform the procedure alteration, no script will be displayed in the Show Script view, since the values used for alteration are passed as parameters to the utility, and an alter procedure script does not exist.

Changing the signature of a external or native stored procedure

The signature of a procedure consists of the following:

  • Procedure name
  • Procedure schema
  • Number of parameters
  • Order of parameters
  • Data types of the parameters

The signature of a procedure cannot be altered using the ALTER PROCEDURE statement. Therefore, altering the signature of the procedure causes the procedure to be dropped and re-created. Hence the script generated will include a drop DDL for the procedure, followed by the create DDL. This applies to both external as well as native stored procedures.

Figure 2 shows an example of the script generated where the signature of the external procedure is changed by adding a new parameter to the procedure definition. Notice that the script generated contains a drop DDL, followed by a create DDL to drop and re-create the procedure.

Figure 2. Altering the signature of an external stored procedure
Altering the signature of an external stored procedure

Although changes to the signature of a procedure will cause a destructive type of alteration that involves dropping and re-creating the procedure, there are several other properties of a procedure that can be altered in a non-destructive manner using the ALTER PROCEDURE statement. The following sections describe the various alteration scenarios in detail for both external as well as native procedures.

Altering external procedures

External procedures are procedures that were created on any z/OS server prior to Version 9.1. Apart from the signature of the procedure, several z/OS options can be altered for an external procedure using a simple ALTER PROCEDURE statement. Alterations to the procedure body cannot be performed using ALTER PROCEDURE statement but can be done by using a build utility (DSNTPSMP) that is available on the z/OS server. So if the procedure body is changed in addition to changes to other z/OS options, the alteration will use the build utility to perform the alteration instead of direct ALTER PROCEDURE statements. Depending on the type of alteration performed, either an ALTER PROCEDURE statement will be generated or the build utility will be used to perform the alteration on the external procedure.

Altering only z/OS options for an external procedure

If the signature as well as the routine body of the procedure remain unchanged, while the values for the z/OS options were changed, then a simple ALTER PROCEDURE statement is generated to perform the procedure alteration. The following z/OS options can be altered using the ALTER PROCEDURE statement:

  • Collection ID
  • WLM Environment
  • Stay Resident
  • Runtime Options
  • External Security
  • External Name
  • Dynamic result sets
  • ASU time limit
  • Compile Options

As mentioned earlier, choosing the Open Definition option on a procedure will launch the Procedure Designer. The z/OS options section in the designer may be used to edit and change the values of specific z/OS options. Figure 3 shows a screen shot of the z/OS options section in the Procedure Designer for an external procedure.

Figure 3. z/OS Options Section in the Procedure Designer for an external procedure
z/OS Options Section in the Procedure Designer for an external procedure

The figures below show some examples of using ALTER PROCEDURE statements to alter these z/OS options. These options can either be altered individually, or several options can be combined and altered together.

Figure 4 shows the alter script generated when the values for the WLM Environment and the External name were changed.

Figure 4. Altering WLM Environment and external name
Altering WLM Environment and external name

Figure 5 shows the alter script generated when the values for several z/OS options have been changed.

Figure 5. Altering several z/OS options
Altering several z/OS options

Altering the procedure body of an external procedure

The ALTER PROCEDURE statement available for altering external procedures on z/OS does not allow the procedure body of a procedure to be altered. So to perform alterations to the procedure body, you use a build utility (DSNTPSMP) that is available on the z/OS server. This build utility also allows alteration of certain z/OS options.

If the signature of an external procedure is unchanged, while the user changes the routine body and optionally changes any z/OS options, then the build utility will be called to perform the alteration. Any changes to the signature of the procedure will cause the script generated to contain the drop DDL followed by the create DDL for the procedure.

Note that in cases where the build utility is used to perform the procedure alteration, no script will be displayed in the Show Script view since the values used for altering the procedure are passed as parameters to the utility and an alter procedure script does not exist.

In Figure 6, the SQL body of the procedure was altered, and in addition the values for external name and collection ID were changed. Since changes to the SQL body are handled by the build utility, you do not see it directly reflected in the generated ALTER PROCEDURE statement.

Figure 6. Altering the routine body as well as some z/OS options of an external procedure.
Altering the routine body as well as some z/OS options of an external procedure.

Altering the debug mode of an external procedure

Alteration of the debug mode for external procedures cannot be performed using the ALTER PROCEDURE statement, and hence the build utility(DSNTPSMP) is used to change the debug mode. As in the case of changes to the SQL body of an external procedure, no script will be displayed in the Show Script view when changing the debug mode since the alteration is handled by the build utility.

Altering native procedures

Creating native SQL procedures has been introduced in z/OS Version 9.1 and usually provides better performance and support more functions than external SQL statements. z/OS Version 9 allows creating and managing several versions of a native procedure. While several versions of a native procedure can exist only one version can be made the active version at any given time.

For more information on creating and working with native stored procedures, refer to the Part 1 article in this series. Create, drop and add multiple versions of a native procedure on DB2 for z/OS V9.1 using IBM Database Add-ins for Visual Studio 2005.

As noted in an earlier section, changing of the signature of a native procedure will cause the procedure to be dropped and re-created. While the signature of the native procedure cannot be altered using a non destructive mechanism, several other attributes of a native procedure definition can be changed using the ALTER PROCEDURE statement that includes support for working with several versions of a stored procedure.

For native procedures, several alterations can be performed either on the active version or on specific versions of the procedure. The below sections detail several alteration scenarios for native procedures and also contain screen shots of the generated script in each of these cases.

Altering a native procedure by adding a new version.

The first time a native SQL procedure is created in DB2 for z/OS V9, it is automatically assigned to be the active version of the procedure. If the create DDL does not contain an explicit version number, then the procedure created is considered to be the first version and assigned the default version number ‘V1’ by the server. When you create additional versions for the native procedure, you must specify a specific version number.

In IBM Database Add-ins for Visual Studio 2005, for native procedures, a specific procedure node in the Server Explorer represents the active version of the procedure. Any additional version of the native procedure are listed with their version numbers under the Versions node under the procedure, as you see in Figure 7. Here, the procedure node NATIVE_PROCEDURE in the tree represents the active version (V1) of the procedure, and the additional versions, V2 and V3, are listed under the Versions node. Also, the Add New Version menu option that allows you to add new versions is highlighted.

Figure 7. A native procedure and its version displayed in the Server Explorer
A native procedure and its version displayed in the Server Explorer

To alter native procedures, you can either choose to directly alter the actual procedure or you can choose to add a new version of a procedure without changing the existing procedure. There are several advantages to choosing the latter mode of alteration and adding new versions. Some of the advantages are:

  • You can create and test a new version of the procedure without making any changes to the existing procedure.
  • You can maintain different version of a procedure and easily switch the active version of a procedure, thus allowing the option to fall back to an older version of the procedure.

All versions of a native SQL procedure should contain the same number of parameters, while they can contain different values for the parameter names, procedure options, and procedure body.

To add a new version of native procedure, right click on the respective procedure node on the Server Explorer tree and choose Add New Version. This will launch the Procedure Designer with the definition of the active version pre-filled. Some of the properties that cannot be changed for a new version are automatically disabled. Figure 8 shows the alteration of a procedure by adding a new version where the new version has a different procedure body.

Figure 8. Altering the procedure body and adding a new version of a native SQL procedure
Altering the procedure body and adding a new version of a native SQL procedure

Altering an active version of a native procedure

To alter native procedures, in addition to creating new versions, the user can also choose to directly alter the active version of a native procedure. To alter the active version of the native procedure, right click on the active procedure node in the Server Explorer and choose Open Definition. The designer launched will contain the information for the active version of the procedure, and the user can choose to alter this information. Some of the fields will be disabled.

The screen shot in Figure 9 shows the alter script for a native SQL procedure where the options such as debug mode, number of result sets, WLM environment and ASU time limit have been changed in the active version of the procedure. Notice the ALTER ACTIVE VERSION clause generated in the alter DDL.

Figure 9. Altering only the z/OS options of an active version of a native SQL procedure
Altering only the z/OS options of an active version of a native SQL procedure

Changing the body of the active native procedure in addition to changing the values for some z/OS options, results in the replacement of the procedure definition of the active version. Notice the REPLACE ACTIVE VERSION clause generated in the alter DDL.

Figure 10. Altering the SQL body and options of an active version of a native SQL procedure
Altering the SQL body and options of an active version of a native SQL procedure

Altering a non-active version of a native procedure

In the previous section we discussed altering of the active version of a native procedure. Non-active versions of a native procedure can also be altered in a similar way. To alter a non-active version of a native procedure, in the Server Explorer, right click on the specific version node under the Versions node of the procedure and choose Open Definition. The designer launched will contain the information for the specific non-active version of the procedure, and the user can choose to alter that information. Some of the fields will be disabled.

The screen shot below shows the alter script for a native SQL procedure where the options such as debug mode, number of result sets, WLM environment and ASU time limit have been changed in a non-active version (V2) of the procedure. Notice the ALTER VERSION version_ID clause generated in the alter DDL.

Figure 11. Altering only the z/OS options of a non-active version of a native SQL procedure
Altering only the z/OS options of a non-active version of a native SQL procedure

Changing the body of the non-active native procedure in addition to changing the values for some z/OS options, results in the replacement of the procedure definition of that particular version. Notice the REPLACE VERSION version_ID clause generated in the alter DDL.

Figure 12. Altering the SQL body and options of a non-active version of a native SQL procedure
Altering the SQL body and options of a non-active version of a native SQL procedure

Altering the debug mode of a native SQL procedure

It the debug mode of a native procedure is changed, then the ALTER PROCEDURE statement generated will include a clause to change the debug mode. The screen shot below shows the alter script for a native SQL procedure where the debug mode of the active version of the procedure has been changed. Notice the ALTER ACTIVE VERSION clause in the script.

Figure13. Altering the debug-mode of an active version of a native SQL procedure
Altering the debug-mode of an active version of a native SQL procedure

Figure 14 shows the alter script for a native SQL procedure where the debug mode of a non-active version (V2) of the procedure has been changed. Notice the ALTER VERSION version_ID clause in the script.

Figure 14. Altering the debug-mode of a non-active version of a native SQL procedure
Altering the debug-mode of a non-active version of a native SQL procedure

Summary

IBM Database Add-ins for Visual Studio 2005 has added a much desired and powerful feature in the FixPack 2 release that allows the user to alter both external as well as native procedures, using a non-destructive alter mechanism, instead of dropping and re-creating the procedure. This approach will help alter the procedure while preserving several settings such as privileges, build options, WLM environment settings, and so on. Different alter scripts are generated based on the various alterations performed to the procedure. With the introduction of native procedures in z/OS Version 9.1, several options to alter a procedure are now available. The user can choose to alter native procedures by adding a new version, altering the active version or altering any specific non-active version. This article has described several procedure alteration scenarios for both native procedures as well as external procedures and highlighted the differences in the alter script that is generated for each of these scenarios.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=203723
ArticleTitle=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
publish-date=03222007