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.

Table 1. JSON attributes for registering new event conditions:
Input attribute name Description
table Table name to be registered
owner Table owner
database Database name
query SELECT statement including projection list and WHERE clause to register for changes in a data set.
label User defined string to be returned along with an event document. This attribute is useful to differentiate between events when more than one push-data event is registered within the same session
timeout The amount of time a client is blocked in the smartblob read API for an event data. The server returns timeout json document when a timeout condition is triggered.

Supported range of values are:
  • -1 to wait forever
  • >=0 to wait for a specified amount of time in seconds.
commit_time Returns event data that is committed after the stated transaction commit time.
txnid A unique 8 byte ID:
  • Higher order 4 bytes: commit work log ID
  • Lower order 4 bytes: commit work log position
max_pending_ops Maximum number of event records to be kept in the pending session .
maxrecs Maximum number of records to be returned by the smartblob API read call.

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);
Table 2. Event data JSON attributes
Attribute name Description
operation Operation type: Insert/Delete/Update
table Table name
owner Table owner
database Database name
label Optional user-specified data for the event condition
txnid 8 byte unique ID:
  • higher order 4 bytes: commit work log ID
  • lower order 4 bytes: commit work log position.
commit_time Transaction commit time for the event data.
op_num Increasing sequence number for the event document within a given transaction. If the transaction generates 10 events, then each document returned will have an incremental op_num value, starting from 1 to 10.
rowdata Row data in JSON document format. Data is returned using the column name as key and the column data as value.
before_rowdata Before row data for an Update operation.
ifx_isTimeout Document with this attribute is returned with its value set to true if no event gets triggered within the timeout interval that is specified by the client.
ifx_warn_total_skipcount A warning document with this attribute is returned, containing the cumulative number of events that are discarded, from exceeding the max_pending_ops attribute threshold.

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,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,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,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,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.