Effect on concurrent transactions

If the database has transaction logging, you must issue START VIOLATIONS TABLE in isolation. That is, no other transaction can be in progress on a target table when you issue START VIOLATIONS TABLE on that table within a transaction. Any transactions that start on the target table after the first transaction has issued the START VIOLATIONS TABLE statement will behave the same way as the first transaction with respect to the violations and diagnostics tables. That is, any constraint and unique-index violations by these subsequent transactions will be recorded in the violations and diagnostics tables.

For example, if transaction A operates on table tab1 and issues a START VIOLATIONS TABLE statement on table tab1, the database server starts a violations table named tab1_vio and filters any constraint or unique-index violations on table tab1 by transaction A to table tab1_vio. If transactions B and C start on table tab1 after transaction A has issued the START VIOLATIONS TABLE statement, the database server also filters any constraint and unique-index violations by transactions B and C to table tab1_vio.

The result is that all three transactions do not receive error messages about constraint and unique-index violations, even though transactions B and C do not expect this behavior. For example, if transaction B issues an INSERT or UPDATE statement that violates a check constraint on table tab1, the database server does not issue a constraint violation error to transaction B. Instead, the database server filters the nonconforming row (also called a "bad row") to the violations table without notifying transaction B that a data-integrity violation occurred.

You can prevent this situation from arising in HCL OneDB™ by specifying WITH ERRORS when you specify the FILTERING mode in a SET Database Object Mode, CREATE TABLE, ALTER TABLE, or CREATE INDEX statement. When multiple transactions operate on a table and the WITH ERRORS option is in effect, any transaction that violates a constraint or unique-index requirement on a target table receives a data-integrity error message.