Push data feature

Push data feature lets clients register for changes in a dataset using simple SELECT statements and WHERE clauses. Once the server captures data for push data event conditions which evaluates to true for WHERE clause condition, the server pushes committed data to the client, based on registered events. Scaling is achieved by clients not having to poll for data, and not having to parse, prepare, and execute SQL queries. Database servers with parallel architecture – Enterprise Replication log snooper and grouper -- feed the data to all clients by asynchronously reading logical log file changes. This design lets client applications scale linearly without adding significant overhead to the database server or any OLTP applications making changes to the database. Data that is returned to the client is in a developer-friendly JSON format.

Grant replication permission on sysadmin database for the user registering push data events:

execute function task('grant admin', ‘user1’, 'replication');

Register client as a push data session by using the sysadmin task command:

execute function informix.task('pushdata open')

The above command registers the client as a push-data session, and returns a unique session ID. This ID is needed for reading event documents using the smartblob readAPI.

This command also auto-registers enterprise replication, when it has not been defined earlier.

To internally define enterprise replication automatically, the pushdata open command relies on the existence of at least one storagepool entry to create the dbspace and subspace required for defining enterprise replication. You must create a storagepool entry using the task API.

For example:

Execute function task( 'storagepool add', '/informix/storage', '0', '0', '20000', '1' );

Registering one or more push data event conditions using the sysadmin task command:

execute function informix.task('pushdata register', {table:"creditcardtxns",owner:"informix",database:"creditdb",query:"select uid, cardid,
 carddata from creditcardtxns where carddata.Amount::int >= 100",label:"card txn alert"})

Registering session-specific attributes, like timeout, using the pushdata register task command:

execute function informix.task('pushdata register', { timeout:"60",max_pending_ops:"0",maxrecs:"1"})

De-registering one or more registered event conditions using the pushdata deregister command:

To de-register one or more event conditions for the given table:
execute function informix.task('pushdata deregister', {table:"usertable",owner:"informix",database:"ycsb”})

To de-register all event conditions with the same label attribute tag:

execute function informix.task('pushdata deregister', { label:"card txns"})

Note: To deregister a specific event condition, either use the label attribute, or specify a query attribute, along with the table, owner and database attributes.

API to read event data:

The client must invoke the smartblob read API to read an event data. Input for the smartblob read API must include:

• The session ID returned from running the pushdata open task command.
• The input buffer pointer
• The input buffer size—this should be at least equal to the sum of the before image size, the after image size, and 1024 bytes. If multiple records are expected from one read call, then the input buffer size should be equal to the sum of the before image size, the after image size, and 1024, multiplied by the number of records.
• The error code pointer.

ESQLC READ API Example:

/*
         * Read data into the buffer
         */
        bytesread = ifx_lo_read(sessionid, databuf, bytes_per_read, &loreaderr);

Sample output from the smartblob read API for an Insert operation:

{“operation”:"insert",”table”:"creditcardtxns",”owner”:"informix",”database
”:"creditdb",”label”:"card txn alert",”txnid”:2250573177224,”commit_time
”:1488243530,”op_num”:1,”restart_logid”:31,”restart_logpos”:24,”rowdata”:{“uid”:22,”cardid
”:"6666-6666-6666-6666",”carddata”:{"Merchant":"Sams Club","Amount":200,"Date":2017-05-01T10:35:10.000Z } }}

Sample output from the smartblob read API for Update operation:

{“opertion”:"update",table:"creditcardtxns",”owner”:"informix",”database
”:"creditdb",”label”:"card txn alert",”txnid”:2250573308360,”commit_time
”:1488243832,”op_num”:1,”restart_logid”:31,”restart_logpos”:24,”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  } }}

Sample output from the smartblob read API for Delete operation:

{“opertion”:"delete",”table”:"creditcardtxns",”owner”:"informix",”database”:"creditdb",”label”:"card txn alert",
”txnid”:2250573287760,”commit_time”:1488243797,”op_num”:1,”restart_logid”:31,”restart_logpos”:24,”rowdata
”:{“uid”:22,”cardid”:"6666-6666-6666-6666",
”carddata”:{"Merchant":"Sams Club","Amount":200,"Date":2017-05-01T13:35:06.000Z } }}

Sample output from the smartblob read API for a multi-row buffer, when the maxrecs input attribute is set to greater than 1:

{[
{“operation”:"Insert",”table”:"creditcardtxns",”owner”:"informix",”database”:"creditdb",”label”:"card txn alert", “txnid
”:2250573309999,”commit_time”:1487781325,”op_num”:1,”rowdata”:{uid:"7",”cardid”:"6666-6666-6666-6666",”carddata
”:{"Merchant":"Sams Club","Amount":200,"Date":2017-05-01T15:10:10.000Z } }},{“operation”:"insert",table:"creditcardtxns",”owner”:"informix",”database”:"creditdb",
”label”:"card txn alert",”txnid”:2250573177224,”commit_time”:1488243530,”op_num”:1,”restart_logid”:31,”restart_logpos”:24,
”rowdata”:{“uid”:22,”cardid”:"6666-6666-6666-6666",”carddata”:{"Merchant":"Sams Club","Amount":200,"Date":2017-05-01T16:20:10.000Z } }}
]}

Using the sample pushdata ESQL/C program

You can run the pushdata ESQL/C program to safely preview the process of registering event triggers with your Informix server, to retrieve event data in JSON format.

The program file, pushdata.ec, can be found in the /demo/cdc folder of your Informix installation folder.