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 non SQL-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.
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.
►►─ 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 ──┘
Specifies the 1- to 18-character name of the procedure you are creating. Procedure-identifier must:
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:
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.
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.
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.
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.
Use IDMS for procedures that are written in COBOL, PL/I, or Assembler.
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.
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.
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.
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.
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:
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.
Note: If you do not code a LOCAL WORK AREA clause, the default local storage size is 1024 bytes.
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.
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.
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.
Specifies that transaction sharing should be enabled. ON is the default if language is SQL.
Specifies that transaction sharing should be disabled.
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.
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 CA ADS Reference Guide, Appendix D.2.6 Dialog-expression. Compile-option can be specified only if language is SQL.
Note: The ability to specify the ADS COMPILE OPTION clause is a CA IDMS extension.
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.
Specifies whether a default database should be established for database sessions started by the procedure.
Specifies that no default database should be established.
Specifies that the database to which the SQL session is connected should become the default for any database session started by the procedure.
Specifies the value of the synchronization stamp to be assigned to the procedure. Timestamp-value must be a valid external representation of a timestamp.
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.
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
Specifies a 1- to 32-character name of a parameter to be passed to the table procedure. Parameter-name must:
All parameters are implicitly nullable. Input parameters can be assigned NULL as a parameter value and output parameters can return NULL.
Defines the data type for the named parameter. For expanded data-type syntax, see Expansion of Data-type.
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
Specifies that the SQL routine is written in the CA ADS language.
Specifies that the SQL routine is written in the assembler language.
Specifies that the SQL routine is written in the COBOL language.
Specifies that the SQL routine is written in the PL/I language.
Specifies that the SQL routine is written in the SQL language.
Note: The ability to specify ADS or ASSEMBLER as a language is a CA IDMS extension.
Parameters for Expansion of procedure-statement
Specifies a statement from the Access Module Management Statements category.
Specifies a statement from the Authorization Statements category.
Specifies a statement from the Control Statements category.
Specifies a statement from the Diagnostics Statements category.
Specifies a statement from the Data Description Statements category.
Specifies a statement from the Data Manipulation Statements category.
Specifies a statement from the Session Management Statements category.
Note: The ability to include a RELEASE, SUSPEND, or RESUME statement in an SQL routine is a CA IDMS extension.
Specifies a statement from the Transaction Management Statements category.
Note: The ability to include a COMMIT or ROLLBACK statement in an SQL routine is a CA IDMS extension.
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 chapter in the CA IDMS Common Facilities Guide.
Language SQL
If LANGUAGE SQL is specified, the following attribute settings are established by default and must not be overridden to a different value:
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:
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.
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:
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 ++
|
Copyright © 2014 CA.
All rights reserved.
|
|