Edit

Purge Data Stores

Refer to Schedule Settings to get information about how to set up the data purge for Business Processes.

When your Data Stores size gets rather big (for example, 100k records and more) it can slow down Bot task execution, therefore you need to perform Data Store purging procedure.

This article describes how to perform Data Store purging using a one-step Business Process.

General Use Case

Overview

1. Create a single-step Business Process with a Bot step to purge a specific data store.
2. Specify a condition inside of Bot Step Config.
3. Run the Business Process periodically, when a purge procedure is needed, or create a Schedule for this Business Process.

Data Store

Assume, a data store named as "t_status" has the next structure and is populated with data:

name (type: TEXT)	status (type: TEXT)
one	FINISHED
two	RUNNING
three	FINISHED
four	NEW

We are going to remove all of records with status = "FINISHED".

Performance note

To speed up the table cleaning it's highly recommended to create an index on the "status" column of the data store in the MsSQL database wf_datastore before running the purging BP.

Pay attention: the table name is extended with the prefix "ds_".

create index ds_t_status_IDX_status on ds.ds_t_status(status);

Bot Step Config

Condition to purge: status = 'FINISHED' (row #5)

<?xml version="1.0" encoding="UTF-8"?> <config charset="UTF-8">

    <datastore name="t_status"><![CDATA[ delete from @this where status = 'FINISHED'; ]]></datastore> <export include-original-data="true"></export>

</config>

Run the Business Process

Data Store content BEFORE purging

wf_datastore=# select * from ds.ds_t_status;

 ds_t_status_id | name  |  status
----------------+-------+----------
              1 | one   | FINISHED
              2 | two   | RUNNING
              3 | three | FINISHED
              4 | four  | NEW (4 rows)
              ```

![State of the completed BP](/enterprise/docs/assets/iac/iac-attachments/77371081/77371084.png)

**Data Store content AFTER purging**

``` sql
wf_datastore=# select * from ds.ds_t_status;

 ds_t_status_id | name | status
----------------+------+---------
              2 | two  | RUNNING
              4 | four | NEW (2 rows)

Schedule for data purging

To fulfill purging of data stores periodically you may want to schedule the Business Process executing. Your DBA can observe increasing of rows in data stores. Tables with 100,000 records and more are good candidates for purging (524,288 rows in the example below).

Determine a number of records in MsSQL console

select count(*) from ds.ds_t_status;

 count
--------
 524288

Use Case: purge multiple data stores by last modification time

Overview

1. Create a Business Process that accepts a list of data stores to purge in a CSV file.
2. Add one Bot Task (see its code below).
3. Run the Business Process periodically, when a purge procedure is needed or create a Schedule for this Business Process.

Input list of data stores

list-of-data-stores.csv

datastore_name, purge_timestamp_column, days
t1, update_timestamp, 30
t2, change_timestamp, 10

datastore_name - name of data store to purge;

purge_timestamp_column - data store's column with time of the last modification;

days - number of days from the current date to start purging.

Bot Task Config

The Config below uses the SQL syntax to find records to purge:

Machine Config

    <datastore name="${datastore_name}"><![CDATA[ delete from @this where "${purge_timestamp_column}"::timestamp < now() - interval '${days} day'; ]]></datastore> <export include-original-data="true"></export>

</config>

Sample Data Stores

Your Data Stores being purged need to have a time-stamp column which shows the date of the last record update (t1.update_timestamp and t2.change_timestamp accordingly in the example below).

ds-purge-t1.csv - file for the data store "t1"

unique_name,update_timestamp
one,2017-01-01 12:00:00
two,2017-06-01 17:20:30
three,2017-08-01 09:40:45

ds-purge-t2.csv -file for the data store "t2"

unique_name,change_timestamp
first,2017-01-02 11:12:13
second,2017-03-15 10:20:30
third,2017-07-01 09:40:45
fourth,2017-08-03 23:19:01

Remember: default data type is TEXT, not TIMESTAMP

If a Data Store is created from a file, then the **update_timestamp **column has the TEXT type (by default). So it has been explicitly converted to ::timestamp in the config (see the row #5 in the Bot Config).

Don't forgot to update timestamp

All of Business Processes that use a data store should update a value of update_timestamp column each time when a change occurs.

Sample of timestamp updating

<datastore name="t1"><![CDATA[ update @this set update_timestamp = now() where unique_name='${var_unique_name}'; ]]></datastore>