The
CREATE PROCEDURE (sourced) statement defines a procedure (the sourced
procedure) that is based on another procedure (the source procedure).
In a federated system, a federated procedure is a sourced procedure
whose source procedure is at a supported data source.
Invocation
This statement can be embedded
in an application program or issued through the use of dynamic SQL
statements. It is an executable statement that can be dynamically
prepared only if DYNAMICRULES run behavior is in effect for the package
(SQLSTATE 42509).
Authorization
The privileges held by the
authorization ID of the statement must include at least one of the
following authorities:
- IMPLICIT_SCHEMA authority on the database, if the schema name
of the procedure does not refer to an existing schema
- CREATEIN privilege on the schema, if the schema name of the procedure
refers to an existing schema
- DBADM authority
For data sources that require a user mapping, the privileges
held at the data source by the authorization ID of the statement must
include the privilege to select the procedure's description from the
remote catalog tables.
To replace an existing
procedure, the authorization ID of the statement must be the owner
of the existing procedure (SQLSTATE 42501).
Syntax
>>-CREATE--+------------+--PROCEDURE--procedure-name------------>
'-OR REPLACE-'
>--| source-procedure-clause |--| option-list |----------------><
source-procedure-clause
|--SOURCE--| source-object-name |------------------------------->
>--+-------------------------------+---------------------------->
+-( )---------------------------+
'-NUMBER OF PARAMETERS--integer-'
>--+----------------------+--FOR SERVER--server-name------------|
'-UNIQUE ID--unique-id-'
source-object-name
|--+---------------------------------------------------+-------->
'-source-schema-name--.--+------------------------+-'
'-source-package-name--.-'
>--source-procedure-name----------------------------------------|
option-list
|--●--+-------------------------+--●---------------------------->
'-SPECIFIC--specific-name-'
.-WITH RETURN TO CALLER--ALL-------------------------------------.
>--+----------------------------------------------------------------+-->
| .-,-------------------------. |
| V | |
'-WITH RETURN TO CLIENT--+-(----result-set-element-number-+--)-+-'
'-ALL---------------------------------'
>--●--+-------------------+--●--+-------------------+--●-------->
+-NO SQL------------+ +-NOT DETERMINISTIC-+
+-CONTAINS SQL------+ '-DETERMINISTIC-----'
+-MODIFIES SQL DATA-+
'-READS SQL DATA----'
>--+--------------------+--●------------------------------------|
+-EXTERNAL ACTION----+
'-NO EXTERNAL ACTION-'
Description
- OR REPLACE
- Specifies to replace
the definition for the procedure if one exists at the current server.
The existing definition is effectively dropped before the new definition
is replaced in the catalog, with the exception that privileges that
were granted on the procedure are not affected. This option can be
specified only by the owner of the object. This option is ignored
if a definition for the procedure does not exist at the current server.
To replace an existing procedure, the specific name and procedure
name of the new definition must be the same as the specific name and
procedure name of the old definition, or the signature of the new
definition must match the signature of the old definition. Otherwise,
a new procedure is created.
- procedure-name
- Names
the sourced procedure being defined. It is a qualified or unqualified
name that designates a procedure. The unqualified form of procedure-name is
an SQL identifier. In dynamic SQL statements, the CURRENT SCHEMA special
register is used as a qualifier for an unqualified object name. In
static SQL statements, the QUALIFIER precompile or bind option implicitly
specifies the qualifier for unqualified object names. The qualified
form is a schema-name followed by a period
and an SQL identifier.
The name, including the implicit or explicit
qualifiers, together with the number of parameters, must not identify
a procedure that is described in the catalog (SQLSTATE 42723). The
unqualified name, together with the number of parameters, need not
be unique across schemas.
If a two-part name is specified,
the schema-name cannot begin with 'SYS'
(SQLSTATE 42939).
In a federated system, procedure-name is
the name of the procedure on the federated server.
- SOURCE source-object-name
- Specifies the source procedure that is used by the procedure being
defined. In a federated system, the source procedure is a procedure
that is located at a supported data source.
- source-schema-name
- Identifies the schema name of the source procedure. If a schema
name is used to identify the source procedure, the source-schema-name must
be specified in the CREATE PROCEDURE (Sourced) statement. If the source-schema-name contains
any special or lowercase characters, it must be enclosed by double
quotation marks.
- source-package-name
- Identifies the package name of the source procedure. The source-package-name applies
only to Oracle data sources. If a package name is used to identify
the source procedure, the source-package-name must
be specified in the CREATE PROCEDURE (Sourced) statement. If the source-package-name contains
any special or lowercase characters, it must be enclosed by double
quotation marks.
- source-procedure-name
- Identifies the procedure name of the source procedure. If the source-procedure-name contains
any special or lowercase characters, it must be enclosed by double
quotation marks.
- ( )
- Indicates that the number of parameters is zero.
- NUMBER OF PARAMETERS integer
- Specifies the number of parameters for the source procedure. The
minimum value for integer is 0, and the
maximum value is 32 767.
- UNIQUE ID string-constant
- Provides a way to uniquely identify the source procedure when
there are multiple procedures at the data source with the identical
name, schema, and number of parameters. The string-constant value,
which has a maximum length of 128, is interpreted uniquely by each
data source.
- FOR SERVER server-name
- Specifies a server definition that was registered using the CREATE
SERVER statement.
- SPECIFIC specific-name
- Provides a unique name for the instance of the sourced procedure
that is being defined. This specific name can be used when altering,
dropping, or commenting on the sourced procedure. This name can
never be used to invoke the sourced procedure. The
unqualified form of specific-name is an
SQL identifier. The qualified form of specific-name is
a schema-name followed by a period and an
SQL identifier. The specific-name value,
including the implicit or explicit qualifier, must not identify another
procedure instance that exists at the application server; otherwise
an error is returned (SQLSTATE 42710).
The specific-name can
be the same as an existing procedure-name.
If
no qualifier is specified, the qualifier that was used for procedure-name is
used. If a qualifier is specified, it must be the same as the explicit
or implicit qualifier for procedure-name,
or an error is returned (SQLSTATE 42882).
If specific-name is
not specified, a unique name is generated by the database manager.
The unique name is 'SQL' followed by a character timestamp: 'SQLyymmddhhmmssxxx'.
- WITH RETURN TO CALLER or WITH RETURN TO CLIENT
- Indicates where the result sets from the source procedure are
handled. If the source procedure is not from an Oracle data source,
the only one result set is returned to the caller or client; and if
the source procedure is coded to return more than one result set,
only the first result set is returned to the caller or client. The
default is WITH RETURN TO CALLER.
- WITH RETURN TO CALLER ALL
- Specifies that all result sets from the source procedure are returned
to the caller.
- WITH RETURN TO CLIENT
- Indicates which result sets from the source procedure are returned
directly to the client application. The dynamic result set value at
the data source must be greater than 0 for a result set to be returned.
- (result-set-element-number, ...)
- Specifies a non-empty list of result sets to return to the client
application (SQLSTATE 42601). A result-set-element-number identifies
a result set based on the order the result sets are returned, where
1 identifies the first result set, 2 the second result set, and so
on. A result-set-element-number greater
than the total number of result sets returned is ignored. Each result-set-element-number must
be an integer value greater than zero (SQLSTATE 42815), and must not
exceed the value of a small integer constant (SQLSTATE 42820). The
list of result sets to return to the client application must not contain
duplicate values and must be specified in ascending order (SQLSTATE
42815). Result sets are always processed in the order they are returned
from the source procedure.
Result sets that are not identified in
the list to return to client application are returned to the caller.
Note: This
list of result sets to return to the client application must only
be used with source procedures that are known to consistently return
result sets that are intended for the client in the same position
in the list of result sets each time they are executed. It is possible
for a source procedure to return different sets of result sets each
time it is executed, depending on the internal logic of the procedure.
If this is the case, then specify either WITH RETURN TO CALLER ALL
or WITH RETURN TO CLIENT ALL instead, and code the application to
handle this case.
- ALL
- Specifies all result sets from the source procedure are returned
to the client.
- NO SQL, CONTAINS SQL, MODIFIES SQL DATA, READS SQL DATA
- Specifies the classification of SQL statements that can be run by this procedure, or any routine
that is called by this procedure. The database manager verifies that the SQL statements issued by
the procedure and all routines that are called by the procedure are consistent with this
specification.
For the
classification of each statement, see SQL
statements that can be executed in routines and triggers.
Because
the source procedure for the sourced procedure is not on the federated server, the specified level
is not enforced during execution of the source procedure at the data source. If there is discrepancy
between what is specified for the sourced procedure and what the source procedure actually does at
the data source, data inconsistency might occur.
If this option is not explicitly specified,
the value for the source procedure is used.
If this option is explicitly specified but does
not match the value for the source procedure, an error is returned (SQLSTATE
428GS).
If this option is not available at the data source, the default is MODIFIES
SQL DATA.
- NO SQL
- Specifies that the procedure can run only SQL statements with a data access classification of NO
SQL. (SQLSTATE 38001).
- CONTAINS SQL
- Specifies that the procedure can run only statements with a data access classification of
CONTAINS SQL or NO SQL (SQLSTATE 38003 or 38004).
- MODIFIES SQL DATA
- Specifies that the procedure can run any SQL statement except statements that are not supported
in procedures (SQLSTATE 38003).
- READS SQL DATA
- Specifies that the procedure can run statements with a data access classification of READS SQL
DATA, CONTAINS SQL, or NO SQL (SQLSTATE 38002 or 38003 or 42985).
- DETERMINISTIC or NOT DETERMINISTIC
- Specifies whether the sourced procedure always returns the same
results for given argument values (DETERMINISTIC), or whether the
sourced procedure depends on some stated values that affect the results
(NOT DETERMINISTIC). A DETERMINISTIC sourced procedure must always
return the same result from successive invocations with identical
inputs. This clause currently does not impact the processing of the
procedure. If this option is not explicitly specified, the value for
the source procedure is used. If this option is not available at the
data source, the default is NOT DETERMINISTIC. If this option
is explicitly specified, but does not match the value for the source
procedure, an error is returned (SQLSTATE 428GS).
- EXTERNAL ACTION or NO EXTERNAL ACTION
- Specifies whether the sourced procedure takes some action that
changes the state of an object that is not managed by the database
manager (EXTERNAL ACTION), or does not (NO EXTERNAL ACTION). If the
NO EXTERNAL ACTION clause is specified, the federated database uses
optimization that assumes that the sourced procedure has no external
impact. If this option is not explicitly specified, the value for
the source procedure is used. If this option is not available at the
data source, the default is EXTERNAL ACTION. If this option is explicitly
specified but does not match the value for the source procedure, an
error is returned (SQLSTATE 428GS).
Rules
- If the source-object-name, along with
the NUMBER OF PARAMETERS and UNIQUE ID clauses do not identify a procedure
at the data source, an error is returned (SQLSTATE 42883);
if more than one procedure is identified, an error is returned (SQLSTATE
42725).
- If the UNIQUE ID clause is specified and the data source does
not support unique IDs, an error is returned (SQLSTATE 42883).
Notes
- Before a federated procedure can be registered for a data source,
the federated server must be configured to access that data source.
This configuration includes: registering the wrapper for the data
source, creating the server definition for the data source, and creating
the user mappings between the federated server and the data source
server for the data sources that require user mapping.
- Creating procedures that are initially
invalid: If an object referenced in the procedure body does
not exist or is marked invalid, or the definer temporarily doesn't
have privileges to access the object, and if the database configuration
parameter auto_reval is not set to DISABLED,
then the procedure will still be created successfully. The procedure
will be marked invalid and will be revalidated the next time it is
invoked.
- Unlike SQL and external procedures
defined at the federated server, federated procedures do not inherit the special registers of the
caller, even those whose remote-object-name refers to a procedure on a DB2® data
source.
- If the definition of the source procedure is changed (for example,
a parameter data type is changed), the federated procedure should
be dropped and recreated; otherwise, errors might occur when the federated
procedure is invoked.
- If the length of the source procedure parameter is longer than
128, the parameter name of the federated procedure is truncated to
128 bytes.
- Compatibilities:
The DataJoiner syntax
for Create Stored Procedure Nickname is not supported. Parameter type
mapping is handled similarly to nicknames: A catalog look-up determines
the remote data type. The local parameter type is determined through
forward type mapping.
Examples
- Example 1: Create a federated procedure named FEDEMPLOYEE
for an Oracle procedure named EMPLOYEE, using the remote schema name
USER1, the remote package name P1 at the federated server S1, and
returning the result set to the client.
CREATE PROCEDURE FEDEMPLOYEE SOURCE USER1.P1.EMPLOYEE
FOR SERVER S1 WITH RETURN TO CLIENT ALL
- Example
2: Create a federated procedure named FEDSALARYSTAT for an Oracle
procedure named SALARYSTAT, using the remote schema name USER1, the
remote package name P1 at the federated server S1, and returning the
first and the third result set to the client, and remaining result
sets to the caller.
CREATE OR REPLACE PROCEDURE FEDSALARYSTAT SOURCE USER1.P1.SALARYSTAT
FOR SERVER S1 WITH RETURN TO CLIENT(1,3)