Data aging is controlled with SQL statements that define how deletion of data is handled during replication. You can perform data aging per session or transaction.
Overview of data aging
When an application is ready to age data, it invokes a delete capture mode in the solidDB® server. The delete capture mode disables the propagation of deletes from the frontend to the backend. The delete capture mode is set with the SQL statement SET [TRANSACTION] DELETE CAPTURE NONE; starting from the next transaction, data changes (deletes) are not propagated on that session or transaction.
After the data has been removed with DELETE statements, the delete capture mode is set back to normal. To prevent deleting the data from the backend database, refreshes from the frontend to the backend are blocked permanently for those tables that have had rows aged.
Data aging architecture
1 Data aging mode is enabled with the SET DELETE CAPTURE NONE statement and data is deleted with DELETE statements.
▪Mirroring of DELETE statements is disabled.
▪Refresh is disabled permanently for tables in which data has been aged.
2 Rows that were deleted in the frontend database are preserved in the backend data server.
solidDB® behavior during data aging
There are two data capture modes that can be set in the solidDB® server:
▪SET [TRANSACTION] DELETE CAPTURE NONE: Data aging mode is set on: starting from the next transaction, data changes (deletes) are not propagated on this session or transaction.
▪SET [TRANSACTION] DELETE CAPTURE CHANGES: Data aging mode is set off: starting from the next transaction, data changes (deletes) are propagated on this session or transaction.
If the data capture mode is set for a session, it is valid until it is changed back to a normal mode (DELETE CAPTURE CHANGES).
To preserve consistency in the frontend and backend databases, execution of certain solidDB® statements is restricted during data aging. Other database operations, such as transaction logging or database recovery are not affected.
When the session or transaction is in the DELETE CAPTURE NONE mode, the solidDB® server behaves as follows:
▪INSERT and UPDATE statements are not allowed.
This is because transactions containing either one or both would produce potentially inconsistent results in the backend database. The same restriction applies to statements executed in procedures and triggers. Executing INSERT and UPDATE statements produces an error.
▪DELETE statements are allowed.
Because deleted rows are not propagated to the backend, any portion of the frontend data can be deleted without the danger of removing it from the backend database.
▪All other statements, except DDL statements, are executed normally. DDL statements are not allowed.
▪The DELETE CAPTURE NONE mode affects only the connection or connections that it is enacted on; database operations performed on other frontend connections (sessions) are not affected.
▪The DELETE CAPTURE NONE mode does not affect normal transaction logging, database recovery, or High Availability (HotStandby) operation.
Data aging and solidDB® High Availability
If data aging is taking place and HotStandby failure occurs, the active aging transactions (transactions with DELETE statements) are aborted. The aborted transactions need to be executed again. Otherwise, the behavior depends on the connectivity mode:
▪Basic Connectivity: In Basic Connectivity, the application has to reconnect to the new Primary database and set the DELETE CAPTURE NONE mode for the session.
▪Transparent Connectivity:
– If the failure transparency level (TF_LEVEL) is set to SESSION, the application may continue without any preparation steps.
– If the failure transparency level (TF_LEVEL) is set to CONNECTION, the application has to set the delete capture mode of the session before continuing.
– Load balancing does not have effect on data aging.
IBM InfoSphere CDC behavior during and after data aging
Subscriptions where solidDB® is a source datastore
Refreshes of aged tables from frontend to backend are blocked.
To be able to remove data from the frontend but preserve it in the backend, refreshes from the front end to the backend are blocked for those tables that have had rows aged. This is because when you start a refresh on a subscription, IBM InfoSphere CDC sends a complete copy of the data in the source tables to the target tables. If refreshes were allowed, the data that was deleted from the frontend would also be deleted in the backend.
Note Both the Refresh feature and the automatic refresh performed in the beginning of Mirroring are blocked.
Subscriptions where solidDB® is a target datastore
Refreshes and mirroring from the back end to frontend are not affected. This means that data that was deleted from the frontend could be returned to the frontend when Refresh or Mirroring from backend to frontend is started.
To prevent this kind of recursion of data from the backend to the frontend:
▪Design your subscriptions and table mappings in such a way that data that can become obsolete in the frontend is not mapped to be propagated from the backend to the frontend.
▪Do not use Mirroring in the subscriptions from the backend to the frontend.
If you want to return aged data back to the frontend, you need to perform a Refresh from the backend to the frontend. This also removes the blocking of refreshes from frontend to backend for those tables that have been aged.
