8 Replies Latest reply on Jun 3, 2021 1:25 AM by kunal pandit

    Updating _MTCH table

    Priyanka Bhardwaj Active Member

      Hello Everyone,


      I have a requirement in my project where we need to change the AUTOMERGE_IND to false on the basis of Match Score. We are thinking of using PostMatch User exit .Can I directly update the _MTCH table using update sql statement ?

      Please suggest.




        • 1. Re: Updating _MTCH table
          Rahul Tiwary Seasoned Veteran

          If I understood the requirement clearly, you are trying to avoid auto merges of low match score candidates and would like to take it as tasks for review.


          While there is no stopping updating the Automerge indicator in Post Match UE , we need to consider the impact this entire process would have on amount of tasks generated for review. It's better to tune the Match Rule to generate the accepted level of matches , that would save a lot of custom development and task review effort.

          2 of 2 people found this helpful
          • 2. Re: Updating _MTCH table
            Prabakar Shanmugam Seasoned Veteran

            In general we do not recommend any back end updates on system tables.

            • 3. Re: Updating _MTCH table
              Shirshendu Deb Roy Guru

              Hi Priyanka,

              You should not do it. Changing the backend system tables' data manually with an update query is not recommended and could potentially damage the data lineage.


              However, your requirement is to set the auto merge indicator to 0, after match process ends based on the match score. So, this process has to be done automatically by using the Post Match UE. The manual intervention will not help you to achieve the objective here.


              For example, if the auto match merge process is running (Match and then followed by Merge in Production environment), then you will not obviously do the manual work after the M/M is done because by that time, the records which shouldn't have been merged as per your use case, would have already been merged as per the M/M settings. Therefore, it has to be done after the match process ends via Post Match UE (will be called each time 250 records are processed in a block).

              1 of 1 people found this helpful
              • 4. Re: Updating _MTCH table
                Priyanka Bhardwaj Active Member

                Hi Shirshendu,


                Thank you for your reply !, Yes you are right , my plan is to write Post Match UE only but my UE will have SQL update statement to update _MTCH table. I am not sure if we specifically have any API to update _MTCH table.

                Please suggest how can I update _MTCH table in my Post Match UE.




                • 5. Re: Updating _MTCH table
                  Shirshendu Deb Roy Guru

                  Hi Priyanka,


                  There is no specific API to update the <BO>_MTCH tables, as far as I know. I have just gone through all the available SIF API but couldn't find any.


                  You can directly update the auto merge indicator from Post Match UE (update statement) only for those records where your desired match score is less than the expected. The point here is that, all the process should be automated.


                  Q. Why manual intervention to update the auto merge indicator of <BO>_MTCH table is not preferred?

                  A. There could be some updates that you didn't intend to do which can cause the potential damage to the data lineage leading to confusions later. However, the possibility of messing up the other system tables due to the update of auto merge indicator can be ruled out as the auto merge indicator column is only available in <BO>_MTCH tables.


                  Let me know if you need any help from Post Match UE code perspective.



                  1. Write a procedure in order to update the <BO>_MTCH tables.

                  2. To call the procedure, DONOT create explicit JDBC connection rather use the below code to get the connection object.


                  Connection conn = userExitContext.getDBConnection();

                  2 of 2 people found this helpful
                  • 6. Re: Updating _MTCH table
                    Sathiesh M Guru

                    I think its okay to fire sql update to _MTCH table, as there's no other alternative provided by Infa.

                    And reg the performance, the Pre/PostMatch UE runs only once per job, so it not gonna hinder performance on small or large datasets.

                    A single SQL statement to update all the records in _MTCH is advisable. You dont need to issue explicit commit, as UE will take care of that, pls check the doc below.

                    Refer - Match Process User Exits


                    And as pointed out by Shirshendu, pls follow the best practice for Userexits as mentioned in below doc.

                    Refer - Guidelines for Implementing User Exits

                    1 of 1 people found this helpful
                    • 7. Re: Updating _MTCH table
                      Priyesh Gupta Seasoned Veteran

                      Hi Priyanka,


                      Yes you can update the AUTOMERG_IND to 0 , but make sure this updating should be done at the last  .


                      But instead of that you can update your match rule to Auto into the hub .


                      Note :-- Once you update the AUTOMERG_IND task demone will assign rowid task on such matches , you should have configured the Task Demon in your environment .




                      1 of 1 people found this helpful
                      • 8. Re: Updating _MTCH table
                        kunal pandit Guru

                        There is no API to update the MTCH tables, but it is one of the few MDM tables that you can confidently update using a SQL as long as there is no batch job executing. Post Match will be an excellent location to implement a SQL update on the MTCH table to remove or update any matches. If you are just adding Matches to the manual match queue you should be fine, but if you are moving matches from manual match queue to merge queue, you may end up with Tasks for which the Match records were removed.

                        2 of 2 people found this helpful