Hi Devs,
I have a long running process instance which runs on a loop. It handles
hundreds of message exchanges ( both invokes and receives ) per day and
designed to runs years until business logic ends.
Now the problem is, I am seeing database is growing with mex data related
to this process instance. With my experiences with ODE, I have seen
performance degradation with database size increases. (I am using ODE
1.3.5.) So I am thinking writing a clean up script to clean up following
tables.
ODE_MEX_PROP,
ODE_MESSAGE,
ODE_MESSAGE_EXCHANGE
So I want to identify the old message exchanges ( let's say created 2
months before) in order to clean from the database.
So I considered following scenarios.
- MyRole message exchange with request-only and request-response pattern
- PartnerRole message exchange with request-only and request-response
pattern
I used following filter to filter out old message exchanges.
SELECT *
FROM ODE_MESSAGE_EXCHANGE MEX
WHERE MEX.PROCESS_INSTANCE_ID IN ('3752')
AND
(
(
-- Completed MyRole Message Exchanges
MEX.CORRELATION_STATUS IN ('MATCHED')
AND MEX.DIRECTION = '77'
)
OR
(
-- Partner Role Message Exchanges.
MEX.DIRECTION = '80'
)
)
AND
(
(
-- Handeling REQUEST_RESPONSE message exchanges.
MEX.PATTERN = 'REQUEST_RESPONSE'
AND MEX.STATUS IN ('RESPONSE' , 'FAULT' , 'FAILURE' ,
'COMPLETED_OK' , 'COMPLETED_FAULT', 'COMPLETED_FAILURE')
)
OR
(
-- Handeling REQUEST_ONLY message exchanges.
MEX.STATUS IN ( 'ASYNC' , 'FAULT' , 'FAILURE' , 'COMPLETED_OK'
, 'COMPLETED_FAULT', 'COMPLETED_FAILURE')
AND MEX.PATTERN = 'REQUEST_ONLY'
)
);
I have done some tests to verify whether this is possible and found that we
can clean up old message exchanges/messages without affecting process
execution. I though get advice from the ODE developers to improve my logic
on filtering old message exchanges. Appreciate your input on this.
Thanks,
Hasitha.
*Hasitha Aravinda*
Blog: http://tryitnw.blogspot.com/
Linkedin: http://www.linkedin.com/in/hasithaaravinda
