Procedure, Designer

Procedure Identification

In this section, you define the procedure.

Procedure name

Type a name for the new procedure. Use quotation marks for delimited names. If you do not enter a value, the designer generates a default value. The name cannot exceed 128 characters.

Schema name

By default, the value for this field is the name of the schema that is obtained from the current data connection. You can change the value of this field to another schema, or leave it blank and the designer uses the default value. Use quotation marks for delimited names.

Specific name  (DB2^® for Linux^®, UNIX^®, and Windows^®)

Type a specific name for the new procedure. This is a unique name that the server uses to identify the procedure. This field automatically fills in as you type in the Procedure name field. If you do not specify a unique name, the designer generates one.

Procedure type  (DB2 for z/OS^®)

Select whether the procedure is native or external. You can specify a procedure as native only for a database on a DB2 for z/OS, Version 9.1 or Version 10 server.

Version  (DB2 for z/OS, Version 9.1 or Version 10)

For a native procedure, specify the version number of the procedure.

Comment

Type a comment for the new procedure. Comments that you type for the procedure are appended after the CREATE PROCEDURE declaration in the generated procedure.

Debug mode

Specifies whether you want to be able to debug the SQL procedure. The choice that you select will not apply until after you have saved the procedure. Select one of the following choices:

• ALLOW: Procedure is built with debugging support and debugging is allowed.
• DISALLOW: Procedure is built with debugging support, but debugging is not allowed. With this choice you can change to the ALLOW debug mode without dropping and re-creating the procedure. DISALLOW is the default selection.
• DISABLE [DB2 for z/OS, Version 9 new function mode (NFM), Version 10 in Version 9 conversion mode (CM), and Version 10 NFM]: Procedure is built without debugging support. With this choice you cannot change to either ALLOW or DISALLOW debug mode with an ALTER statement. To change this option, you must drop and re-create the procedure.

Number of result sets

Specify the estimated upper bound of returned result sets for the procedure.

WLM environment

Specify the name of the MVS workload manager (WLM) environment in which to run the procedure.

ASU time limit

Specify the total amount of processor time, in CPU service units, that a single invocation of the procedure can run.

Owner

Specify the user ID of the owner of the procedure. The owner must be the same ID that qualifies the procedure name of the UDR that you create.

Specific name

Type a specific name to declare an identifier for a UDR that is unique in the database or name space. A database can have more than one UDR with the same name and different parameter lists. You can assign a specific name that uniquely identifies the specific UDR.

Comment

Type your comment such as a description of this procedure. The comments are applied to the DOCUMENT clause. Anyone with access to the database can query the sysprocbody system catalog table to obtain a description of one or all of the UDRs that are stored in the database.

Debug mode

Specifies whether you want to be able to debug the SQL in the SPL procedure. The choice that you select will not apply until after you have saved the procedure. Select one of the following two choices:

• ALLOW: Procedure is built with debugging support and debugging is allowed.
• DISALLOW: Procedure is built with debugging support, but debugging is not allowed. With this choice you can change to the ALLOW debug mode without dropping and recreating the procedure. DISALLOW is the default selection.

Routine modifier

Specifies the routine modifier to apply to this UDR. Routine modifiers tell the database server about attributes of the UDR. Select one of the following choices:

• VARIANT: Can return different results when invoked with the same arguments.
• NOT VARIANT: Might return cached results.

REFERENCING

Type the REFERENCING clause in this field, or click , and declare the information for the clause in the Declare REFERENCING Clause window.

Listing file name

Specify a full directory path and file name where compile time warnings are sent. After you compile a UDR, the listing file contains warning messages that were generated. The file name that you specify is sent with the WITH LISTING IN clause.

z/OS Options  (DB2 for z/OS)

In this section, you specify values for options that are specific to SQL procedures that are stored in databases on z/OS servers. If the value of Procedure type in the Procedure Identification section is Native, the options in this section are unavailable.

Select an option to see its description in the box under the options list. If the description exceeds the size of the box, hover the mouse pointer over the last line of text in the box. After a brief pause, the full description is shown in a ScreenTip.

Procedure Parameters

In this section, you define the parameters for the SQL or SPL procedure.

Parameters

Specify the parameters in the order that they appear in the generated code. You can use the arrow buttons to move a selected parameter up or down in the list.

Name

Type a name for the parameter. A parameter requires a unique identifier. No duplicate parameter names are allowed in a procedure. Maximum length of a parameter name is 18.

Mode

Select the mode for the parameter. The default value for the mode is IN. You can select IN (indicating that the parameter is an input only parameter), OUT (indicating that the parameter is an output only parameter), or INOUT (indicating that the parameter is both an input and output parameter).

Type

Select the data type for the parameter. The default data type is VARCHAR with a length of 256 characters.
If you selected a data type of DECIMAL, type the scale value for the parameter in Scale property in the Parameter Properties list.

(DB2 Version 9.7 for Linux, UNIX, and Windows, or later; DB2 for z/OS)
Optional: If you selected a data type of TIMESTAMP or TIMESTAMP WITH TIMEZONE [DB2 for z/OS Version 10 new function mode (NFM) only], type the precision value for the timestamp in the Precision property in the Parameter Properties list. The default value is 6 microseconds.

(DB2 Version 9.7 for Linux, UNIX, and Windows)
If you selected a data type of CURSOR, the mode of the parameter will be set to OUT. You cannot create a procedure with a IN or INOUT mode CURSOR parameter.

Length

If you selected a data type of CHARACTER, VARCHAR, BLOB, CLOB, DBCLOB, GRAPHIC, or VARGRAPHIC, type the length (byte count) of the parameter.
If you selected a data type of DECIMAL or FLOAT, type the precision of the parameter.

Default Value

Specify the value to use when you run the procedure with the Default Value parameter selected. You must enclose values of CHAR, VARCHAR, DATE, TIME, and TIMESTAMP types in single quotes.

Opens the Import window so that you can import columns from one or more tables into the procedure as parameters. The data type of each imported parameter is the same as the data type of the column that it is associated with.

Parameter properties

Set the property values for the parameter that is selected in the Parameters list. An unavailable property is a limitation of the database that you are using.

Select a property to see its description in the box under the properties list. If the description exceeds the size of the box, hover the mouse pointer over the last line of text in the box. After a brief pause, the full description is shown in a ScreenTip.

(DB2 Version 9.7 for Linux, UNIX, and Windows, or later)

To specify an array parameter, in the value cell of the Array Type property, click to open the Array Types window. In the window, select the array data type that you want to use for the parameter.

If you specify an array parameter, the value cell of each of the following properties is unavailable because the property inherits its value from the selected array data type.

• Array Elements
• Length
• Mode
• Precision
• Scale
• Type

You can specify the data type of a parameter in three different ways:

• Specify the SQL data type directly
• Specify a LIKE clause
• Use a REFERENCES clause

Use the Data Type Style property to indicate how you want to specify the data type, and then specify values for the corresponding properties. The properties that are available depend on the value that you select for this property.

SQL Body

In this section, enter an SQL or SPL statement or expression in the editor.

For examples of SQL code for a procedure, see DB2 SQL Procedures and CREATE PROCEDURE Sample SQL.

The designer allows only SQL SELECT statements, and it checks for correct syntax. You can include multiple SELECT statements in your procedure definition. Click at the beginning of the  END P1  line, press Enter, right-click on the new blank line, select Insert SQL on the shortcut menu, and then select CURSOR (with return).

Additional information is available in your DB2 documentation. You can also find information in DB2 SQL documentation that is available through The SQL Reference for Cross-Platform Development Web page on developerWorks.

To use the Visual Studio Query Builder to create the SELECT statement, right-click in this editor, and then select Query Builder on the shortcut menu. When you close the Query Builder, the SQL code in the Query Builder replaces all of the existing code in this editor.

Uncomment and complete one or more of the following SPL clauses that is shown in the editor:

DEFINE

Use to declare local variables that an SPL routine uses, or to declare global variables that several SPL routines can share.

ON EXCEPTION

Use to specify the actions to be taken for any error, or for a list or one or more specified errors, during execution of a statement block.

EXECUTE FUNCTION

Use to run a user-defined function.

EXECUTE PROCEDURE

Use to invoke a user-defined procedure.

You can include any of the following SPL statements in the statement block of your SPL procedure:

CALL CONTINUE EXIT

FOR FOREACH IF

LET RAISE EXCEPTION RETURN

SYSTEM TRACE WHILE