Purging database tables

Overview

Persistent Mobile Foundation runtime supports relational databases such as IBM DB2, Oracle and MySQL. Database is required to store the data required for the running of PMF applications.

PMF runtime transactions like device registration, app registration, security checks, and OAuth flow update two tables, which are MFP_PERSISTENT_DATA and MFP_TRANSIENT_DATA.

MFP_TRANSIENT_DATA stores the application transactions, security checks and their security states, including the expiry time of the security states. MFP_PERSISTENT_DATA holds the device to application relations, application access time in the device and maintains the version information of the applications.

PMF runs a daily job, which currently takes care of tracking, archiving, decommissioning and deleting records for customers who use license tracking feature. This is controlled by the flags for license tracking and device decommissioning. License tracking is enabled by default. See List of JNDI properties for PMF runtime, for more information.

For customers who do not use license tracking, there is a possibility of accumulation of stale records in the above mentioned tables.

The PMF job is now enhanced to take care of archiving and deleting expired records from the tables, for those who do not enable license tracking.

This feature is driven by the following JNDI properties:

mfp.purgedata.enabled: A property that is used to enable or disable purge of expired records in PMF runtime tables. Default is true in Liberty. To enable it in WebSphere Application Server, set the value to true from WebSphere Application Server console. When mfp.purgedata.enabled is true or mfp.licenseTracking.enabled is true, records that has expiresAt column value older than 5 days gets deleted from the MFP_TRANSIENT_DATA table.

mfp.purgeOldData.age: This property is applicable only when the license tracking is off and mfp.purgedata.enabled is true. When this property is set to a value greater than 0, records from MFP_PERSISTENT_DATA with last_activity_time field value prior to the given number of days is removed from the table. The records are archived into PMF Server home\devices_archive directory. Keep this property value >=90 days. Default value: 0 (does not delete records). When mfp.licenseTracking.enabled is true, deletion of records in MFP_PERSISTENT_DATA follows rules of license tracking.

The number of expired records in the tables may be very high and enabling the feature may cause the queries to run for a long time. To avoid this, we recommend that the customer does an initial clean up of the tables before using this feature. This will ensure that the job runs only for a few seconds or minutes and that there is minimal impact to any transaction.

Warning: When old records from MFP_PERSISTENT_DATA are deleted, users who got deleted will need to re-register their applications. If the application is using custom attributes, this may result in losing these attributes. During registration, the application logic will need to take care of adding these attributes.

For the initial clean up, connect to Mobile Foundation runtime database and run the following queries:

MFP_PERSISTENT_DATA table:

Choose the number of days of records that needs to be persisted. See the date corresponding to this and get the timestamp of this date. All records with LAST_ACTIVITY_TIME less than this will be deleted from MFP_PERSISTENT_DATA with the following query:

select * from MFPDATA.MFP_PERSISTENT_DATA where last_activity_time < (timestamp)

Back up the selected records in case it is needed.

delete from MFPDATA.MFP_PERSISTENT_DATA where last_activity_time < (timestamp)

For example, Consider today’s date is December 19, 2018 and data for last 100 days need to be retained. 100 days before this date will be September 10, 2018 Timestamp corresponding to September 10, 2018 1AM: 1536714000000 Queries for the above sample data is as below:

select * from MFPDATA.MFP_PERSISTENT_DATA where LAST_ACTIVITY_TIME < 1536714000000
delete from MFPDATA.MFP_PERSISTENT_DATA where LAST_ACTIVITY_TIME < 1536714000000

Purge feature mentioned here will only delete rows that satisfy the condition eligible for deletion. Both MFP-PERSISTENT_DATA and MFP_TRANSIENT_DATA contains a LOB column “VALUE”. During delete query involved in purge, the data of VALUE column gets deleted. However, the space used by these LOB values must be separately reclaimed by DBA.

MFP_TRANSIENT_DATA table:

Any record with an expiresat less than current time is a stale record in this table. Get the timestamp corresponding to the current day’s starting hour ( This is to avoid any JVM and DB time mismatch. Make sure you are deleting only records prior to current time of server).

delete from MFP_TRANSIENT_DATA where expiresat < (timestamp for current day 12 AM)

For example, Consider today’s date is December 19, 2018. Time in millis for 12 AM today is 1545177600000

delete from MFP_TRANSIENT_DATA where expiresat < 1545177600000

Note: The Purge feature mentioned here only deletes the rows that satisfy the condition eligible for deletion. Both MFP_PERSISTENT_DATA and MFP_TRANSIENT_DATA contains a LOB column “VALUE”. During delete query involved in purge, the data of VALUE column gets deleted. However, the space used by these LOB values need to be separately reclaimed by DBA.