• 2 replies
  • Latest Post - ‏2016-11-01T16:54:07Z by KaranBal
Venkat Podatharapu
Venkat Podatharapu
75 Posts

Pinned topic Virtual MDM - MPI_ENTOQUEUE_ID table is accumulated with 396 million regards

‏2016-10-04T15:36:55Z | data management master mdm-migration

We have configured Default JMS process in our project, from past few days JMS messages are not generating, then we investigated to find out the root cause of the issue and found out that MPI_ENTOQUEUE_ID table is accumulated with all the messages around 396 Million records where as we have around 10 million records in our system.

Table count is coming down every two minutes 500 records, with this process we will not be able to clear the data on this table.

We have handlers implemented to create composite view from the MDM and send JMS messages to the down stream applications.We were not able to reproduce this issue in lower enviroments.


My Questions :

Is there any Java API to clean this table with MDM process. I am against to run the DML which will create lot other issues since we are missing MDM flavor thru DML process.

Is there any other reasons for this table accumulated with these many records.


Any input would be appreciated.

  • spiggle
    7 Posts

    Re: Virtual MDM - MPI_ENTOQUEUE_ID table is accumulated with 396 million regards


    You don't need an API. An SQL delete or truncate statement can be used to remove these rows. But I would say the bigger question is why did these rows accumulate in the first place. 

    If this is a production system, I'd make sure you don't need this data to be processed and published to jms before you remove them, at a minimum take a backup of the rows. Deleting production data is not something you want to get wrong. I'd be hesitant myself about deleting them.

    If as you say data is being processed ok, albeit slowly, then it looks like your event manager is not coping with its workload. Event managers are often single threaded (context) which can mean a relatively slow process rate. Are you able to increase the contexts in the configuration. Multiple contexts can result in messages getting processed out of sequence, which may be an issue for you, that's why it defaults to one.

    Check that the 2 indexes on this table are still there and haven't somehow been deleted. You don't say if this is DB2, but maybe running runstats on this table would help as it might not be making use of the indexes

    Find the oldest (lowest) audrecno value in your mpi_entoqueu_id table, and use this to look up mpi_audhead. What is the timestamp on this audit record, i.e. how long had this data been waiting in the output queue. This is just for your own interest, it won't help with a solution. But with only 10 million records, I'm guessing these 396 million will go back quite some time.



  • KaranBal
    227 Posts

    Re: Virtual MDM - MPI_ENTOQUEUE_ID table is accumulated with 396 million regards


    If the quantity of messages is the concern, then the handler code or other setting should be reviewed to not send as many events.

    If the size of the table is a concern, then we don't delete records from that table and so it can become big. We send JMS notifications that need sending and then change the wrkowner and it stays there. The rows in there are not sent periodically; just the new ones. Key is what is the wrkowner for that record, if it is ERROR then it wasn't sent properly but we are not trying to either. if the wrkowner was a user like say mdmadmin or SYSTEM (user name is configurable and determines which event manager picks it up), then the system would try to send it and later change the wrkowner to something else.

    We don't have API calls, but you may just truncate the table and wipe it clean. MDM doesn't need this table, it's for your downstream application and custom code. So the only consideration is whether your downstream application and custom solution need to process all the records. If they don't, then you may delete it. If they do need to take action, then it should be completed before deletion. From MDM side, it is not an important consideration.