1.1

Table Of Contents

Chapter 25

Using Data-Aware Stored Procedures

A stored procedure is an application function call or subroutine that is managed in the database server. Because multiple

SQLFire members operate together in a distributed system, procedure execution in SQLFire can also be parallelized

to run on multiple members, concurrently. A procedure that executes concurrently on multiple SQLFire members is

called a data-aware procedure.

Data-aware procedures use an extended CALL syntax with an ON clause to designate the SQLFire members on which

the procedure executes. When you invoke a procedure, the SQLFire syntax provides the option to parallelize the

procedure execution on:

• All data stores in the SQLFire cluster

• A subset of data stores (on members that belong to one or more server groups)

• All data store members that host a table

• All data store members that host a subset of data in a table

SQLFire executes the user code in-process to where the data resides, which provides very low-latency access to

colocated data. (This is in contrast to map-reduce job execution frameworks like Hadoop, where data has to be streamed

from processes into a Task process.) Procedures often return one or more result sets. SQLFire streams a result set to

one coordinating member that can perform the reduction step on the results (typically this involves aggregation, as in

map-reduce). In SQLFire, the reduction step is carried out by a result processor. SQLFire provides a default result

processor, and you can also develop your own result processor implementations to customize the reduction step.

The sections that follow describe how to conﬁgure, invoke, and develop stored procedure and result processor

implementations in SQLFire.

Configuring a Procedure

You must conﬁgure a procedure implementation in SQLFire before you can invoke the procedure.

Before you conﬁgure a procedure, ensure that the procedure implemention is available in the SQLFire classloader.

See Storing and Loading JAR Files in SQLFire on page 121.

The syntax for conﬁguring a procedure in SQLFire is as follows. You designate a procedure as data-aware or

data-independent when you invoke it. See Invoking a Procedure on page 155.

CREATE PROCEDURE procedure-name

([ procedure-parameter [, procedure-parameter] * ])

LANGUAGE JAVA

PARAMETER STYLE JAVA

{ NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA }

153