Previous Topic: Define a DB2 z/OS View TriggerNext Topic: DB2 LUW Property Editors


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 New icon in property editors to create a new object 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 Parameter tab (applies to all procedure types):
    Parameter

    Specifies the name of the parameter. Enter a name that is unique to the model.

    Type

    Specifies the type of the parameter. The type can be Input, Output, or Input Output.

    Physical Data Type

    Specifies the data type for the parameter.

    As Locator

    For a SQL External type of procedure, specifies whether the parameter is a transition table. For SQL Native and External types of procedures, specifies that a locator to the value of the parameter is passed to the procedure instead of the actual value.

    Character Type

    Specifies the sub-type of the specified Character data type. You can select from For Bit Data, For Mixed Data, For Single Byte Char Set.

    CCSID

    Specifies the encoding scheme of the parameter, for character and graphic data types. You can select ASCII, EBCDIC, or UNICODE.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. Work with the other tabs in the editor to access additional design features, for example:
  9. (Optional) Click the Comment tab and enter any comments that you want to associate with the object.
  10. (Optional) Click the UDP tab to work with user-defined properties for the object.
  11. (Optional) Click the Notes tab to view and edit user notes.
  12. 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