Q Replication and Event Publishing Q Replication and Event PublishingTypically in Q subscriptions, data from a source table is mapped to a target table; however, for Q subscriptions in unidirectional replication, you can instead have the Q Apply program call a stored procedure and pass the source data as input parameters to the stored procedure.
The source columns map to parameters in the stored procedure instead of to target columns. By mapping source columns directly to parameters in a stored procedure, you avoid the need to parse the incoming data and have a clean, simple programming model.
When you specify that you want the target of a Q subscription to be a stored procedure, you still have all of the options that you have for a Q subscription with a target table. For example, with a stored procedure, you still choose error options.The stored procedure can refer to either a DB2 table or a nickname in a DB2 federated database.
The stored procedure returns a return code to the Q Apply program that indicates whether the data was applied to the target table. The developer that creates the stored procedure must ensure that the stored procedure returns an appropriate SQL return code to the Q Apply program. The stored procedure is responsible for getting the source data to its final destination.
CREATE PROCEDURE storedprocedure_name(
INOUT operation integer,
IN suppression_ind VARCHAR(size),
IN src_commit_lsn CHAR(10) FOR BIT DATA,
IN src_trans_time TIMESTAMP,
XParm1,
Parm1,
XParm2
Parm2,
Parm3,
Parm4,
Parm5
)See the sample programs for examples of stored procedures for Q Replication that are written in C and in SQL.
The stored procedure must not perform COMMIT or ROLLBACK functions because the Q Apply program commits or rolls back transactions.
The Q Apply program handles LOB data for stored procedures similarly to how it handles LOB data for target tables. For Q subscriptions with stored procedures, LOB changes are replicated by sending them in multiple messages, depending on the size of the LOB and the message size that you allowed the Q Capture program to send. The stored procedure call that involves the first LOB message for a single LOB change is the original row operation. All of the remaining LOB data for the single LOB change is sent by additional calls to the stored procedure. The Q Apply program transforms the operation into an update. The suppression_ind index marks all parameters as suppressed, except for the parameter that maps to the LOB column in the source table. The stored procedure is called each time a LOB message is on the receive queue.
If a LOB value is small enough to be sent in a single message, the special update operation with append (operation value 34) is not necessary. The stored procedure writer must write logic to handle each operation code as well as column suppression; the Q Apply program has all other logic for handling LOB values.
If a LOB value is too big to be sent in one message (because the size exceeds the maximum message size) and, therefore, is split into several messages, the first message is handled differently than the subsequent messages. The Q Apply program appends any portions of a LOB value that are not sent in the first message using the concatenation operation ||. A special operation code is passed to indicate this special form of update.
In a stored procedure that has two LOB columns, C1 and C2, the following would occur in an insert:
| SQL | Operation value |
|---|---|
| INSERT C1 = first portion C2 = some dummy value | 16 |
| UPDATE C1 = C1 || next portion | 34 |
| UPDATE C1 = C1 || next portion | 34 |
| UPDATE C2 = first portion | 32 |
| UPDATE C2 = C2 || next portion | 34 |
| UPDATE C2 = C2 || next portion | 34 |
