Procedures Wizard (DB2 Z/OS)

From DBArtisan
Jump to: navigation, search

Go Up to IBM DB2 for z/OS Object Wizards

Procedures are a reusable block of PL/SQL, stored in the database, that applications can call. Procedures streamline code development, debugging, and maintenance by being reusable. Procedures enhance database security by letting you write procedures granting users execution privileges to tables rather than letting them access tables directly.

To create a new procedure using a wizard:

  1. Open a creation wizard for a procedure. For details, see Opening an Object Wizard.
  2. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
  3. Finally, use the Execute button to create the object.

Important Notes

  • If you are creating a SQL routine procedure, you must have the latest DB2 fixpack installed on your OS/390 Client. If you do not have the latest fixpack installed, the SQL routine procedure creation will fail.

Procedures (DB2 z/OS) - Properties

When creating or editing a procedure, this tab/panel lets you work with the following settings:

Setting Description

Schema, Name, and Specific Name

Select the schema that is to own the procedure, provide a name for the procedure, and provide the Specific Name for the procedure.


The database manager will call the procedure accordingly assuming the program is designed to run in the server’s environment. Assemble: a stored procedure written in Assembler C: a stored procedure written in C or C++ COBOL: a stored procedure written in COBOL CompJAVA: CompJAVA is no longer supported. Stored procedures should alternatively be written in JAVA. JAVA PLI: A stored procedure written in PL/I. REXX (Restructured Extended Executor Language) - Don’t use this language when SQL parameter style is in effect. To specify REXX, the general parameter style or general with nulls. SQL

SQL Access Level

MODIFIES SQL DATA (Default): The procedure can support any SQL statement except those that cannot be supported in procedures. CONTAINS SQL DATA: Only SQL statements that neither modify nor read SQL data can be executed in the procedure. READS SQL DATA Some SQL statements that don’t modify SQL data can be included in the procedure NO SQL: Only SQL statements with a data access classification of NO SQL can be executed. Don’t select this option for a JAVA procedure that uses a .jar.

WLM Environment

Specify a Workload Management Environment (Required if Language is JAVA/COMPJAVA/REXX, the Procedure contains a LOB parameter, or Security is set to ‘USER’ or ‘DEFINER’).

WLM For Nested


Procedures (DB2 z/OS) - Parameters

For each parameter for this function, use the New button to add a new parameter, provide a name for the parameter, and in the Attributes area, select a Type, specify a Parameter Mode of INPUT, OUTPUT, or INPUT_OUTPUT, and if appropriate, the Precision, Scale, and Size options.