Creating Models › Target Servers for Physical Models › Database Property Editors › DB2 z/OS Property Editors › Define DB2 z/OS Stored Procedures

Define DB2 z/OS Stored Procedures

Use the DB2 Stored Procedures editor to add, change, view, or delete a DB2 stored procedure for the model. As you define parameters, the Code tab of the editor is populated with SQL code, which you can review and edit.

To define a DB2 z/OS stored procedure

1. Right-click a Stored Procedure in the Model Explorer and select Properties.

   The DB2 Stored Procedure Editor opens.

2. Select a stored procedure and work with the following options:

   Note: Click New on the toolbar to create a stored procedure. Use the Enter filter text text box to filter a large list of names to locate the one you want to work with.

   Name

   Specifies the name of the stored procedure.

   Schema

   Specifies the default schema for the stored procedure.

   Type

   Specifies whether the stored procedure is Model Level or Table Level. Make a selection from the drop-down list.

   Procedure Type

   Indicates the procedure type: SQL Native, SQL External, or External. Make a selection from the drop-down list. Your choice affects the options displayed on the General tab below.

   Attach to New

   Specifies to attach the selected stored procedure to each new table that you create. Clear the check box if you want to manually attach the stored procedure to new tables.

   Generate

   Generates DDL for this object during Forward Engineering.

3. Work with the following options on the General tab (applies to all procedure types):

   For DB2 z/OS:

   Dynamic Result Sets

   Specifies the maximum number of query result sets that the stored procedure can return. Default: 0

   Language

   Specifies the application programming language in which the stored procedure is written. Valid values: Assemble, C, Cobol, Java, PLI, REXX, SQL.

   SQL Data Access

   Specifies the classification of SQL statements that the procedure can execute. Valid values: Modifies SQL Data, Reads SQL Data, Contains SQL, No SQL.

   Is Deterministic

   Indicates that the stored procedures always returns the same results each time it is called with the same IN and INOUT arguments. Clear the check box to set the parameter to NOT DETERMINISTIC.

   Use CPU Service Time Limit

   Indicates to use the CPU service unit limit. Enter an integer in the field below.

   CPU Service Time Limit

   Specifies the service time limit.

   Commit Transaction On Return

   Indicates that DB2 commits the transaction immediately on return from the stored procedure. Default: NO (check box is cleared).

   Special Register Usage

   Specifies how special registers are set on entry to the routine. Select a value from the drop-down list: Inherit (Special Registers), Default (Special Registers).

   Null Input

   Specifies that the procedure is to be called even if any or all argument values are null. Select Called from the drop-down list, or leave the value empty.

   CCSId

   Indicates the encoding scheme for string parameters. Select a value from the drop-down list: ASCII, EBCDIC, or UNICODE.

   For DB2 z/OS SQL Native procedures, the following additional options appear:

   Qualifier Schema

   Specifies the schema to be used as the implicit qualifier. Select a schema name from the drop-down list.

   Package Owner

   Specifies the owner of the package. Select a name from the drop-down list.

   Is Prepare Deferred

   Indicates that the DEFER PREPARE parameter is set. Clear the check box to indicate NODEFER PREPARE.

   Use Data Currency

   Specifies that data currency is required for read-only and ambiguous cursors. Clear the check box to set the parameter to CURRENTDATA NO.

   Is Parallel Processing Allowed

   Specifies whether or not to run a query using parallel processing for optimum performance. Clear the check box to indicate NO.

   For DB2 z/OS External procedures, the following additional options appear:

   Parameter Style

   Identifies the linkage convention used to pass parameters to and return values from the stored procedure. Select a value from the drop-down list: SQL, General, General with null, Java.

   Parameter Varchar Form

   Specifies, for varying length character parameters, the value representation. Select a value from the drop-down list: Null Terminated or Structured.

   Use Package Path

   Indicates to use the package path entered in the following field.

   Package Path

   Indicates the package path.

4. When you work with an External or SQL Native procedure type, you can work with the following options on the Other Options tab:

   Is Fenced

   Specifies that the stored procedure runs in an external address space to prevent user programs from corrupting DB2 storage.

   Use DB Info

   Specifies whether or not status information known by DB2 is passed to the stored procedure when it is invoked. Clear the check box to indicate NO DBINFO.

   Use Package Collection

   Indicates to use the package collection id indicated in the next field.

   Package Collection Id

   Identifies the package collection to be used when the stored procedure is executed. Enter the collection id in the text box.

   Remain Resident In Memory

   Indicates that the load module remains resident in memory after the stored procedure ends. Clear the check box to indicate STAY RESIDENT NO.

   Program Type

   Specifies how the stored procedure runs. Select a value from the drop-down list: Subroutine or Main Routine.

   External Security Type

   Specifies how the stored procedure interacts with an external security product. Select a value from the drop-down list: DB2, User, Definer.

   Failure Stop Type

   Specifies how to put the routine in a stopped state after the indicated number of failures. Select a value from the drop-down list: System Default, Specific Number, Continue (After Failure).

   Failure Limit

   Indicates the failure limit to work with the above Failure Stop Type option. Enter an integer in the text box.

   Runtime Options

   Specifies any Language Environment run-time options, as a string.

   Workload Manager Environment

   Identifies the workload manager (WLM) environment. Enter the WLM name in the text box.

   Use Calling Procedure Workload Manager Environment

   Indicates to use the WLM calling procedure. Clear the check box to not specify a WLM ENVIRONMENT.

   Debug Mode

   Specifies the debug mode. Select an option from the drop-down menu: Disallow, Allow, or Diaable.

5. When you work with a SQL Native procedure type, you can work with the following options on the SQL Native Options tab:

   Decimal Arithmetic Precision

   Specifies a value for the maximum precision to be used for decimal arithmetic operations. Enter an value in the text box.

   Cursor For Update Clause Mode

   Specifies the For Update Clause option. Select a value from the drop-down list: Required, or Optional.

   Time Format

   Specifies the time format for result values that are string representations of date or time values. Select a value from the drop-down list: ISO, European, USA, Japanese, or Local.

   SQL Path Type

   Specifies the SQL path type for the stored procedure. Select a value from the drop-down list: Default, Schema, Schema List, Session User.

   SQL Path Schemas

   Specifies the SQL Path Schemas. Select from the list of displayed schemas.

6. When you work with the External procedure type, you can work with the following options on the External Name tab:

   External Name

   Identifies the user-written code that implements the stored procedure.

   File

   Indicates the file to reference for the external name.

   Class Name

   Identifes the class name. Enter a value in the text box.

   Method Name

   Identifies the name of the method. Enter a value in the text box.

   Package Ids

   Identifies the package names that the class identifier is part of. Use features on the toolbar near the Package Ids box to add, delete, and reorder the Package Ids.

   Method Data Types

   Indicates the Method Data Types for the stored procedure. Use features on the toolbar near the Method DataTypex box to add, delete, and reorder the Method Data Types.

7. Work with the other tabs in the editor to access additional design features, for example:
   • Edit the SQL code for the selected stored procedure on the Code tab
   • View expanded code (read-only) on the Expanded tab
   • Work with parameters for the selected stored procedure on the Parameter tab
   • Change the object creation order on the Object Creation Order tab
   • Associate permissions with the selected stored procedure on the Permission tab
   • Review where the stored procedure is used on the Where Used tab.
8. (Optional) Click the Comment tab and enter any comments you want associated with the database role.
9. (Optional) Click the UDP tab to work with user-defined properties for the database role.
10. (Optional) Click the Notes tab to view history information and view or edit user notes.
11. Click Close.

    The DB2 z/OS Stored Procedure Editor closes.

More Information

View Expanded Code in a Property Editor

Change the Object Creation Order in a Property Editor