Federated procedures for Microsoft SQL Server

Before you create a federated procedure, review which parameters are supported, how result sets are returned, and what limitations exist.

Parameters

Microsoft SQL Server procedures support the use of optional INPUT and OUTPUT parameters. The SQL Server wrapper maps each INPUT parameter to a federated IN parameter and maps each OUTPUT parameter to a federated INOUT parameter. If you use optional parameters in an SQL Server procedure, you must count them when you specify the NUMBER OF PARAMETERS clause of the CREATE PROCEDURE statement.

Result sets

The SQL Server wrapper can return a result set and an OUTPUT parameter. When an SQL Server procedure returns both an OUTPUT parameter and a result set, only the parameter is returned. The result set is discarded, and you receive the SQL0464W error message. The DB2_RETURN_STATUS value is retrieved for procedures that return result sets, but always returns a value of zero (0), regardless of the actual return value from the procedure.

Limitations

The SQL Server wrapper cannot call a procedure in these situations:

When another procedure is already called
When another statement is executed during a single connection

To work around these limitations, you can define the federated procedure on another server. In this example, the following statements succeed if the nickname sql_nick and the procedure sql_proc are defined on the different servers. If the nickname and the procedure are defined on the same server, the statements fail.

DECLARE clientcur CURSOR FOR SELECT colsml,coldec,colvch,coltsp 
FROM sql_nick OPEN clientcur; CALL sql_proc();

Note: DATETIMEOFFSET, NCHAR, NVARCHAR, BINARY, VARBINARY, TEXT, IMAGE, ROWVERSION, SQL_VARRIANT are not supported as federated procedure IN, OUT and INOUT parameters.

Quick start guide

How to call a stored SQL Server procedure 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 Microsoft SQL Server. Note that Microsoft SQL Server does not support the UNIQUE ID clause and does not support the source_package_name value of the SOURCE clause.

CREATE PROCEDURE PROC1 SOURCE BHATIA.PROC1_MSSQL
     NUMBER OF PARAMETERS 5 FOR SERVER MSSQL_SERVER
     SPECIFIC MYPROC1 WITH RETURN TO CLIENT ALL
     MODIFIES SQL DATA DETERMINISTIC EXTERNAL ACTION;

PROC1: Required. Specifies the name of the federated procedure.
SOURCE BHATIA.PROC1_MSSQL: Required. Specifies the name of the schema and procedure on the data source.
FOR SERVER MSSQL_SERVER: Required. Specifies a server definition where the federated procedure is created.
NUMBER OF PARAMETERS 5: Specifies the total number of IN and OUTPUT parameters that the data source procedure uses.
SOURCE BHATIA.PROC1_SQL: Specifies the schema and name for the data source procedure. The format for this two-part name is source_schema_name.source_procedure_name.
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 data source procedure, an error message is returned. If you do not specify this clause, the clause for the data source 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.
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.