DB2 10.5 for Linux, UNIX, and Windows

CREATE PROCEDURE (sourced) statement

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)
```