Using the Aging stored procedure

Universal Cache User Guide : Tools and utilities : SQL stored procedures for data aging and refresh : Using the Aging stored procedure

The Aging procedure SQL_START_AUTOMATIC_AGING is an SQL stored procedure that executes user-defined DELETE statements in the solidDB^® database. The user defines the aging rules in the form of DELETE statements which are maintained in a table AUX_AUTOMATIC_DELETES. The AUX_AUTOMATIC_DELETES table is created automatically by the procedure.

Aging rules

You create and modify the aging rules in the AUX_AUTOMATIC_DELETES table using normal SQL statements. The rules can be removed, added, or changed at runtime.

Column	Data type	Description
id	INTEGER PRIMARY KEY	Identifier for the aging rule
statement	LONG VARCHAR NOT NULL	The value must be a full DELETE statement. Any other statement will cause the procedure to fail. Only one statement is allowed per row.
exec_period	INTEGER NOT NULL	Defines the aging interval in seconds
next_exec_date	TIMESTAMP	Defines the next time the rule is executed The procedure calculates the value by adding the value of exec_period to the current time of the execution. If the user gives the value when creating the rule, the first delete operation will take place at the time specified. If the value is not given, the statement will be executed at the next available opportunity.

Any type of DELETE statement can be used as the aging rule. Each row in the AUX_AUTOMATIC_DELETES table corresponds to a single rule. Several rules may be inserted in the table, each executing with its own frequency.

The formulation of the rules depend on the application design. Two examples are described below:

▪Example 1: aging rule is based on a column that contains information on the aging state

If in a table called ‘table_1’ the rows to be aged can be identified by value ‘DONE’ in a column ‘state’, the rule statement would be:

DELETE FROM table_1 WHERE state=’DONE’;

▪Example 2: aging rule is based on the date

If in a table called ‘table_2’, all those rows can be aged for which the date is older that the current date, the rule statement would be:

DELETE FROM table_2 WHERE DATE<CURDATE();

Procedure lifecycle

The procedure does not have any parameters. It runs an internal loop: at each iteration, it reads the rules, executes the applicable rules, and then calculates and updates the next execution time of a rule by adding the value (in seconds) of exec_period to the current execution time. By default, the procedure sleeps for 1 second between each iteration. The sleep interval may be changed by editing the procedure code.

The procedure is typically run as a background job. The exit mechanism is based on a table created by the procedure, called AUX_AUTOMATIC_DELETES_BREAK. At each iteration of the internal loop, the procedure checks whether there are any rows in the AUX_AUTOMATIC_DELETES_BREAK table. If there is at least one row in it, the procedure exits. At the next startup, the procedure removes all the rows from the AUX_AUTOMATIC_DELETES_BREAK table.

Column	Data type	Description
break	INTEGER	Any existing rows causes the aging procedure to exit

Scripts for creating and running the Aging procedure

The solidDB^® package includes SQL scripts for creating and running the stored procedure. The scripts are available in the procedures directory under the solidDB^® installation directory.

Script	Usage
create_automatic_aging.sql	Creates the stored procedure
start_automatic_aging.sql	Calls the stored procedure
stop_automatic_aging.sql	Stops the stored procedure

Creating the Aging procedure

To create the Aging procedure:

1 If there are any backend to frontend subscriptions involving the tables that are to be aged in the frontend, remove or stop those subscriptions.

Alternatively, the databases could be designed in such a way that IBM InfoSphere CDC row filtering can be used to prevent recursion of aged data. For an example, see Example: Automating data aging for bidirectional subscriptions.

2 Create the procedure by running the script create_automatic_aging.sql. For example, solsql can be used to run the script.

solsql -f "C:\solidDB^®\procedures\create_automatic_aging.sql" "tcp 2315" dba dba

Starting and running the Aging procedure

After you have created the procedure, you need to start the procedure and define the aging rules. The aging rules can also be modified at runtime.

1 Start the aging procedure.

2 Run the script start_automatic_aging.sql.

This will start the aging procedure in the background. or include the start_automatic_aging.sql script at the solidDB^® server startup, using the -x executeandnoexit command-line option.

solid -x executeandnoexit:start_automatic_aging.sql

3 Define the aging rules by populating the AUX_AUTOMATIC_DELETES table.

For example, to age data in the table ‘table_1’ based on the value of the ‘state’ column every 5 seconds, issue the following command:

INSERT INTO aux_automatic_deletes (id, statement, exec_period) values (1, ’DELETE FROM table_1 WHERE state=’’DONE’’’, 5);
COMMIT WORK;

Stopping the Aging procedure

The Aging procedure can be stopped in the following ways:

▪Run the script stop_automatic_aging.sql.

▪Add a row in the AUX_AUTOMATIC_DELETES_BREAK table by issuing the following command:

INSERT INTO aux_automatic_deletes_break (1); COMMIT WORK;

▪Use the ADMIN COMMAND ’backgroundjob’ command to control the procedure.