Isobel Gascon

This article covers the basics of using Db2 Developer Extension to create a simple stored procedure and then explains some of the more complex things you might need to do when you create a stored procedure, such as working with DBCS data and user-defined types.

Stored procedures are a powerful tool for increasing the performance and efficiency of distributed applications. Currently, Db2 Developer Extension supports Db2 for z/OS native stored procedures, which are stored procedures that are written entirely in SQL and are created by using the CREATE PROCEDURE or CREATE  OR REPLACE PROCEDURE statement, which is available with Db2 12 function level 507 and later.

The snippet includes some of the more commonly used parameters and an example SELECT statement. The SELECT statement is the part of the stored procedure that gets executed when the stored procedure is called. To see all of the options that you can define in a stored procedure, open the link that is included at the top of the snippet.

Now this stored procedure is ready to be deployed. To learn how, see Deploying, running, and debugging stored procedures.

Now let’s look at some more complex examples.

Languages such as Chinese and Japanese contain double-byte characters, which are characters that are too large to represent with a single byte.  If you write SQL applications that support double-byte character languages, you need to know a bit about how Db2 for z/OS supports DBCS data and how character translation is handled.  This section describes how to code DBCS data in a Db2 for z/OS native stored procedure.

Db2 for z/OS provides DBCS support for the following data types:

Tip: Db2 translates all Db2 character data between the internal Db2 table Code Character Set Identifier (CCSID) and the external application CCSID.

The following example shows a user-defined type stored procedure that includes a double-byte DBCLOB variable. When you create a stored procedure that includes DBCLOB parameters, you need to specify PARAMETER CCSID UNICODE, and if the stored procedure includes DBCLOBs in the body, you need to specify APPLICATION ENCODING SCHEMA UNICODE.

Setting this input returns the following output:

You can create and use a custom data type that’s called a user-defined distinct type in Db2 for z/OS. For example, let’s say that you keep email documents that are sent to your company in a Db2 table. The Db2 data type of an email document is a CLOB, but you define it as a distinct type so that you can control the types of operations that are performed on the email data. The distinct type is defined like this:

Now you can define and write a native stored procedure or user-defined function to search for and return the following information about an email document:

The following example shows how to create a user-defined distinct type for a currency (US_DOLLAR), how to include it in a stored procedure, and how to do some calculations.

Note that the US_DOLLAR distinct type does not automatically inherit the functions and operators of its source type, DECIMAL. You can use casting functions to assign values other than host variables. Also, the DISTINCT keyword is optional.

Setting this input returns the following output:

An array value is a structure that contains an ordered collection of elements. Arrays make it easier to exchange long lists of values with the Db2 server. You can create a user-defined data type for an array in Db2 for z/OS.

The following example shows how to create an ordinary array user-defined type (PHONENUMBERS) that can contain a maximum of 50 elements. The elements are of the DECIMAL(10,0) data type. The array index starts with 1.

Setting this input returns the following output:

The following example shows how to create an ordinary arrays user-defined type for time and timestamp with time zone. The default TIMESTAMP WITH TIME ZONE length is 6.

Setting this input returns the following output:

They wanted a university to help them develop “the talent of not offending,” “delicacy of thought,” “happiness of expression,” “taste and

10 Incredible Coastal Campsites in Washington · 1 Kayak Camp on Cypress Island · 2 Camp on Orcas Island · 3 Camp at First Beach · 4 Camp at Second Beach · 5

1. Go to Microsoft account security and select Password security.
2. As a security measure, you might be prompted to verify your identity with a security code.
3. Enter your current password, enter your new password, and then select Save.