Example: Automating data aging for bidirectional subscriptions
This example describes how the Aging procedure can be used together with IBM InfoSphere CDC row filtering to automate data aging in a bidirectional subscription setup.
If your setup includes bidirectional subscriptions, you must design your applications and subscriptions in such a way that those rows that are removed (aged) from the frontend are not returned back to the frontend when a backend to frontend refresh or mirroring is used.
One possibility is to use the Aging procedure to delete data in the frontend while IBM InfoSphere CDC row filtering is set up to prevent replication of aged rows back to the frontend.
In this example, the application controls which data can be aged by maintaining information about the aging status of the data; it flags the rows to be deleted. The actual deletes of data are performed using the Aging procedure. The IBM InfoSphere CDC row filtering is then set up so that rows which are flagged to be deleted are not replicated from the backend to the frontend.
Example of setting up data aging with bidirectional subscriptions
Note In this example, it is assumed that a new column can be added to the tables. This is not mandatory; depending on the database design, existing columns could be used to identify the aged rows.
1 Set up the environment to support data aging.
▪Add a column “aged” which can contain the value “0” (not aged) or “1” (aged).
▪Design the application to set the rows to be aged to have value 1 in the “aged” column.
2 Create and start the Aging procedure in the solidDB® server.
3 In the backend to frontend subscriptions, set up the row filtering.
Create a row filtering rule that replicates only those rows that have the value <1 in the “aged” column.
4 Create the aging rules by adding DELETE statements in the AUX_AUTOMATIC_DELETES table.
For example, to create a rule that deletes all rows flagged for delete in table_1, execute the following INSERT statement:
INSERT INTO aux_automatic_deletes (id, statement, exec_period) values (1, ’DELETE FROM table_1 WHERE aged=1’, 10);
Result
While the application is running, it flags certain rows in the database for aging (‘aged’ = 1). Those rows are replicated to the backend, together with the changed aging state. As the Aging procedure is running, it deletes the flagged rows from the frontend tables. These rows will not be replicated from the backend to the frontend as the row filtering (‘aged’ < 1) prevents that.
Replicating data from backend to front end
All forms of replication from the backend to the front end are allowed: continuous mirroring, a refresh initiated with the IBM InfoSphere CDC tools, or a refresh initiated by the application by using the Refresh procedure.
However, since IBM InfoSphere CDC replication is asynchronous in nature (the changes made in the frontend are not effective immediately in the backend, and vice versa), the following restrictions apply for this example:
▪If the rows to be aged are being modified in the backend, mirroring from the backend to the frontend is not allowed until the aging activity has finished.
▪If the Refresh procedure is used, ensure that the values in the ‘aging’ column have been replicated to the backend before you issue a refresh. If you issue a refresh before replication has finished, the data you aged in frontend may return to the backend.
You can check the progress of the refresh by viewing the number of refreshed rows in the TS_REFRESH table.
For example:
SELECT * from TS_REFRESH;
SUBSCRIPTION_NAME STATE ERROR_DESCRIPTION INSERTS_PERFORMED