Remote Procedure Calls

Intro to Remote Procedure Calls

What are remote procedure calls? Remote procedure calls are typically used to permit a stored procedure on one Sybase server to be invoked from another Sybase server. Sybase RPC's can also be used as an efficient way for a client to call a dataserver. The following discussion concentrates on server to server RPC's.

Note, Sybase RPC's are different from regular operating system RPC's. Sybase RPC's only work with the Sybase clients and servers. Sybase RPC's also permit multiple rows of data to be returned to the client.

RPC Syntax and Examples

The syntax is just like a regular procedure call, with an extra server option:

exec [@return_status] = [server.database.owner.]procedure_name {param}, {param}, ...

For example:

declare @retstat int exec @retstat = myserver...myproc 1, 3

This example performs a remote procedure call from the current server to the server "myserver". It uses the default database and owner on the remote machine.

Another example:

declare @retstat int exec @retstat = myserver.mydb.joe.myproc 1, 3

This example performs an rpc to myserver, calling myproc stored procedure owned by joe in database "mydb".

In this example we are using the stored procedure parameters to pass data to the remote procedure. Remember that it's also possible to have stored procedure paramaters which return data.

Note that the server administrator has to perform some setup before one server can send an RPC to another server.

Example of RPC With "out" Parameters

/* First lets define our stored procedure */ create procedure myproc @param1 int, @param2 int out as begin .... if {error_condition} begin select @param2=null return 344 end select @param2=count(*) from .... end /* next lets execute an RPC call in a Sybase server */ declare @mystatus int declare @table_cnt int exec @mystatus = myproc @param1=2839, @param2=@table_cnt output if @mystatus != 0 {do error processing}

Who's Calling Who Here

Let's say you log on to a Sybase server and invoke a remote procedure call. What specifically happens when you "invoke" the remote procedure.

Let's say you're logged onto Sybase server named "server_a". You submit a text buffer which looks like:

exec @retstat=server_b...myproc 1, 2

These set of characters is sent from the client to the server_a. Server_a then executes the exec command.

Server_a then logs into server_b (using you Sybase login name and password) and calls myproc on server_b.

When myproc returns, the batch of SQL statements that you are executing on server_a continues after the RPC.

Any "out" RPC parameters are assigned to the corresponding variables in the SQL batch on server_a.

What About Select's in the RPC

If the remote procdure (in this case "myproc"), performs a non- assignment select, where will the output go?

The select output will go back to the original client. This is true for any other type of output to client statements, including print and raiserror.

For example, if a stored procedure calls another stored procedure, which performs an RPC, which calls another stored procedure, and so on, if any of the stored procedures perform a "select" statement, the output will be returned to the original invoking client.

This "select output back to the client" feature differentiates Sybase's RPC's from other Unix RPC's. Most Unix RPC implementations are a simply a means of letting one procedure call a remote subprocedure as if it were just a local subroutine.

tbd: figure - remote procedure calls with select

RPC's Directly From the Client

What does this mean and do you care?

Normally, for a client to invoke a stored procedure, the client sends a string buffer like:

exec myproc {param}, {param}, ...

to a Sybase server. The Sybase server performs the following steps:

Processes the string
Determines if it's a stored procedure call
Performs the stored procedure call
Returns the select results to the client (if any).
The stored procedure returns to the calling SQL batch, also returning any out parameters to the calling SQL batch.

With a remote procedure call directly from the client, the following occurs:

The client sends the remote procedure call to the Sybase server. The parameters are sent along with the RPC in an internal format.
The server executes the stored procedure, returning results (if any) to the client.
The stored procedure returns to the calling client, also returning any out parameters defined in the stored procedure.

The main advantage here is that the Sybase server doesn't have to analyze a text buffer to know it's a stored procedure call. Also, the "in" stored procedure parameters can be sent in internal binary format (instead of ASCII) from the client to the server. For example, compare the two representations of the number 1,234,567,890:

Binary (4 bytes): 01001001 10010110 00000010 11010010 ASCII(10 bytes): 00110001 00110010 00110011 00110100 '1' '2' '3' '4' 00110101 00110110 00110111 00111000 '5' '6' '7' '8' 00111001 00110000 '9' '0'

As we shall later see, remote procedure calls are useful with Openservers. We'll discuss this in the next chapter.

An Example RPC Application

One possible use of RPC's involves the distribution of data from one server to another.

A typical data distribution scenario involves a satellite table being kept in synch with a central table.

"It would be nice" if the satellite table could be automatically kept up to date with the central table.

What tools do we have to automatically do something whenever a table is inserted, updated, or deleted? Answer

So how about the following:

We setup a set of triggers on the central table for insert, update, and delete.
We code the triggers so that they use RPC's to call stored procedures on the satellite server.
The stored procedures on the remote server modify the satellite table on behalf of the triggers.

This is a good teaching application, but there are several problems with the design for a production application. Specifically, what happens if the satellite server is down. The choices are:

Rollback the original insert/update/delete operation.
To have the trigger intelligently catch the RPC return error status. The trigger would then have to keep the pending update in a local table. A separate process would periodically have to try and transfer the data from the pending update table to the satellite server. Ugly.

What happens if the trigger is part of a transaction that is rolled back?

Bummer, remote procedure calls do not pay attention to transaction commits.

What happens if the satellite server is running very very slow?

The update on the central table will appear to run very very slowly.

What happens if there is no corresponding data to perform the update on in the satellite server?

Perform the special "sp_haltandcatchfire" command.

Seriously, there will need to be a process that periodically synch's up the tables. While this is conceptually simple, implementing it in a production environment is not easy.

In Sybase Release 10, there is product called The Replication Server. This product performs exactly this function (but doesn't use triggers). If the satellite server is down, updates are buffered until the satellite server comes back up.

What's more, the Replication Server doesn't just replicate individual insert/update/deletes to a single table. It actually replicates whole transactions.

The replication server uses transactions logs to replicate data. For more info on transaction logs see Recovery Issues

Next chapter.