Federated procedures
A federated procedure is a federated database object that references a procedure on a data source.
Federated procedures are not alternative names for data source procedures in the same way that aliases are alternative names. A federated procedure is defined at the federated database but calls a data source procedure when the federated procedure is invoked. Because the federated procedure is a federated database object, users and client applications can invoke the data source procedure logic by calling a federated procedure. The results of the data source procedure, such as the output parameters, are returned by the federated procedure. Using a federated procedure makes the location of the data source procedure transparent to users and client applications. You use the name of the federated procedure to call the data source procedure.
A federated procedure is to a remote procedure what a nickname is to a remote table. Nicknames and federated procedures are objects on the federated database. A nickname is an object that references an object, such as a table or view, on the data source. With a nickname, you query a data source object. With a federated procedure, you call a data source procedure.
You use the CREATE PROCEDURE (Sourced) statement to register a federated procedure and use the CALL statement to call a procedure. You can embed the CREATE PROCEDURE (Sourced) statement in an application program or issue the statement with dynamic SQL statements.
Parameters and data types
Federation uses three types of parameters: IN, OUT, and INOUT. When you create a federated procedure, the default forward data type mappings are used to map the data types for the parameters for the data source procedure to the federated data types. This mapping includes data types for the input and output parameters and data types for the columns in the result set. You can use the CREATE TYPE MAPPING statement to override the default type mapping for the data source parameters. However, the type mappings of the result set are not affected by user-defined type mappings. You can use all of the data types that are supported for nickname columns except LOB data types. Federated procedures do not support complex data types.
User mappings and authorization
To call a federated procedure, you must have the correct authorizations on the federated procedure and the data source procedure. When you call a federated procedure, the user mapping and privileges of the authorization ID that created the federated procedure are used to access the data source tables.
For example, the user ZELLER creates a federated procedure named FP1. The FP1 procedure references a Sybase procedure that accesses a Sybase table. The remote user ID in the user mapping for ZELLER has the privilege to update the Sybase table. The user ZELLER grants the EXECUTE privilege to the user BHATIA on the FP1 procedure. The user BHATIA must have a valid user mapping to a remote user ID that has EXECUTE privilege on the Sybase procedure that is referenced by the FP1 procedure. The remote user ID that user BHATIA is mapped to does not need to have SELECT privilege on the Sybase procedure. When the user BHATIA calls the FP1 procedure, the user BHATIA can update the table in Sybase.
Procedure calls and access levels
- By default, the CALL RESOLUTION IMMEDIATE clause is used with federated procedures when you issue the PRECOMPILE command. The CALL RESOLUTION DEFERRED clause is not supported with federated procedures.
- You cannot not see output when a federated procedure calls a data source procedure that prints to a buffer or the standard output.
- When you call a federated procedure from an external user-defined function, the federated procedure must not have the access level set to READS SQL DATA or MODIFIES SQL DATA. Federated access is blocked inside external functions.
- The limitations that apply to calling local procedures in pass-through mode also apply to federated procedures.
- Each argument in the CALL statement must be compatible with the corresponding parameter in the procedure. Federated procedures follow the same parameter assignment rules as local procedures.
Transactions
- The data source procedure that the federated procedure references must not issue a COMMIT or a ROLLBACK statement. Federation does not enforce this restriction and data inconsistencies might occur if the data source procedure issues a COMMIT or ROLLBACK statement.
- Federated procedures with the MODIFIES SQL DATA access level cannot be invoked inside of triggers, dynamic compound statements, SQL scalar, tables, row functions, and methods. After a SAVEPOINT statement is issued, you cannot call a federated procedure with the MODIFIES SQL DATA access level.
- Federated procedures do not support WITH HOLD cursors, scrollable cursors, or updatable cursors. If the data source procedure uses these types of cursors, you do not receive a warning or error message. Applications that access result sets with data source procedure that use these types of cursors might behave differently. Typically the cursors are returned without the hold capability and forward read-only cursors.
Catalog views
- SYSCAT.ROUTINES
- SYSCAT.ROUTINESFEDERATED
- SYSCAT.ROUTINEOPTIONS
- SYSCAT.ROUTINEPARMS
- SYSCAT.ROUTINEPARMOPTIONS