When using Record and Replay you may notice a build-up of data in the record tables. The following provides guidance on removing older records from the tables.
App Connect Enterprise uses two tables to store the recorded data:
· The wmb_msgs table stores each recorded message and some metadata.
· The wmb_binary_data table which is used if a message body or exception list was recorded.
The tables have no enforced referential integrity and all we need to do when deleting data is remove it from the tables in the correct order.
The following commands to remove messages older than 15th November 2023
DELETE FROM wmb_binary_data WHERE wmb_msgkey IN (SELECT wmb_msgs.wmb_msgkey FROM wmb_msgs WHERE event_timestamp < '2023-11-15 00:00:00.000')
DELETE FROM wmb_msgs WHERE event_timestamp < '2023-11-15 00:00:00.000'
You may require a more complicated ‘WHERE’ clause to target the entries to be removed, if you do, please ensure you add any extra clauses to both ‘DELETE’ statements.
Notice that the SQL statements are using strings as for the timestamps. This is correct as for performance reasons record and replay stores the ‘event_timestamp’ as a VARCHAR in these tables. This means you must format your dates in the full ISO standard, with YYYY-MM-DD, always using two-digit months and days.
Once you have your SQL you could create a script file and call it from an application to run the database commands for you, or add it to a cronjob if desired.