Procedures Wizard (DB2 LUW)

From DBArtisan
Jump to: navigation, search

Go Up to IBM DB2 for Linux, Unix, and Windows 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.

Procedures (DB2 LUW) - Properties

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

Setting Description

Schema

Select the owner for the procedure.

Name

Provide the name of the function.

Specific Name

Optionally, provide the unique name of the procedure.

Language

Select among C, JAVA, COBOL, OLE, or SQL. The database manager will call the procedure accordingly.

External Name

Provide the external name of the procedure.

SQL Access Level

Select an option: MODIFIES SQL DATA - 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.

Procedures (DB2 LUW) - Advanced

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

Setting Description

Results Sets

Indicate the estimated upper bound of returned result sets. 0 is the default.

External Action

Select the External Action option.

New Save Point

Lets you specify a NEW SAVEPOINT LEVEL clause for the procedure.

Threadsafe

Specify whether the procedure is safe to run within the same process as other routines.

Fenced

If you select yes, you are saying you do not want the procedure to run in the manager operating system environment. This means the database management system will protect its internal resources from the procedure. This option can affect the procedure’s operation.

To run a procedure as not fenced, or a No selection, you must have SYSADMIN or DBADMIN privileges because of the potential to compromise data if the procedure has not been adequately tested.

Parameter Style

Lets you select an option: DB2DARI, DB2GENERAL, DB2SQL, GENERAL, GENERAL WITH NULLS, JAVA, and SQL. DB2GENERAL is for Java Language only. DB2SQL is for C, COBOL, or OLE Language only. GENERAL is for C Language only. GENERAL WITH NULLS is for C or COBOL Language only. JAVA is for Java Language only. SQL is for C, COBOL, or OLE Language only.

Program Type

MAIN: valid for C or COBOL Language and Parameter Style GENERAL, GENERAL WITH NULLS, SQL, or DB2SQL only. In this case, parameters will be passed as an argument counter or argument vector. SUBROUTINE: the procedure expects the parameters to be passed as separate arguments.

Inherit Special Registers

Lets you specify this optional clause dictating that the procedure will inherit initial values from the environment of the invoking statement.

DBINFO

Specific information contains such information as the database name, application ID, database code page, and so on.

Deterministic

Enabling this feature specifies the procedure will always return the same result for given argument values. Disabling it means there are state values that affect the results and so the same result will not always be returned when you use identical inputs to invoke the procedure.

Parameter CCSID

Select an encoding scheme of ASCII, UNICODE, or NONE for character or graphic string parameters.

Procedures (DB2 LUW) - 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.