Creating a stored procedure

Authorities and privileges

To create a stored procedure:

1. Open the Create Procedure notebook.
2. Specify general properties for the procedure on the Properties page.
3. Specify workload manager and security properties on the Environment page.
4. Specify language options and parameters on the Parameters page .
5. Click Add... to open the Create Procedure Add window so thatr you can define at least one parameter. The Parameters text area shows the procedure's parameters. If no parameters are defined, then this area is blank.
6. Click OK.

Related information
Altering a procedure
Dropping a procedure
Displaying reports about procedures

Specifying general properties

1. In the Procedure schema field, specify a schema. The specified schema is used as the second part of the three-part name.

Tip: To display a list of existing objects, click the (Browse) button next to the field. Select an existing object from the list.

2. In the Procedure name field, type the name of the procedure. The procedure name can be up to 18 characters long and must not duplicate an existing stored procedure at the current server.
3. Optional: In the External name field, type a name that identifies the user-written code that implements the stored procedure.

The name must not be longer than 8 characters and must conform to the naming conventions for MVS load modules. If you do not specify a name, the procedure implicitly uses the procedure name as the external name. In this case the procedure name must not be longer than 8 characters.

4. Optional: In the Collection ID field, type the name of the package collection to use when the stored procedure is executed.

The collection ID can be up to 18 characters long. Leaving this field blank indicates that the package collection is the same as the package collection of the program that issued the SQL CALL statement.

5. From the SQL list, select one of the following choices:

Contains SQL

This is the default. The stored procedure can contain any SQL statements except for:

• COMMIT
• DELETE
• INSERT
• PREPARE INTO
• ROLLBACK
• SELECT
• UPDATE

No SQL

The stored procedure cannot contain any SQL statements

Reads SQL

The stored procedure can contain any SQL statements except for:

• COMMIT
• DELETE
• INSERT
• ROLLBACK
• UPDATE

Modifies SQL

The stored procedure can contain any SQL statements except for:

COMMIT

DELETE

INSERT

ROLLBACK

6. Optional: In the Result sets returned field, type the maximum number of query result sets that can be returned by this stored procedure.

This field accepts the small integer data type; the default value is zero. A value of zero indicates that no query results set is returned.

7. Optional: Select a program type to specify whether this stored procedure runs as a Main routine or a Subroutine.
8. If successive calls of the stored procedure with identical input arguments return the same results, select the Results are deterministic check box.
9. If the stored procedure requires specific information known by DB2, select the DB2 information is passed check box.

If you select this check box, an additional argument is passed when the stored procedure is invoked. For information about this argument, see the DB2 Application Programming and SQL Guide.
10. If you want the load module to remain resident in memory after the stored procedure ends, select the Stay resident at exit check box.
11. If you want the unit of work to be committed immediately upon the successful return from this stored procedure, select the Commit on return check box. (A non-negative SQL code signifies a successful return.)
12. Optional: If you want to limit the service units permitted for any single invocation of the stored procedure, select the Limit service units check box and type the number of service units in the field.

The field acceptsonly non-negative integers; the default value is zero. Zero indicates that there is no limit on the service units. If you set this field to a value other than zero and a stored procedure uses more service units than allowed by the setting, DB2 cancels the stored procedure.

13. Optional: In the Comment field, type a comment for this procedure.

Specifying workload manager and security properties

1. From the External security list select one of the follwoing choices:

DB2

The stored procedure does not require a special external security environment.

User

The stored procedure requires an external security environment, which is accessed using the authorization ID of the user who invoked the stored procedure.

Definer

The stored procedure requires an external security environment, which is accessed using the authorization ID of the user who createed the stored procedure.

2. Under Workload manager (WLM) environment, select a radio button:

No WLM environment

The stored procedure runs in the DB2-established address stored procedure space. Do not specify this option if:

• The stored procedure is a Subroutine.
• The stored procedure uses external security accessed though the user's or definer's authorization ID.
• The stored procedure uses a parameter of the LOB data type or a distinct type based on a LOB data type.

Use the default WLM environment

The stored procedure runs in the default WLM-established address stored procedure space, which is specified at during installation.

Use the following WLM environment

The stored procedure runs in the specified address stored procedure space. If you select this options you must specify a Name for the environment and specify whether nested procedures use:

A different address space

The same address space

The Name is a long identifier and must not contain an underscore.

Specifying language options and parameters

1. From the Language list, select the programming language used to create the stored procedure. You can choose ASSEMBLE, PL1, COBOL, or C.
2. If you don't want to use default the language environment run-time options, type options in the Run-time options field. You can type up to 256 characters.

Example:

MSGFILE(OUTFILE),RPTSTG(ON),RPTOPTS(ON)

3. From the Parameter style list select one of the following choices:

DB2SQL

In addition to the parameters on the CALL statement, the following arguments are also passed to the stored procedure:

• A null indicator for each parameter on the CALL statement.
• The SQLSTATE to be returned to DB2.
• The qualified name of the stored procedure.
• The specific name of the stored procedure. (The specific name is the same as the qualified name.)
• The SQL diagnostic string to be returned to DB2.

General

Only the parameter on the CALL statement are passed to the stored procedure. The parameters cannot be null.

General with nulls

In addition to the parameters on the CALL statement, another argument is also passed to the stored procedure. This argument enables the stored procedure to accept or return null parameter values.

Changing parameter properties

1. Select the parameter that you want to change.
2. Click the Change push button.
3. Use the Create Procedure Change window to change the following properties:
Parameter type
Parameter characteristics
Parameter name