It seems there are no (or few?) public samples available, which is a pity.

Nevertheless, I'd ask first what are you trying to achieve?

Basically, a hook procedure is just a procedure with a pre-defined name which is then called by SQL Remote - think of a callback.

What we are using such hook proedures for is basically one thing:

• The need to do DML statements within the contexts of a replication run that are replicated back to the message originator.

That has to do with the following SQL Remote rule:

SQL Remote ensures that statements are not sent to the remote user that initially executed them.

Here's a small sample:

Say, you have remotes that generate some kind of orders, and you want those orders to have a central order number or a central registration time once the order has been applied within the consolidated database. Until the order is applied at the cons, it would just have a provisory order number marked via a field OrderNrIsProvisory.

So you might have a trigger in the cons that runs when a new order is processed and would do an update on the order to make it a "central one", such as

    ...
    update T_Order
       set OrderNr = <...>, OrderNrIsProvisory = 0
       where OrderNrIsProvisory = 1;
    ...

Straightforward, but it won't work as expected within SQL Remote:

If that trigger is run within the contexts of DBREMOTE, it will not sent that UPDATE back to the remote the order was created, since DBREMOTE does not do that unless an update conflict was detected (see the cited ruel). As a consequence, the remote won't have the freshly updated values.

One solution here is to use the trigger for a different purpose: Do not change the row itself but do insert an entry into a help table (say T_NewOrder) that only exists on the cons (and therefore won't be replicated at all).

Then, use a hook procedure like sp_hook_dbremote_receive_end() to process that help table, such as

create procedure sp_hook_dbremote_receive_end()
begin
   for forNewOrder as crsNewOrder cursor for
      select OrderID as NewOrderID from T_NewOrder order by NewOrderID
      for update
   do
      update T_Order
         set OrderNr = <...>, OrderNrIsProvisory = 0
         where OrderNr = NewOrderID and OrderNrIsProvisory = 1;
      delete where current of crsNewOrder;
   end for;
end;

Note, DML actions run within one of the SQL Remote hooks run as normal code, i.e. they don't fall under the "Do not replicate back to the originator" rule. As such, the according UPDATE will be replicated to all according remotes, just as it would have been done in a normal SQL session within the remote.

That's because procedures specified in a hook are called on a separate connection.

HTH.