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 informationIf 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.
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
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
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')
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
SELECT object_name, object_type, status FROM sys.all_objects WHERE owner = '' AND object_name = '' AND object_type IN ('PROCEDURE', 'FUNCTION')
SELECT id FROM dbo.sysobjects WHERE id = object_id('.') AND (TYPE = 'P' OR TYPE ='XP')
Use the Explain utility, the DESCRIBE command, or the db2audit command to diagnose problems with federated procedures.
DESCRIBE CALL FED_PROC1(?,?,?);
- 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
- 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.
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;
CREATE PROCEDURE FEDPROC1 SOURCE newton.B FOR SERVER s1 NUMBER OF PARAMETERS 1 WITH RETURN TO CLIENT 1;
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
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 the SHOW ERRORS command in the SQL*Plus utility from Oracle.
- Query the Oracle sys.all_errors catalog table.