Replication to SPL routine

At target participant, ‘replication to SPL routine’ type replicate definition causes SPL routine to be executed instead of applying data to target table. Target participant for “replication to SPL routine” replicate definition can be configured to be same as source database, different database on the same server, or remote peer Enterprise Replication server. “Replication to SPL routine” replicate definition does not enforce the requirement to have primary key, unique index or ER key on the replicated table.
Note: Even though data is applied to stored procedure routine, target table definition must exist.

|--+--splname=spl_routine_name----+-------+---------------------+--------->
   |                              |       |               .-y-. |   
   
'--'--jsonsplname=spl_routine_name-'      '- -- cascaderepl=-+-n-+-'
Long Form Meaning
--splname Stored procedure routine name to apply data to. SPL routine must exist at all participants. Column list for SPL routine extracted from replicate participant select statement column projection list.
--jsonsplname Stored procedure routine name to apply data to. SPL routine and table definition must exist at all participants. Input argument for SPL routine must be a JSON document. --jsonsplname option is mutually exclusive to --splname option.
--cascaderepl Enable cascade replication. Required if replication to SPL needs to be executed for the data applied through Enterprise Replication.

--splname option stored procedure argument list:

  • Optype char(1) – operation type. Values include
    • I – Insert
    • U – Update
    • D – Delete
  • Soucre_id integer – Source server id. Same as group id.
  • Committime integer – Transaction commit time.
  • Txnid bigint – Transaction id.
  • userid - Userid of the user executing the IUD operation.
  • session_id - Session id of the session executing the IUD operation.
  • Before value column list.
  • After value column list.
Note: Column list for SPL routine extracted from select statement projection list

--jsonsplname option SPL routine json argument

Attribute Name Description
operation Operation type: Insert/Delete/Update
table Table name
owner Table owner
database Database name
txnid 8 byte unique id. Higher order 4 bytes: commit work log id, lower order 4 bytes: commit work log position.
operation_owner_id User id of the user executing the IUD operation
operation_session_id Session id of the session executing the IUD operation
commit_time Transaction commit time for the event data.
rowdata Row data in JSON document format. Data is returned in column name as key and column data as value.
before_rowdata Before row data for “update” operation.

Example: JSON Document format

{“operation”:"insert",”table”:"creditcardtxns",”owner”:"informix",”database”:"creditdb",”txnid”:2250573177224,
”operation_owner_id”:201,”operation_session_id”:200,”commit_time”:1488243530,rowdata”:{“uid”:22,”cardid”:"6666-6666-6666-6666",
”carddata”:{"Merchant":"Sams Club","Amount":200,"Date":2017-05-01T10:35:10.000Z } }}

{“opertion”:"update",table:"creditcardtxns",”owner”:"informix",”database”:"creditdb”,”txnid”:2250573308360,
”operation_owner_id”:201,”operation_session_id”:202,”commit_time”:1488243832,”rowdata”:{uid:21,cardid:"7777-7777-7777-7777",
”carddata”:{"Merchant":"Sams Club","Amount":200,"Date":"25-Jan-2017 16:15"} },
”before_rowdata”:{“uid”:21,”cardid”:"6666-6666-6666-6666",”carddata”:{"Merchant":"Sams Club","Amount":200,"Date":2017-05-01T10:35:10.000Z  } }}

{“opertion”:"delete",”table”:"creditcardtxns",”owner”:"informix",”database”:"creditdb",”txnid”:2250573287760,
”operation_owner_id”:201,”operation_session_id”:203,”commit_time”:1488243797,rowdata”:{“uid”:22,”cardid”:"6666-6666-6666-6666",
”carddata”:{"Merchant":"Sams Club","Amount":200,"Date":2017-05-01T13:35:06.000Z } }}

Asynchronous post commit trigger support

  • Define loopback replication server. For more information, see Loopback Replication
  • Create ‘replication to SPL’ type replicate with same “database and table” information for both source and target participants. Loopback server group name shall be specified with target participant definition

Example

cdr define replicate txn_repl -C always -S row -M group_1 -A -R --splname=logger4repl2spl 
"stores@group_1:informix.transactions" "select * from transactions" "test@lb_group_1:informix.transactions" "select * from transactions”
Note: group_1 is the local server ER group, and lb_group_1 is the pseudo ER server group for loop back replication.

Prerequisites

  • Enable Login for the tables.
  • Ensure not to combine participant definitions that include table as the target, and SPL routine as the target.
  • Define target table.
  • Out-of-row data datatypes, TEXT, BYTE, BLOB, CLOB are not supported.

Example 1: build staging table for data changes using --splname replicate attribute

create database storesdb with log;

create table transaction (uid int, bill_amount int);

create table changelog (optype int, srcdir int, committime int, txnid bigint, userid int, session_id int, uid_bef int,
 bill_amount_bef int, uid int, bill_amount int);

create procedure logger4repl2spl (opType char(1), srcid int, committime int, txnid bigint, userid int, session_id int,
uid_bef int, bill_amount_bef int, uid int, bill_amount_bef int) 

insert into changelog values (opType, srcid, committime, txnid, userid, session_id, uid_bef, bill_amount_bef, uid, bill_amount);

end procedure;
Note: Require new code section for these shell commands:
$ cdr define replicate txn_repl -C always -S row -M group_1 -A -R --splname=logger4repl2spl 
"storesdb@group_1:informix.transaction" "select * from transaction" 
"storesdb@lb_group_1:informix.transaction" "select * from transaction”

$ cdr start replicate txn_repl

Example 2: build staging table for data changes using --jsonsplname replicate attribute

create database storesdb with log;

create table transaction (uid int, bill_amount int);

create table inventory (inv_id int, inv_count int);

create table staging (data json);

create procedure logger4repl2spl (data json) 
   insert into staging values (data);
end procedure;

$ cdr define replicate inv_repl -C always -S row -M group_1 -A -R --jsonsplname=logger4repl2spl
"storesdb@group1:informix.inventory" "select * from inventory" "storesdb@lb_group_1:informix.inventory" "select * from inventory”

$ cdr start replicate inv_repl

$ cdr define replicate txn_repl -C always -S row -M group_1 -A -R --jsonsplname=logger4repl2spl 
"storesdb@group_1:informix.transaction" "select * from transaction" 
"storesdb@lb_group_1:informix.transaction" "select * from transaction”

$ cdr start replicate txn_repl

Example 3: Realtime aggregation framework

create database retaildb with log;

create table sales (customerid int, storeid int, bill_amount float);

create table sales_summary(storeid int , s_count int, s_sum float, s_avg float, s_min float, s_max float);

create procedure store_agg(opType char(1), srcid int, committime int, txnid bigint, userid int, session_id int,
customerid_bef int, storeid_bef int, bill_amount_bef float, customerid int, storeid_aft int, bill_amount float)
---- -----
----  ----
end procedure;

$ cdr define replicate sale_repl -C always -S row -M group_1 -A -R --serial --splname=store_agg
"retaildb@group_1:informix.sales" "select * from sales" 
"retaildb@lb_group_1:informix.sales" "select * from sales"

$ cdr start replicate sale_repl
Note: For streaming aggregation, make sure to define replicate with –serial option to avoid executing multiple instances of the same SPL routine in parallel.

Example 4: Calculate leader board on the fly based on changes to scores table

create table scores (playerid int, score int); 
create table leaderboard(playerid int, score int);

create procedure leaderboard_spl(opType char(1), srcid int, committime int, txnid bigint, userid int, session_id int,
playerid_bef int, score_bef int, playerid_aft int, score_aft int)
… ….
end procedure;

$ cdr define replicate game_repl -C always -S row -M group_1 -A -R --serial --splname=leaderboard_spl 
"gamedb@group_1:informix.scores" "select * from scores" "gamedb@lb_group_1:informix.scores" "select * from scores"

$ cdr start replicate game_repl

Define smart trigger on leaderboard table(mentioned in the example given above) and push out changes to application layer.

Note: For more information, see Smart Trigger.