Previous Topic: Define a DB2 LUW SequenceNext Topic: Define DB2 LUW Users


Define DB2 LUW 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 DB2 stored procedures

  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. Select from the drop-down list.

    Procedure Type

    Indicates the procedure type. Select from the drop-down list; valid valules are External, Sourced, and SQL.

    Use Replace Syntax

    Specifies whether or not to use the CREATE or REPLACE command instead of the CREATE command during Forward Engineering.

    Attach to New

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

    Generate

    Specifies whether or not to generate DDL for this object during Forward Engineering.

  3. Work with the following options on the Parameter tab:
    Parameter

    Specifies the name of the parameter. The name must be unique to the procedure.

    Type

    Specifies the mode of the parameter. Select from the drop-down list; valid values are Input, Output, and Input Output.

    Physical Data Type

    Specifies the data type of the parameter. Select from the drop-down list.

    Character Type

    Specifies whether the data type is for binary data.

    Default

    Specifies the default value of the parameter. Create or select from the drop-down list.

    Anchored To Type

    Specifies the type of data to which the variable is anchored. Select from the drop-down list; valid values are: Variable, Column, Table Row, View Row, and Cursor Variable.

    Global Variable

    Specifies the global variable to which the selected variable is anchored. This field appears only when you set the value for the Anchored To Type field as either Variable or Cursor Variable.

    Abstract Entity

    Specifies the name of the table for the anchored column. This field appears only when you set the value for the Anchored To Type field as Table Row.

    Column

    Specifies the name of the anchored column for the variable. This field appears only when you set the value for the Anchored To Type field as either Column or View Row.

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

    Provides a unique name for the instance of the stored procedure that is being defined.

    SQL Data Access

    Indicates the level of data access for SQL statements included in the procedure. Select a value from the drop-down list; valid values are Modifies SQL Data, Reads SQL Data, and Contains SQL Data.

    Is External Action Allowed

    Specifies that the stored procedure takes some action that changes the state of an object not managed by the database manager.

    Is Deterministic

    Specfies that the stored procedure the procedure always returns the same results for given argument values.

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

    Specifies the language in which the procedure body is written. Select from the drop-down list; valid values are C, Java, COBOL, OLE, SQL, and CLR.

    Coded Character Set Identifier

    Specifies the encoding scheme for the string data passed to and from of the procedure. Select from the drop-down list; valid values are ASCII and UNICODE.

    Parameter Style

    Specifies the conventions for passing parameters to and returning values from procedures. Select from the drop-down list; valid values are DB2 General, DB2 SQL, General, General with Nulls, Java, and SQL.

    Program Type

    Specifies how the stored procedure runs. Select from the drop-down list; valid values are Subroutine or Main Routine.

    Is Fenced

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

    Is Thread Safe

    Specifies if the procedure is safe to run in the same process as other routines.

    Use DB Info

    Specifies whether or not status information known by DB2 is passed to the stored procedure when it is invoked.

    Null Input

    Specifies if the procedure is called regardless of whether any arguments are null. Select Called from the drop-down list if you want to call procedures with null arguments.

    Use New Savepoint Level (check box)

    Specifies whether or not the procedure creates a savepoint level for savepoint names and effects.

    Special Register Usage

    Specifies if updatable special registers in the procedure inherit their initial values from the environment of the invoking statement. Select Inherit from the drop-down list if you want the special registers to inherit initial values.

    Dynamic Result Sets

    Specifies the estimated maximum limit of the returned result sets for the procedure.

    Commit Transaction On Return

    Specifies if you want to issue the COMMIT command after the procedure is executed.

    Executes Autonomously (check box)

    Specifies whether or not you want the procedure to run in its own autonomous transaction scope.

  6. When you select Sourced procedure type, you can work with the following options on the Other Options tab:
    Source Object Schema Name

    Specifies the schema name of the source procedure.

    Source Object Package Name

    Specifies the package name of the source procedure.

    Source Number of Parameters

    Specifies the number of parameters for the source procedure. Enter an integer.

    Source Object ID

    Specifies the unique identifier for the source procedure; this identifier helps you identify the specific procedure when there are multiple identical procedures.

    Server

    Specifies a server definition that you have registered using the CREATE SERVER statement.

  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.

  8. Work with the other tabs in the editor to access additional design features, for example:
  9. Click the Comment tab and enter any comments you want to associate with the stored procedure.
  10. Click the UDP tab to work with user-defined properties for the stored procedure.
  11. Click Close.

    The DB2 for LUW Stored Procedure Editor closes.

More Information

View Expanded Code in a Property Editor

Change the Object Creation Order in a Property Editor