Federated procedure troubleshooting

If you encounter problems with federated procedures, there are several ways that you can troubleshoot the problems.

The following queries and diagnostic tools help you to view information about the federated procedures. This information will assist you in resolving problems with the federated procedures.

Verify data source procedure information

If the SQL1253N error is returned when you issue a CREATE PROCEDURE statement, you can issue the following queries against the catalog tables on the data source to verify information about the data source procedure. The SQL1253N error indicates that the source procedure specified in the CREATE PROCEDURE (Sourced) statement was not found at the data source. You can query the Oracle server directly or use a pass-through session to query the Oracle server.

For procedures in Db2®:

SELECT parm_count, result_sets,
	sql_data_access, deterministic, external_action
	FROM syscat.routines
WHERE routineschema = ''
AND routinename = '' 
AND routinetype = 'P'
AND parm_count = '' <-- optional

For procedures in Db2 for IBM® i:

SELECT in_parms+out_parms+inout_parms,
number_of_results, sql_data_access, deterministic, 
external_action
FROM qsys2.sysroutines
WHERE routine_schema = '' 
and routine_name = '' 
and routine_type = 'PROCEDURE' 
and in_parms+out_parms+inout_parms = ''; <-- optional

For procedures in Db2 for z/OS®:

SELECT parm_count, result_sets,
	sql_data_access, deterministic, external_action
	FROM sysibm.sysroutines
WHERE schema = ''
AND name = '' 
AND routinetype = 'P'

For Microsoft SQL Server

SELECT id
	FROM dbo.sysobjects
		WHERE id = object_id AND
			(TYPE = 'P' or TYPE = 'X')

For Oracle procedures that are in a package:

SELECT owner, package_name, object_name, overload, parm_count 
   FROM (
      SELECT owner, package_name, object_name, overload, 
      SUM(case 
         WHEN data_type IS NULL 
         THEN 0 
         ELSE 1 
         END) 
      AS parm_count
      FROM sys.all_arguments 
      WHERE data_level = 0 
      GROUP BY owner, package_name, object_name, overload 
      ) aa 
   WHERE object_name = '' AND
      package_name = '' AND
      owner = '' AND 
      overload = '' AND   <-- optional
      parm_count =;       <-- optional

For Oracle procedures that are not in a package:

SELECT object_name, object_type, status 
   FROM sys.all_objects 
   WHERE owner = '' AND
     object_name = '' AND 
     object_type IN ('PROCEDURE', 'FUNCTION')

For Sybase procedures:

SELECT id 
   FROM dbo.sysobjects 
   WHERE id = object_id('.') AND
     (TYPE = 'P' OR TYPE ='XP')

Diagnostic tools

Use the Explain utility, the DESCRIBE command, or the db2audit command to diagnose problems with federated procedures.

For example, the FED_PROC1 procedure has three OUTPUT parameters. To use the DESCRIBE command on the FED_PROC1 procedure, issue the following command:

DESCRIBE CALL FED_PROC1(?,?,?);

System monitor

The system monitor elements in the federated database contain information about federated procedures. The monitor elements are as follows:

The Stored Procedure Time monitor element, stored_proc_time, contains the time it has taken the data source to respond to federated procedure statements.
The Rows Returned by Stored Procedures monitor element, sp_rows_selected, contains the number of rows that are sent from the data source to the federated server. You can use this element to calculate the average number of rows sent to the federated server from the data source for each federated procedure or to calculate the average time to return a row to the federated server from the data source.
The Stored Procedures monitor element, stored_procs, contains a count of the total number of procedures that the federated server has called from this data source.

SQL error SQL30090N with return code 21

There are several situations in which the SQL30090N error with return code 21 is returned. One of the most common situations is when a federated procedure is being created using a fenced wrapper. Federated procedures can be created only on trusted wrappers.

Result set not returned

A result set might not be returned to the client or caller for one of the following reasons:

The clause for returning result sets is not specified correctly in the federated procedure.
Some data sources do not return sets in the same order each time a procedure is called. Because federated procedures return only the first result set, a different result set might be returned from the data source when the federated procedure is called.

For example, there are two procedures on the data source, PROCEDURE A and PROCEDURE B. PROCEDURE B calls PROCEDURE A. The statements to create these procedures are:

CREATE PROCEDURE A () 
BEGIN
   DECLARE cur1 CURSOR WITH RETURN TO CLIENT
   FOR SELECT * FROM t;
   OPEN cur1
END

CREATE PROCEDURE B (arg1 INT) 
BEGIN
   DECLARE cur2 CURSOR WITH RETURN TO CLIENT
   FOR SELECT * FROM t;
   IF arg1<10) THEN
      CALL A();
   END IF;
   OPEN cur2
END;

The federated procedure FEDPROC1 references the data source PROCEDURE B. The statement for the FEDPROC1 procedure is:

CREATE PROCEDURE FEDPROC1
SOURCE newton.B
FOR SERVER s1
NUMBER OF PARAMETERS 1
WITH RETURN TO CLIENT 1;

A local procedure calls the federated procedure FEDPROC1. The statement for the local procedure is:

CREATE PROCEDURE local (arg1 INT)
   BEGIN
   CALL FEDPROC1 (arg1) 
END;

When you issue the CALL LOCAL(1) statement, the cur1 result set from PROCEDURE A is returned. The result set cur2 is not returned.

However, if you issue the CALL LOCAL(20) statement, the cur2 result set from PROCEDURE B is returned.

Pass-through session (Oracle only)

If you create a data source procedure, function, or package in a pass-through session, a successful message is returned even if the object definition has an error. The object is created on the Oracle server, but it is marked INVALID. You cannot create federated procedures on INVALID objects. When you attempt to create a federated procedure that references an INVALID Oracle object, the CREATE PROCEDURE (Sourced) statement fails.

Use one of the following methods to determine why an object is not valid:

Use the SHOW ERRORS command in the SQL*Plus utility from Oracle.
Query the Oracle sys.all_errors catalog table.