Hi Sathwik, I have done it already. Cleanup logic is based on instance ID and using instance ID we can obtains mex ids. Then we can use mexIDs to cleanup relevant tables. I have used same logic for my clean up script as well.
But the problem I am facing is finding a completed message exchanges for an active instance. Completed in the sense that mex will no longer in use ( like Mex Match Job) and safe to delete. So I consider following four scenarios and came up with above SQL query. I have ignored the case where instance creation message exchange since there is only one mex for an instance following that pattern. 1) MyRole message exchange with request-only pattern and correlation status MATCHED 2) MyRole message exchange request-response pattern and correlation status MATCHED 3) PartnerRole message exchange with request-only pattern 4) PartnerRole message exchange with request-response pattern But It seems like I have forgot to check a scenario when correlation match event on Partner Role Message Exchanges. I am currently working on it. Are you seeing any other message exchange patterns that need to be considered here. ? Thanks, Hasitha. *Hasitha Aravinda* Blog: http://tryitnw.blogspot.com/ Linkedin: http://www.linkedin.com/in/hasithaaravinda On Mon, Mar 30, 2015 at 11:46 AM, Sathwik B P <sathwik...@gmail.com> wrote: > Oh my bad, It's a long running process. > > In that case I would recommend tracing the cleanup code for messages and > using those if you have not done that already. > > > On Mon, Mar 30, 2015 at 11:32 AM, Hasitha Aravinda < > mail.hasith...@gmail.com > > wrote: > > > Hi Sathwik, > > > > Thank you for your quick response. As I know instance clean up feature > was > > designed to clean up data when the instance is completed or failed. But > In > > my scenario I want to clean data from a running instance. > > > > Thanks, > > Hasitha. > > > > > > > > > > > > *Hasitha Aravinda* > > Blog: http://tryitnw.blogspot.com/ > > Linkedin: http://www.linkedin.com/in/hasithaaravinda > > > > > > > > > > > > On Mon, Mar 30, 2015 at 11:19 AM, Sathwik B P <sathwik...@gmail.com> > > wrote: > > > > > Hi, > > > > > > How about using instance data cleanup > > > https://ode.apache.org/instance-data-cleanup.html > > > > > > Have you tried it out. > > > > > > regards, > > > sathwik > > > > > > On Mon, Mar 30, 2015 at 10:38 AM, Hasitha Aravinda < > > > mail.hasith...@gmail.com > > > > wrote: > > > > > > > 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 > > > > > > > > > >
