1.0

CALL (PROCEDURE)
SQLFire extends the CALL statement to enable execution of Data-Aware Procedures (DAP). These procedures
can be routed to SQLFire members that host the required data.
Syntax
CALL [ PROCEDURE ] procedure_name
( [ expression [, expression ]* ] )
[ WITH RESULT PROCESSOR processor_class ]
[ { ON TABLE table_name [ WHERE whereClause ] }
|
{ ON { ALL | SERVER GROUPS (server_group_name [, server_group_name]* )
} }
]
Description
The CALL syntax enables you to specify the SQLFire members that execute the procedure as well as the result
processor that you want to use to process the results from multiple members.
SQLFire has a default result processor that collates results from different members if you do not specify a custom
result processor. The default processor performs unordered merges on the dynamic result sets from each server
where the procedure is executed, and presents the same number of ResultSets to the JDBC client that was declared
in the CREATE PROCEDURE statement.
Using Data-Aware Stored Procedures on page 143 provides more information about developing and configuring
data-aware procedures and custom result processors.
WITH RESULT PROCESSOR
With this clause a custom result processor can be given which collates results and OUT parameters from multiple
servers.
ON and WHERE Clauses
The optional ON and WHERE clauses control the execution of DAPs on specific SQLFire members. If no ON
clause is provided, the procedure is executed in only one server, the coordinator (data-independent). Otherwise,
it is executed on only the servers that are hosting data for the specified table, optionally routed based on a WHERE
clause. If an ON ALL or ON SERVER GROUPS clause is provided, then execution is routed to either all servers
or the servers in the specified server groups.
Example
This call executes the procedure "procedureName" only on those members that belong to
server group "sg2:"
CALL procedureName() ON SERVER GROUPS (sg2)
This call executes the procedure on members where values of ID are in the range 'ID >= 20
and ID < 40'. ID should be the partitioning column, otherwise SQLFire routes the procedure
execution to all members that host data for the table:
CALL procedureName() ON TABLE EMP.PARTITIONTESTTABLE WHERE
ID >= 20 and ID < 40")
This call executes the procedure on all members (both accessors and data hosts):
CALL procedureName() ON ALL
435
SQL Language Reference