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
The DB2 Stored Procedure Editor opens.
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.
Specifies the name of the stored procedure.
Specifies the default schema for the stored procedure.
Specifies whether the stored procedure is Model Level or Table Level. Make a selection from the drop-down list.
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.
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.
Generates DDL for this object during Forward Engineering.
Specifies the name of the parameter. Enter a name that is unique to the model.
Specifies the type of the parameter. The type can be Input, Output, or Input Output.
Specifies the data type for the parameter.
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.
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.
Specifies the encoding scheme of the parameter, for character and graphic data types. You can select ASCII, EBCDIC, or UNICODE.
For DB2 z/OS:
Specifies the maximum number of query result sets that the stored procedure can return. Default: 0
Specifies the application programming language in which the stored procedure is written. Valid values: Assemble, C, Cobol, Java, PLI, REXX, SQL.
Specifies the classification of SQL statements that the procedure can execute. Valid values: Modifies SQL Data, Reads SQL Data, Contains SQL, No SQL.
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.
Indicates to use the CPU service unit limit. Enter an integer in the field below.
Specifies the service time limit.
Indicates that DB2 commits the transaction immediately on return from the stored procedure. Default: NO (check box is cleared).
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).
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.
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:
Specifies the schema to be used as the implicit qualifier. Select a schema name from the drop-down list.
Specifies the owner of the package. Select a name from the drop-down list.
Indicates that the DEFER PREPARE parameter is set. Clear the check box to indicate NODEFER PREPARE.
Specifies that data currency is required for read-only and ambiguous cursors. Clear the check box to set the parameter to CURRENTDATA NO.
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:
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.
Specifies, for varying length character parameters, the value representation. Select a value from the drop-down list: Null Terminated or Structured.
Indicates to use the package path entered in the following field.
Indicates the package path.
Specifies that the stored procedure runs in an external address space to prevent user programs from corrupting DB2 storage.
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.
Indicates to use the package collection id indicated in the next field.
Identifies the package collection to be used when the stored procedure is executed. Enter the collection id in the text box.
Indicates that the load module remains resident in memory after the stored procedure ends. Clear the check box to indicate STAY RESIDENT NO.
Specifies how the stored procedure runs. Select a value from the drop-down list: Subroutine or Main Routine.
Specifies how the stored procedure interacts with an external security product. Select a value from the drop-down list: DB2, User, Definer.
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).
Indicates the failure limit to work with the above Failure Stop Type option. Enter an integer in the text box.
Specifies any Language Environment run-time options, as a string.
Identifies the workload manager (WLM) environment. Enter the WLM name in the text box.
Indicates to use the WLM calling procedure. Clear the check box to not specify a WLM ENVIRONMENT.
Specifies the debug mode. Select an option from the drop-down menu: Disallow, Allow, or Diaable.
Specifies a value for the maximum precision to be used for decimal arithmetic operations. Enter an value in the text box.
Specifies the For Update Clause option. Select a value from the drop-down list: Required, or Optional.
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.
Specifies the SQL path type for the stored procedure. Select a value from the drop-down list: Default, Schema, Schema List, Session User.
Specifies the SQL Path Schemas. Select from the list of displayed schemas.
Identifies the user-written code that implements the stored procedure.
Indicates the file to reference for the external name.
Identifes the class name. Enter a value in the text box.
Identifies the name of the method. Enter a value in the text box.
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.
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.
The DB2 z/OS Stored Procedure Editor closes.
|
Copyright © 2017 erwin Inc.
All rights reserved.
|
|