Advanced Replication Guide : Using advanced replication with applications : Designing and implementing Intelligent Transactions : Using the advanced replication Parameter Bulletin Board
  
Using the advanced replication Parameter Bulletin Board
solidDB® advanced replication introduces a new parameter passing method, “Parameter Bulletin Board” for transactions to use for various purposes. Parameter Bulletin Board is a memory area to which one can add parameters of various kinds and from which the operations (procedures) of the transaction may read those parameters.
There are three different kinds of parameters that appear on the Parameter Bulletin Board.
▪Volatile Transaction Parameters that are used within a transaction for transferring information between the different procedures of the transaction.
▪Transaction Properties (persistent transaction parameters) that are used for giving a transaction some properties (i.e. describing the transaction) when the transaction is created in the replica database.
▪Persistent, catalog-level Sync Parameters that are used for describing the catalog where the transaction is being executed.
Depending on the type of parameter, there is a different way to specify the parameter and a different mechanism for when and how the parameter is put to the parameter bulletin board. However, the mechanism for reading the parameters from the bulletin board is the same for all different types of parameters.
Passing parameters between procedures within a transaction
Procedures of a transaction may communicate with each other by putting volatile parameters on the parameter bulletin board using the PUT_PARAM() function and reading the parameters using the GET_PARAM() function. Each parameter is a name-value pair. If a parameter already exists in the bulletin board, the PUT_PARAM() function replaces the current value with a new one. If a parameter does not exist in the bulletin board, then GET_PARAM() returns NULL.
Below are some examples of writing and reading parameters:
-- Procedure P1 sets a bulletin board sync parameter.
"CREATE PROCEDURE P1()
BEGIN
  PUT_PARAM('CreditLimitExceeded', 'Y');
...
END";
-- Procedure P2 reads the bulletin board sync parameter.
"CREATE PROCEDURE P2()
BEGIN
  DECLARE cred_lim_exceeded CHAR(1);
  cred_lim_exceeded := GET_PARAM('CreditLimitExceeded');
...
END";
Note that you may use GET_PARAM() not only to read the sync parameter values set by the PUT_PARAM() command, but also to read the transaction properties that were set with the SAVE PROPERTY command.
The parameter bulletin board is visible to all statements of the transaction. This allows different stored procedures, for example, to communicate with each other even if they do not call each other. If one procedure detects an error, it can set a flag that will notify subsequent procedures in that same transaction to skip processing of the erroneous data.
The parameters appear on the parameter bulletin board of the transaction when the transaction is executed in the master database. The parameters are also visible to the replica while the transaction is executing on the replica.
In most cases, the transaction properties are used when the transaction executes on the master, that is, after the transaction has been propagated from the replica to the master. However, you may use the values on either the replica or the master (or both).
Note When implementing intelligent transactions for conflict resolution, be sure to set autocommit OFF to prevent losing transaction properties. The lifecycle of a transaction parameter is one transaction; that is, it is visible only in the transaction that has set the value. If autocommit is ON, then each statement is a separate transaction and bulletin board values are lost immediately.
See the solidDB® SQL Guide for details on PUT_PARAM() and GET_PARAM().
Assigning properties to a replicated transaction
Transaction properties are used for describing an entire replicated transaction. These parameters are persistent parameters that are defined in the replica database using SAVE PROPERTY statement and who persist until the transaction to which they are attached, has been successfully propagated to the master database and executed there. The SAVE PROPERTY command stores the parameter for a propagable transaction in the replica database. When the propagated transaction is later executed in the master, these parameters are put to the parameter bulletin board (in the master database) in the beginning of the transaction. Any procedure of the transaction may query the value of this parameter using the GET_PARAM() function.
The transaction properties can be used for two purposes.
▪act as a selection criteria for selecting, which transactions to propagated in a synchronization message
▪internally by the procedures of the transaction when it is executed in the master database.
The syntax for saving a property to a transaction is:
SAVE PROPERTY property_name VALUE property_value
In the excerpt from Create order transaction, the transaction has one saved property with the name “priority” and value “1”.
-- Save a parameter to the transaction SAVE PROPERTY priority VALUE '1';
This parameter can be used as a search criterion of the transaction propagation process (“propagate only those transactions that have parameter ‘priority’ with value ‘1’”). For example:
MESSAGE APPEND PROPAGATE TRANSACTIONS WHERE priority = ’1’;
When the transaction has been propagated to the master database, the values of all defined properties of this transaction appear on the parameter bulletin board of the transaction when the transaction is executed in the master database. Thus, you can query the value of the “priority” property from a procedure using the GET_PARAMI() function.
DECLARE priority_value CHAR(1); priority_value := GET_PARAM('priority');
This information may be used for the application’s own purposes, for example, for determining how a possible update conflict should be resolved with this particular transaction.
Defining catalog-level persistent synchronization parameters
To define a parameter that has a per-catalog scope, you use SET SYNC PARAMETER command. This command specifies a parameter that can be read by any transaction (using the GET_PARAM() function) that executes within that catalog. Each transaction in that catalog can also alter the value of the parameter, but those updated values are only seen within the current transaction. Subsequent transactions do not see the updated values; subsequent transactions only see the “original” values.
Advanced replication system parameters
The advanced replication system itself pre-defines some parameters known as “system parameters”. System parameters are something that the server knows about and can act upon (for example, by terminating transaction). (The generic catalog-scope parameters are something the application-level intelligent transactions must know about. They do not affect the server’s own behavior.) An example of a system parameter is SYS_ROLLBACK. Setting SYS_ROLLBACK to “YES” causes the server to terminate that transaction. The value of SYS_ROLLBACK is reset back to default “NO” for the next transaction. System parameters are merely parameters that use names that are reserved by the system. System parameters are visible on the bulletin board, just like any other parameters. Your transaction can read and write system parameters just as it can read and write other parameters.
See also
Designing and implementing Intelligent Transactions