Procedures Wizard (DB2 LUW)
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:
- Open a creation wizard for a procedure. For details, see Opening an Object Wizard.
- Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
- Properties panel - for details, see Procedures (DB2 LUW) - Properties.
- Advanced panel - for details, see Procedures (DB2 LUW) - Advanced.
- Parameters panel - for details, see Procedures (DB2 LUW) - Parameters.
- DDL View panel - for details, see Previewing the DDL Generated to Create the New Object.
- 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.