Detailed explanation of the problem:
API Gateway data stored in Elastic Search can be archived/purged using the purge utility or from API GW UI → Manage Data → Purge/Archive.
API Gateway Internal DB is used to store event data, but the purging utility is not provided for housekeeping data stored in DB.
When it comes to API Gateway events stored in external destinations, such as a database, the responsibility of housekeeping does not lie with the API Gateway itself. The archive and purge options provided within the product are exclusively meant for managing the API Gateway datastore.
For databases, it’s the customer’s responsibility to handle the data based on their specific data retention policy.
The advantages of this approach are as follows:
- No API Gateway resources (such as CPU, threads, memory, etc.) are consumed.
- Reduced network chatter.
- Data archival is delegated to database administrators instead of webMethods administrators or developers.
- Archival operations can take place regardless of whether the API Gateway is running or not.
Regarding the script or service used to delete API transaction logs data from the API Gateway Events database, the following query can be employed based on the insert timestamp:
delete from dbo.AGW_EVENT_TXN where INSERTTIMESTAMP<'timestamp';
Please modify the table name and timestamp value according to the customer’s environment and desired data retention period in the above query. In case the data volume is substantial, it is advisable to consult with the database administrators and choose a smaller time interval for deletion (e.g., INSERTTIMESTAMP>‘2022-10-10 17:01:06.420’ and INSERTTIMESTAMP<‘2022-10-13 17:01:06.420’).