Execute Query failed. Error type: ODBC error. ODBC operation attempted: SQLExecDirect. INSERT/UPDATE INVALID; INDEX 1 RESTRICTS COLUMNS WITH SAME VALUES
CAUSE:The cause of this issue is a missing database trigger. From a SQL trace, the offending table and column can be determined. In this instance, the offending column is MR_SUB_ID from the MSTRSUBSCRIPTIONS table.
This primary key column is intended to have the following database trigger applied:
CREATE TRIGGER \PMSTRUNQINT NO CASCADE BEFORE INSERT ON \PMSTRSUBSCRIPTIONS REFERENCING NEW AS NEWROW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET NEWROW.MR_SUB_ID = (SELECT COALESCE( MAX(MR_SUB_ID)+1, -2147483648) FROM \PMSTRSUBSCRIPTIONS); END;
In this situation, there was no trigger applied on the column in the database. Therefore a value of '0' is used for each insert. This means that the first addition to a subscription set will work, but each subsequent addition will fail with this error, as there is already a MR_SUB_ID row with a value of '0'.
ACTION:This issue occurs because of a missing database trigger, caused by the use of unsupported methods to create the Subscription Book Repository. By following the Strategy suggested guidelines for moving repositories, such as the Copy Object Repository tool, database triggers will not be lost.
A missing database trigger may be one symptom of a larger problem caused by incorrect creation of a subscription book. Therefore the recommended action is to drop the faulty tables, and recreate using a recommended method.
For situations when a database trigger be lost, and dropping the table is not an option, the trigger may be recreated manually by running the appropriate SQL found in the SBRCreate<database_type>.sql file, in the Distribution Engine folder.
