Federated procedures for data sources

Before you create a federated procedure, review how to specify options in the CREATE PROCEDURE statement.

When you create federated procedures, keep these issues in mind:

Database procedures support the IN, OUT, and INOUT parameters.
You must specify same options that the database procedure specifies for the SQL access, deterministic, and external action clauses of the CREATE PROCEDURE statement.
If two remote database procedures have the same name, use the NUMBER OF PARAMETERS option to identify the procedure that you want to use.
The federated database does not issue a COMMIT statement for federated procedures that are created for procedures in Db2® for z/OS® and that contain a COMMIT ON RETURN YES clause.

Quick start guide

How to call a stored procedure in remote Db2 data source by using Db2 federation.

Example

This example shows how to use the CREATE PROCEDURE statement to create a federated procedure for a data source procedure on a database. You can issue the CREATE PROCEDURE statement from the Db2 command line or create a federated procedure in IBM® Data Studio.

CREATE PROCEDURE PROC1 SOURCE KELLER.PROC1_DB2
     NUMBER OF PARAMETERS 3 FOR SERVER DB2_SERVER
     SPECIFIC MYPROC1 WITH RETURN TO CLIENT ALL
     MODIFIES SQL DATA DETERMINISTIC EXTERNAL ACTION;

PROC1: Required. Specifies the name of the federated procedure.
SOURCE KELLER.PROC1_DB2: Required. Specifies the schema and name for the database procedure. For database procedures, you specify a two-part name in the CREATE PROCEDURE statement. The format for this two-part name is source_schema_name.source_procedure_name.
NUMBER OF PARAMETERS 3: Specifies the total number of IN, OUT, and INOUT parameters that the database procedure uses. Use this parameter when you have more than one procedure with the same schema name and procedure name. For example, if your schema is KELLER and you have a PROC1 procedure with three parameters and another PROC1 procedure with one parameter, the name for both of these procedures is KELLER.PROC1. The value for the NUMBER OF PARAMETERS in the data source procedure indicates which procedure you refer to in the CREATE PROCEDURE statement.
FOR SERVER DB2_SERVER: Required. Specifies a server definition where the federated procedure is created.
SPECIFIC MYPROC1: Specifies a unique name for the federated procedure that you are creating. This parameter is used only for federated procedures and is not associated with data source procedures. If you do not specify a unique name, a name is generated by the federated database manager.
WITH RETURN TO CLIENT ALL: Specifies that the result set is returned to the client application. Federation returns a maximum of one result set. If this parameter is not specified, the default is WITH RETURN TO CALLER ALL.
MODIFIES SQL DATA: Indicates the level of data access for SQL statements that are included in the federated procedure. If the clause specified does not match the database procedure, an error message is returned. If you do not specify this clause, the clause for the database procedure is used.
DETERMINISTIC: Specifies if the federated procedure always returns the same results for a given set of argument values. This parameter can improve the performance of the interaction between the federated server and the data source. If the clause specified does not match the Db2 procedure, an error message is returned. If you do not specify this clause, the clause for the database procedure is used.
EXTERNAL ACTION: Specifies if the federated procedure takes an action that changes the state of an object that is not managed by the database manager. If the clause specified does not match the database procedure, an error message is returned. If you do not specify this clause, the clause for the database procedure is used.