CREATE PROCEDURE

The CREATE PROCEDURE data description statement stores the definition of a procedure in the SQL catalog. You can refer to the procedure in an SQL CALL statement or in an SQL SELECT statement just as you would a table procedure. These references result in CA IDMS calls to the corresponding routine. Such routines can perform any action, such as manipulating data stored in some other organization (for example, in a network-defined database or in a set of VSAM files). You can also use them to implement business logic.
idmscu
The CREATE PROCEDURE data description statement stores the definition of a procedure in the SQL catalog. You can refer to the procedure in an SQL CALL statement or in an SQL SELECT statement just as you would a table procedure. These references result in CA IDMS calls to the corresponding routine. Such routines can perform any action, such as manipulating data stored in some other organization (for example, in a network-defined database or in a set of VSAM files). You can also use them to implement business logic.
Procedures can be defined with a language of SQL. The routine actions, written as SQL statements, are specified and stored together with the procedure definition in the SQL catalog.
The formal parameters of a procedure definition can be used like columns of a table during a procedure invocation to pass values to and from the procedure.
This article describes the following information:
2
2
Authorization
To issue a CREATE PROCEDURE statement, you must own the schema in which the procedure is being defined or hold the CREATE privilege on the named procedure.
Syntax
►►─ CREATE PROCEDURE ─┬──────────────────────────────┬─
procedure-identifier
─► └─────
schema-name
. ───────────┘ ┌───────── , ────────┐ ►─(▼
parameter-definition
┴) EXTERNAL NAME
external-routine-name
──────────────► ►───┬───────────────────┬───┬─────────────────────────────┬──────────────────► └─
language-clause
─┘ └── PROTOCOL ───┬── IDMS ──┬──┘ └── ADS ───┘ ►───┬────────────────────────────────────┬─┬──────────────────────────┬──────► └─────── ESTIMATED ROWS
row-count
───┘ └─ ESTIMATED IOS
io-count
─┘ ►────┬───────────────┬───────────────────────────────────────────────────────► ├─ USER MODE ◄──┤ └─ SYSTEM MODE ─┘ ►────┬─────────────────────────────────────┬─────────────────────────────────► └── LOCAL WORK AREA
local-stge-size
─┘ ►────┬────────────────────────────────────────────────────────┬──────────────► └─ GLOBAL WORK AREA ──
global-stge-size
──┬──────────────┤ └─
KEY key-id
─┘ ►────┬───────────────────────────────────────────────────────────────┬───────► └─ TRANSACTION SHARING ───────────────────┬─ ON ───────┬────────┘ ├─ OFF ──────┤ └─ DEFAULT ◄─┘ ►────┬───────────────────────────────────────────────────────────────┬───────► └─ DEFAULT DATABASE ────────────────────┬ NULL ◄───┬───────────┘ └─CURRENT──┘ ►────┬───────────────────────────────────────────┬───────────────────────────► └── TIMESTAMP
timestamp-value
─────────────┘ ►───┬─────────────────────────────────────────────────────┬──────────────────► └── DYNAMIC RESULT SETS
maximum-dynamic-result-sets
──┘ ►──┬───────────────────────────────────────────────────────────────────────┬─►◄ └┬──────────────────────────────────────────────┬
procedure-statement
──┘ │ ┌──────────────────┐ │ └ ADS COMPILE OPTION ─▼─
compile-option
─┴─ ; ─┘
Expansion of parameter-definition
►►───
parameter-name
──
data-type
─┬────────────────┬─────────────────────────►◄ └─ WITH DEFAULT ─┘
Expansion of language-clause
►►─── LANGUAGE ────────────────────┬─ ADS ──────────┬──────────────────────────►◄ ├─ ASSEMBLER ────┤ ├─ COBOL ────────┤ ├─ PLI ──────────┤ └─ SQL ──────────┘
Expansion of procedure-statement
►────┬──
SQL-AM-mgmt-stmt
───────────┬────────────────────────────────────────►◄ ├──
SQL-authorization-stmt
─────┤ ├──
SQL-Control-stmt
───────────┤ ├──
SQL-Diagnostics-stmt
───────┤ ├──
SQL-DDL-stmt
───────────────┤ ├──
SQL-DML-stmt
───────────────┤ ├──
SQL-session-mgmt-stmt
──────┤ └──
SQL-transaction-mgmt-stmt
──┘
Parameters
  • procedure-identifier
    Specifies the 1- to 18-character name of the procedure you are creating.
    Procedure-identifier
    must:
    • Be unique among the function, procedure, table, table procedure and view identifiers within the schema associated with the procedure
    • Follow conventions for SQL identifiers
  • schema-name
    Specifies the schema name qualifier to be associated with the procedure.
    Schema-name
    must identify a schema defined in the dictionary. If you do not specify a
    schema-name
    , it defaults to:
    • The current schema associated with your SQL session, if the statement is specified through the Command Facility or executed dynamically
    • The schema associated with the access module used at runtime, if the statement is embedded in an application program
  • parameter-definition
    Defines a parameter to be associated with the procedure. Parameters pass to the procedure in the order you specify them. You must enclose the list of parameters in parentheses. You must separate multiple parameter definitions by commas.
    Expanded syntax for
    parameter-definition
    is shown above immediately following the CREATE PROCEDURE syntax. Descriptions for these parameters are located at the end of this section.
  • external-routine-name
    Specifies the one- to eight-character name of the program which is called to process references to the procedure.
    For procedures written in SQL, the external-routine-name should specify a name that is unique within the dictionary that holds the procedure definition. In other words, the name should be different from any other external name of any SQL-invoked routine and from any &U$IDCADS. dialog, RCM, or AM name.
  • language-clause
    Specifies the programming language of the procedure. This clause is required for procedures written in SQL. For others, it is documentational only. If the language is not specified, it is treated as null.
  • PROTOCOL
    Specifies the PROTOCOL with which the procedure is invoked. This specification is required except with language SQL. If LANGUAGE SQL is specified, PROTOCOL must be ADS or the clause must not be specified.
    • IDMS
      Use IDMS for procedures that are written in COBOL, PL/I, or Assembler.
    • ADS
      Use ADS for procedures that are written in CA ADS. The name of the dialog that is loaded and executed when the procedure is invoked is specified by the
      external-routine-name
      in the EXTERNAL NAME clause. ADS is the default if LANGUAGE SQL is specified.
  • row-count
    Specifies an integer value, in the range 0 through 2,147,483,647, representing the average number of rows returned by the procedure for a given set of input parameters.
  • io-count
    Specifies an integer value, in the range 0 through 2,147,483,647, representing the average number of disk accesses generated by the procedure for a given set of input parameters.
  • USER MODE
    Specifies that the procedure should execute as a user-mode application program within CA IDMS. This can not be specified with language SQL or protocol ADS. For other languages and protocols, it is the default.
  • SYSTEM MODE
    Specifies that the procedure should execute as a system mode application program. SYSTEM MODE is the default if language is SQL.
    To execute as a system mode application, the program must be fully reentrant and be written in either:
    • ADS as a mapless dialog
    • Assembler using DC calling conventions
    • COBOL or PL/I and compiled with an LE-compliant compiler
  • local-stge-size
    Specifies an integer, in the range 0 through 32767, which represents the size, in bytes, of a local storage area which CA IDMS allocates at runtime and passes to the procedure on each invocation.
    If you do not code a LOCAL WORK AREA clause, the default local storage size is 1024 bytes.
  • global-stge-size
    Specifies an integer, in the range 0 through 32767, representing the size, in bytes, of the global storage area that CA IDMS allocates at runtime and passes to the procedure on each invocation.
    CA IDMS allocates a global storage area once within a transaction and retains it until the transaction terminates.
  • key-id
    Specifies the one- to four-character identifier for the global storage area. CA IDMS passes the same piece of global storage within a transaction to all SQL routines that have the same global storage key.
    If you do not specify the storage key, CA IDMS allocates each procedure its own global storage area, which is not used for any other routine within the transaction.
  • TRANSACTION SHARING
    Specifies whether transaction sharing should be enabled for database sessions started by the procedure. If transaction sharing is enabled for a procedure's database session, it shares the current transaction of the SQL session. If language SQL is specified, TRANSACTION SHARING must be ON or the clause must not be specified.
    • ON
      Specifies that transaction sharing should be enabled. ON is the default if language is SQL.
    • OFF
      Specifies that transaction sharing should be disabled.
    • DEFAULT
      Specifies that the transaction sharing setting in effect when the procedure is invoked should be retained. Default is the default for languages other than SQL.
  • compile-option
    Specifies a CA ADS option to be used when compiling the dialog associated with an SQL procedure. The options that can be specified and the syntax to use are given in the "ADS Reference" section; see "Dialog-expression" under "Application and Dialog Utilities."
    Compile-option
    can be specified only if language is SQL.
    The ability to specify the ADS COMPILE OPTION clause is a CA IDMS extension.
  • procedure-statement
    Specifies the actions taken in the procedure.
    Procedure-statement
    is required if language is SQL. It cannot be specified otherwise.
    Expanded syntax for
    procedure-statement
    is shown above immediately following the CREATE PROCEDURE syntax. Descriptions for these parameters are located at the end of this section.
  • DEFAULT DATABASE
    Specifies whether a default database should be established for database sessions started by the procedure.
    • NULL
      Specifies that no default database should be established.
    • CURRENT
      Specifies that the database to which the SQL session is connected should become the default for any database session started by the procedure.
  • timestamp-value
    Specifies the value of the synchronization stamp to be assigned to the procedure.
    Timestamp-value
    must be a valid external representation of a timestamp.
  • DYNAMIC RESULT SETS
    Defines the maximum number of result sets that a procedure invocation can return to its caller. A result set is a sequence of rows specified by a
    cursor-specification
    , created by the opening of a cursor and ranged over that cursor.
  • maximum-dynamic-result-sets
    Defines an integer in the range 0-32767 specifying the maximum number of result sets a procedure can return. The default is 0.
Parameters for Expansion of parameter-definition
  • parameter-name
    Specifies a 1- to 32-character name of a parameter to be passed to the table procedure.
    Parameter-name
    must:
    • Be unique within the table procedure that you are defining
    • Follow the conventions for SQL identifiers
    All parameters are implicitly nullable. Input parameters can be assigned NULL as a parameter value and output parameters can return NULL.
  • data-type
    Defines the data type for the named parameter. For expanded
    data-type
    syntax, see Expansion of Data-type.
  • WITH DEFAULT
    Directs CA IDMS to pass a default value for the named parameter if no value for the parameter is specified.
    The default value for a parameter is based on its data type:
Column data type
Default value
CHARACTER
Blanks
VARCHAR
A character string literal with a length of zero (that is, '')
GRAPHIC
Double-byte blanks
VARGRAPHIC
A double-byte character string literal with a length of zero
DATE
The value in the CURRENT DATE special register
TIME
The value in the CURRENT TIME special register
TIMESTAMP
The value in the CURRENT TIMESTAMP special register
All numeric data types
0 (zero)
Parameters for Expansion of language-clause
  • ADS
    Specifies that the SQL routine is written in the CA ADS language.
  • ASSEMBLER
    Specifies that the SQL routine is written in the assembler language.
  • COBOL
    Specifies that the SQL routine is written in the COBOL language.
  • PLI
    Specifies that the SQL routine is written in the PL/I language.
  • SQL
    Specifies that the SQL routine is written in the SQL language.
The ability to specify ADS or ASSEMBLER as a language is a CA IDMS extension.
Parameters for Expansion of procedure-statement
  • SQL-AM-mgmt-stmt
    Specifies a statement from the Access Module Management Statements category.
  • SQL-authorization-stmt
    Specifies a statement from the Authorization Statements category.
  • SQL-Control-stmt
    Specifies a statement from the Control Statements category.
  • SQL-Diagnostics-stmt
    Specifies a statement from the Diagnostics Statements category.
  • SQL-DDL-stmt
    Specifies a statement from the Data Description Statements category.
  • SQL-DML-stmt
    Specifies a statement from the Data Manipulation Statements category.
  • SQL-session-mgmt-stmt
    Specifies a statement from the Session Management Statements category.
    The ability to include a RELEASE, SUSPEND, or RESUME statement in an SQL routine is a CA IDMS extension.
  • SQL-transaction-mgmt-stmt
    Specifies a statement from the Transaction Management Statements category.
    The ability to include a COMMIT or ROLLBACK statement in an SQL routine is a CA IDMS extension.
Usage
Influencing Join Strategies
CA IDMS uses estimated row and I/O counts in determining the cost of joining a procedure with other tables, views, procedures or table procedure. To determine the optimal access strategy, CA IDMS examines different sequences for retrieving information. By providing the estimated row and I/O counts for both the procedure and for each access key used by the procedure, CA IDMS can select the optimal access strategy.
In determining the cost of a specific access strategy, CA IDMS uses estimates provided in CREATE PROCEDURE unless input values are available for each of the parameters included in a key. If values are available for each of these parameters, CA IDMS uses the estimates specified in the CREATE KEY statement instead of those specified in CREATE PROCEDURE.
Specifying a Synchronization Stamp
When defining or altering a procedure, you can specify a value for its synchronization stamp. You should use care when doing so because the purpose of the stamp is to enable the detection of discrepancies between an entity and its definition. If explicitly specified, you must set the synchronization stamp to a new value following a change so that the change is detectable by the runtime system.
If not specified, the synchronization stamp is automatically set to the current date and time.
Coding procedures with language SQL
The rules for coding the procedure body of an SQL procedure are given by
procedure-statement
. A procedure body typically contains multiple SQL statements and according to the SQL grammar, SQL statements are terminated by the semi-colon. However, to define SQL routines, the Command Facility (OCF, IDMSBCF, or Visual DBA OCF console) needs to be used. It also has the semi-colon as the default command terminator. Before a new command can be specified, the CREATE PROCEDURE needs to be terminated by a semi-colon. Clearly, the semi-colon cannot concurrently be used as a terminator by both the SQL procedure language and the Command Facility. Therefore, when
procedure-statement
contains multiple SQL statements or when the ADS COMPILE OPTION is specified, the Command Facility needs to use a terminator different from the semi-colon. To accomplish this, a SET OPTIONS COMMAND DELIMITER 'delimiter-string' must be executed. Changing the terminator of the Command Facility remains in effect until the end of the session or until a new SET OPTIONS COMMAND DELIMITER is encountered. For more information about SET OPTIONS, see the Command Facility information in the "Using Common Facilities" section of the CA IDMS documentation.
Language SQL
If LANGUAGE SQL is specified, the following attribute settings are established by default and must not be overridden to a different value:
  • Protocol is ADS
  • Mode is SYSTEM
  • Transaction sharing is ON
Procedures whose language is SQL are implemented through an automatically generated CA ADS dialog whose name is
external-routine-name
.
An error while parsing
procedure-statement
or an error while compiling the associated CA ADS dialog causes the CREATE PROCEDURE statement to terminate with a warning instead of a statement error. This allows the erroneous
procedure-statement
syntax to be saved in the catalog for later correction using the DISPLAY PROCEDURE command. The CA ADS dialog and associated access module are not created.
Specifying CA ADS Compile Options
If LANGUAGE SQL is specified, you can specify one or more compile options to be used when the associated dialog is compiled. Specifying compile options can be useful for debugging purposes to enable tracing and the use of online debugging facilities. Compile options can also be used to include additional work records and SQL tables which can be referenced in native CA ADS code included in the routine body.
Some useful compile options include:
  • SYMBOL TABLE IS YES - to allow the use of symbols by the TRACE command and the online debug facilities
  • ADD RECORD record-name - to enable manipulation of elements from the specified record
  • ADD SQL TABLE table-name - to enable manipulation of columns or parameters of the specified SQL table-like object
Grouping procedure statements into a single statement
Multiple procedure statements can be grouped together as a compound statement. A compound statement is a control statement and therefore is also a procedure statement.
Dynamic Result Sets
An SQL invoked procedure can return one or more result sets to its caller, up to the maximum number specified by its dynamic result sets attribute. A result set is returned for each returnable cursor that is still open when the procedure returns control to its caller.
Example
The following CREATE PROCEDURE statement defines a procedure.
create procedure emp.get_bonus (emp_id unsigned numeric(4) with default, bonus unsigned numeric(10) with default, currency_bonus char(3) with default) external name getbonus protocol idms;
The procedure USER01.TSELECT1 uses the given employee ID to retrieve the first and last name. It returns the edited name in the RESULT parameter.
create procedure USER01.TSELECT1 ( TITLE varchar(10) with default , P_EMP_ID numeric(4) , RESULT varchar(20) ) EXTERNAL NAME TSELECT1 LANGUAGE SQL select trim(EMP_FNAME) || ' ' || trim(EMP_LNAME) into RESULT from DEMOEMPL.EMPLOYEE where EMP_ID = P_EMP_ID ; call user01.tselect1('TSIGNAL3', 1003); *+ *+ TITLE P_EMP_ID RESULT *+ ----- -------- ------ *+ TSIGNAL3 1003 Jim Baldwin
The GET_EMPLOYEE_INFO procedure uses the given employee ID, to construct two result set cursors:
  • A static declared cursor RET_COVERAGE returns a cursor with the data from the COVERAGE table.
  • The allocated dynamic cursor RET_BENEFITS to return the data from the BENEFITS data.
set options command delimiter '++'; create procedure SQLROUTE.GET_EMPLOYEE_INFO ( TITLE varchar(10) with default , P_EMP_ID numeric(4) , RESULT varchar(20) ) EXTERNAL NAME GETEMPIN LANGUAGE SQL DYNAMIC RESULT SETS 2 begin not atomic declare STMNT_NAME char(10) default 'DYN_STMNT1'; declare STMNT_BUF char(80) default ' '; declare RET_COVERAGE cursor with return for select * from DEMOEMPL.COVERAGE where EMP_ID = P_EMP_ID; open RET_COVERAGE; set STMNT_BUF = 'select * from DEMOEMPL.BENEFITS' || 'where EMP_ID = ' || P_EMP_ID; prepare STMT_NAME from STMT_BUF; allocate 'RET_BENEFITS' cursor with return for STMT_NAME; open 'RET_BENEFITS'; set RESULT = '2 returned result sets'; end set options command delimiter default ++
More Information