What are db2 stored procedures?

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:

Good thing about Stored procedures are, when it gets compiled and called for the first time the execution plan gets stored in the database which gets used in further invocations of this SP. It is a data base Object which performs certain business logic so it is also considered as a program.

Once the Stored procedure is created, a row gets created in SYSROUTINES table. This table contains this SP details in columns like Specific Schema, Specific Name(Procedure name), Routine Type, Routine Body, SQL Data Access, Path & External Name Etc.

Where to Code? Can be coded just like any other cobol db2 program with the required SQL statements inside it.

Types: Can be a Native SP or External SP. Native Stored procedures are written in SQL language. External Stored procedures are written along with the host language like COBOL.

Creating External St. Procedures: Creating external stored procedures involves in defining/CREATING the SP to DB2 and coding of SP logic in Language defined. In our case it is COBOL.

Defining SP to DB2: This process gives a name, declares IN & OUT parameters, Sets the external Language etc.

Example:

Invoking Stored Procedures: In mainframes environment, A stored procedure can be invoked from an application program USING A CALL or it can be invoked from the Command Line.

Parameters can be supplied to the above SP if they are declared while creating SP.

Features:

A stored procedure is a compiled program that can execute SQL statements and is stored at a local or remote Db2 server. You can invoke a stored procedure from an application program or from the command line processor.

• EMP: employee number.
• PRJ: project number.
• ACT: activity ID.
• EMT: percent of employee's time required.
• EMS: date the activity starts.
• EME: date the activity is due to end.