Physical Modeling › DB2 LUW Property Editors › Define DB2 LUW Stored Procedures

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