I looked into your suggestion but found that we are getting this issue over a couple of Custom Indexes that were defined over a Subject that were not registered into the console which was the main root cause. Currently we've dropped them and are looking into it to resolve the issue permanently.
You can try the below steps:
Use SIF API "RegisterCustomIndex" to create an index. Use the history table name (ex: C_ADDRESS_HIST) and rowid of the specific column from the C_REPOS_COLUMN table corresponding to the history table (where condition of column_name = <your column> and table_name = <C_ADDRESS_HIST>)
Once the API is run successfully, you can get the constraint name from C_REPOS_CONSTRAINT table
With the constraint name listed in the C_REPOS_CONSTRAINT table, you can now create a physical index on the database on the history table.
Now, do a server restart and a MET validation.
Hope you are using DB2
In this case, the same constraint name SVR1_2YNE is created on two tables in the database i.e C_ADDRESS and C_TEST tables.
To resolve this issue, rename (from SVR1_2YNE to INDEX_FO or something else) the index name for the non-MDM table; that is, C_TEST .
registerCustomIndex SIF API can be used to register the index created from the backend.
Please take care of the changes if the indexes are created on the supporting tables as they might get dropped when we apply the changelist.
Are you running metadata validation process while other batch processes are running?
Also which is your database?(oracle,sqlserver,DB2)
I'm not running Metadata Validation process while other batches are running and am using MS Sql Server as my DB.
these Custom Indexes were created on a XREF Table which are getting dropped whenever we are publishing other changes to HUB. Is there a chance we can register them on a XREF Table which can help us getting away from this Issue.
Dropping of Custom indexes is a known issue that got fixed in 10.2 HF4 and upcoming versions
Thank you all for chiming in. We were able to deploy Custom Indices successfully over an XREF Table using SIF API Call as mentioned in KB Article (144917 ) instead of creating and deploying the Index with a Store Proc We've used following SIF API SOAP XML Call
SOAP XML<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:siperian.api"><soapenv:Header/><soapenv:Body><urn:registerCustomIndex><!--Optional:--><urn:username>admin</urn:username><!--Optional:--><urn:password><urn:password>admin</urn:password><urn:encrypted>false</urn:encrypted></urn:password><urn:orsId>localhost-orcl-MDM_SAMPLE</urn:orsId><urn:tableName>C_PARTY_XREF</urn:tableName><urn:rowidColList>SVR1.IS1~SVR1.IWG</urn:rowidColList> <!-- multiple columns separated by tilda --><urn:indexType>NI</urn:indexType></urn:registerCustomIndex></soapenv:Body></soapenv:Envelope>
and then followed next steps as listed in article 144917. As a safety feature we've cleared up the cache to make sure we don't end up with errors which worked for us.
If you apply the changelist again it might get dropped so check the version of MDM as Sathish mentioned in the earlier chat.