improve house cleaning efficiency

We at FEPOC has been using this product together with ITXA and ITX since 2019. The issue here is that the house cleaning jobs are not keeping up with the high volume we process every day. See the detail word document in support ticket TS018931836. We have been working with IBM Support for many months on this issue, however, we haven't found any solutions within the product itself. We feel the product needs serious architectural design change to address the issues. Below are the high level ideas:

1. the product's mailbox message lifecycle management is different and independent from the base product's BP/Document lifecycle management. There is no way to change the product behavior of automatically setting 10 years lifespan of any BP instances that calls mailbox add service. Such design requires customers to configure mailbox delete service properly to delete the message after a few days (in our case, 7 days), and schedule this job to run. In our case, the mailbox delete job runs non stop except the 5am and 12am hours due to our business rules. The underlying SQLs of the mailbox delete service is not optimal when the mailbox backlog gets to huge level like ours.

The design change is to remove such 10 year lifespan for any BP calling the mailbox add service. That way, the mailbox messages will be using the same system wide BP/document lifecycle management architecture, will be linear and far easier to manage. There is no need to run separate mailbox delete house cleaning jobs anymore.

2. The BP indexing job's underlying SQL needs serious optimization. Its current SQL doesn't work well when there are huge backlog of unindexed BP instances in the system. See the details in the support ticket. There is no direct way to control the size of database transaction size. The parameter "max_business_processes" is the only control we have. This is really bad design because different BP logic and their persistence level setting can mean wide different range of # of records in the database tables. For example, a BP with a big looping can have thousands of records persisted, while a simple non looping BP can have a just a few records. We need some parameter to control the database transaction size, to avoid running huge uncommitted transactions on database and time-consuming rollback in any error condition.

3. as of now, we are doing monthly deep-cleaning method to bypass the never-caught-up house cleaning jobs. See the ticket for the detail steps. What we want is a table partitioning provided by the product itself. We have our own design and will implement it to be our permanent solution. The partition key should be timestamp based column of the large transaction tables for easier partition create & drop management. However, a few tables don't have their timestamp column indexed, such as mbx_message table. We tried to create index on the created_datetime colume, but performance testing shows that it actually slowed down our throughput by more than 20%. So we updated the design to use message ID or BP ID as the partition key on those tables where timestamp columns are not indexed. This is hugely inconvenient for our partition management, and not precise either. We have to estimate from time to time the start and end values of such IDs and create partitions accordingly.

4. the product's underlying tables don't have any "foreign key", managed by database. The data referential integrity is managed by the B2Bi application code, leading to unavoidable orphan records in various tables. We want such management logic to be part of the data layer, not application layer.

5. Our solution at FEPOC uses File Gateway heavily. One of the findings during performance testing as well as the PROR operation since 2019 is that FG_EVENT and FG_EVENTATTR table are routinely the top 2 tables by record count in our database. There is no way we can reduce the records being inserted into these tables. We only need about <5% of those records in our solution. This is like running PROD in TRACE logging level.

Please offer a way in FileGateway event to control the level of details that should be stored. We should be able to include or exclude any events and their attributes in the FG_EVENT/FG_EVENTATTR table recording.