Object Modes and Violation Detection

The object modes and violation detection features of the database can help you monitor data integrity. These features are particularly powerful when they are combined during schema changes or when insert, delete, and update operations are performed on large volumes of data over short periods.

Database objects, within the context of a discussion of the object modes feature, are constraints, indexes, and triggers, and each of them have different modes. Do not confuse database objects that are relevant to the object modes feature with generic database objects. Generic database objects are things like tables and synonyms.

Definitions of Object Modes

You can set disabled, enabled, or filtering modes for a constraint or a unique index. You can set disabled or enabled modes for a trigger or a duplicate index. You can use database object modes to control the effects of INSERT, DELETE, and UPDATE statements.

Enabled Mode (IDS)

Constraints, indexes, and triggers are enabled by default.

When a database object is enabled, the database server recognizes the existence of the database object and takes the database object into consideration while it executes an INSERT, DELETE, or UPDATE statement. Thus, an enabled constraint is enforced, an enabled index updated, and an enabled trigger is executed when the trigger event takes place.

When you enable constraints and unique indexes, if a violating row exists, the data manipulation statement fails (that is no rows change) and the database server returns an error message.

You can identify the reason for the failure when you analyze the information in the violations and diagnostic tables. You can then take corrective action or roll back the operation.

Disabled Mode (IDS)

When a database object is disabled, the database server does not take it into consideration during the execution of an INSERT, DELETE, or UPDATE statement. A disabled constraint is not enforced, a disabled index is not updated, and a disabled trigger is not executed when the trigger event takes place. When you disable constraints and unique indexes, any data manipulation statement that violates the restriction of the constraint or unique index succeed, (that is, the target row is changed), and the database server does not return an error message.

Filtering Mode

When a constraint or unique index is in filtering mode, the statement succeeds and the database server enforces the constraint or the unique index requirement during an INSERT, DELETE, or UPDATE statement by writing the failed rows to the violations table associated with the target table. Diagnostic information about the constraint violation is written to the diagnostics table associated with the target table.

Example of Modes with Data Manipulation Statements

An example with the INSERT statement can illustrate the differences between the enabled, disabled, and filtering modes. Consider an INSERT statement in which a user tries to add a row that does not satisfy an integrity constraint on a table. For example, assume that user joe created a table named cust_subset, and this table consists of the following columns: ssn (customer's social security number), fname (customer's first name), lname (customer's last name), and city (city in which the customer lives). The ssn column has the INT data type. The other three columns have the CHAR data type.

Assume that user joe defined the lname column as not null but has not assigned a name to the not null constraint, so the database server has implicitly assigned the name n104_7 to this constraint. Finally, assume that user joe created a unique index named unq_ssn on the ssn column.

Now user linda who has the Insert privilege on the cust_subset table enters the following INSERT statement on this table:

INSERT INTO cust_subset (ssn, fname, city)
   VALUES (973824499, "jane", "los altos")

To better understand the distinctions among enabled, disabled, and filtering modes, you can view the results of the preceding INSERT statement in the following three sections.

Results of the Insert Operation When the Constraint Is Enabled

If the not null constraint on the cust_subset table is enabled, the INSERT statement fails to insert the new row in this table. Instead user linda receives the following error message when she enters the INSERT statement:

-292 An implied insert column lname does not accept NULLs.

Results of the Insert Operation When the Constraint Is Disabled

If the not null constraint on the cust_subset table is disabled, the INSERT statement that user linda issues successfully inserts the new row in this table. The new row of the cust_subset table has the following column values.

Results of the Insert When Constraint Is in Filtering Mode

If the NOT NULL constraint on the cust_subset table is set to the filtering mode, the INSERT statement that user linda issues fails to insert the new row in this table. Instead the new row is inserted into the violations table, and a diagnostic row that describes the integrity violation is added to the diagnostics table.

Assume that user joe has started a violations and diagnostics table for the cust_subset table. The violations table is named cust_subset_vio, and the diagnostics table is named cust_subset_dia. The new row added to the cust_subset_vio violations table when user linda issues the INSERT statement on the cust_subset target table has the following column values.

This new row in the cust_subset_vio violations table has the following characteristics:

• The first four columns of the violations table exactly match the columns of the target table. These four columns have the same names and the same data types as the corresponding columns of the target table, and they have the column values that were supplied by the INSERT statement that user linda entered.
• The value 1 in the informix_tupleid column is a unique serial identifier that is assigned to the nonconforming row.
• The value I in the informix_optype column is a code that identifies the type of operation that has caused this nonconforming row to be created. Specifically, I stands for an INSERT operation.
• The value linda in the informix_recowner column identifies the user who issued the statement that caused this nonconforming row to be created.

The INSERT statement that user linda issued on the cust_subset target table also causes a diagnostic row to be added to the cust_subset_dia diagnostics table. The new diagnostic row added to the diagnostics table has the following column values.

This new diagnostic row in the cust_subset_dia diagnostics table has the following characteristics:

• This row of the diagnostics table is linked to the corresponding row of the violations table by means of the informix_tupleid column that appears in both tables. The value 1 appears in this column in both tables.
• The value C in the objtype column identifies the type of integrity violation that the corresponding row in the violations table caused. Specifically, the value C stands for a constraint violation.
• The value joe in the objowner column identifies the owner of the constraint for which an integrity violation was detected.
• The value n104_7 in the objname column gives the name of the constraint for which an integrity violation was detected.

By joining the violations and diagnostics tables, user joe (who owns the cust_subset target table and its associated special tables) or the DBA can find out that the row in the violations table whose informix_tupleid value is 1 was created after an INSERT statement and that this row is violating a constraint. The table owner or DBA can query the sysconstraints system catalog table to determine that this constraint is a NOT NULL constraint. Now that the reason for the failure of the INSERT statement is known, user joe or the DBA can take corrective action.

Multiple Diagnostic Rows for One Violations Row

In the preceding example, only one row in the diagnostics table corresponds to the new row in the violations table. However, more than one diagnostic row can be added to the diagnostics table when a single new row is added to the violations table. For example, if the ssn value (973824499) that user linda entered in the INSERT statement had been the same as an existing value in the ssn column of the cust_subset target table, only one new row would appear in the violations table, but the following two diagnostic rows would be present in the cust_subset_dia diagnostics table.

Both rows in the diagnostics table correspond to the same row of the violations table because both of these rows have the value 1 in the informix_tupleid column. However, the first diagnostic row identifies the constraint violation caused by the INSERT statement that user linda issued, while the second diagnostic row identifies the unique-index violation caused by the same INSERT statement. In this second diagnostic row, the value I in the objtype column stands for a unique-index violation, and the value unq_ssn in the objname column gives the name of the index for which the integrity violation was detected.

For more information about how to set database object modes, see the SET Database object mode statement in the IBM Informix: Guide to SQL Syntax.

Violations and Diagnostics Tables

When you start a violations table for a target table, any rows that violate constraints and unique indexes during INSERT, UPDATE, and DELETE operations on the target table do not cause the entire operation to fail, but are filtered out to the violations table. The diagnostics table contains information about the integrity violations caused by each row in the violations table. By examining these tables, you can identify the cause of failure and take corrective action by either fixing the violation or rolling back the operation.

After you create a violations table for a target table, you cannot alter the columns or the fragmentation of the base table or the violations table. If you alter the constraints on a target table after you have started the violations table, nonconforming rows will be filtered to the violations table.

For information about how to start and stop the violations tables, see the START VIOLATIONS TABLE and STOP VIOLATIONS TABLE statements in the IBM Informix: Guide to SQL Syntax.

Relationship of Violations Tables and Database Object Modes

If you set the constraints or unique indexes defined on a table to the filtering mode, but you do not create the violations and diagnostics tables for this target table, any rows that violate a constraint or unique-index requirement during an insert, update, or delete operation are not filtered to a violations table. Instead, you receive an error message that indicates that you must start a violations table for the target table.

Similarly, if you set a disabled constraint or disabled unique index to the enabled or filtering mode and you want the ability to identify existing rows that do not satisfy the constraint or unique-index requirement, you must create the violations tables before you issue the SET DATABASE OBJECT MODE statement.

Examples of START VIOLATIONS TABLE Statements

The following examples show different ways to execute the START VIOLATIONS TABLE statement.

Starting Violations and Diagnostics Tables Without Specifying Their Names

To start a violations and diagnostics table for the target table named customer in the demonstration database, enter the following statement:

START VIOLATIONS TABLE FOR customer

Because your START VIOLATIONS TABLE statement does not include a USING clause, the violations table is named customer_vio by default, and the diagnostics table is named customer_dia by default. The customer_vio table includes the following columns:

customer_num
fname
lname
company
address1
address2
city
state
zipcode
phone
informix_tupleid
informix_optype
informix_recowner

The customer_vio table has the same table definition as the customer table except that the customer_vio table has three additional columns that contain information about the operation that caused the bad row.

The customer_dia table includes the following columns:

informix_tupleid
objtype
objowner
objname

This list of columns shows an important difference between the diagnostics table and violations table for a target table. Whereas the violations table has a matching column for every column in the target table, the columns of the diagnostics table do not match any columns in the target table. The diagnostics table created by any START VIOLATIONS TABLE statement always has the same columns with the same column names and data types.

Starting Violations and Diagnostics Tables and Specifying Their Names

The following statement starts a violations and diagnostics table for the target table named items. The USING clause assigns explicit names to the violations and diagnostics tables. The violations table is to be named exceptions, and the diagnostics table is to be named reasons.

START VIOLATIONS TABLE FOR items
   USING exceptions, reasons

Specifying the Maximum Number of Rows in the Diagnostics Table

The following statement starts violations and diagnostics tables for the target table named orders. The MAX ROWS clause specifies the maximum number of rows that can be inserted into the diagnostics table when a single statement, such as an INSERT or SET DATABASE OBJECT MODE statement, is executed on the target table.

START VIOLATIONS TABLE FOR orders MAX ROWS 50000

If you do not specify a value for MAX ROWS when you create a violations table, no maximum (other than disk space) will be imposed.

Example of Privileges on the Violations Table

The following example illustrates how the initial set of privileges on a violations table is derived from the current set of privileges on the target table.

For example, assume that we created a table named cust_subset and that this table consists of the following columns: ssn (customer's social security number), fname (customer's first name), lname (customer's last name), and city (city in which the customer lives).

The following set of privileges exists on the cust_subset table:

• User alvin is the owner of the table.
• User barbara has the Insert and Index privileges on the table. She also has the Select privilege on the ssn and lname columns.
• User carrie has the Update privilege on the city column. She also has the Select privilege on the ssn column.
• User danny has the Alter privilege on the table.

Now user alvin starts a violations table named cust_subset_viols and a diagnostics table named cust_subset_diags for the cust_subset table, as follows:

START VIOLATIONS TABLE FOR cust_subset
   USING cust_subset_viols, cust_subset_diags

The database server grants the following set of initial privileges on the cust_subset_viols violations table:

• User alvin is the owner of the violations table, so he has all table-level privileges on the table.
• User barbara has the Insert, Delete, and Index privileges on the violations table. She also has the Select privilege on the following columns of the violations table: the ssn column, the lname column, the informix_tupleid column, the informix_optype column, and the informix_recowner column.
• User carrie has the Insert and Delete privileges on the violations table. She has the Update privilege on the following columns of the violations table: the city column, the informix_tupleid column, the informix_optype column, and the informix_recowner column. She has the Select privilege on the following columns of the violations table: the ssn column, the informix_tupleid column, the informix_optype column, and the informix_recowner column.
• User danny has no privileges on the violations table.

Example of Privileges on the Diagnostics Table

The following example illustrates how the initial set of privileges on a diagnostics table is derived from the current set of privileges on the target table.

For example, assume that a table called cust_subset consists of the following columns: ssn (customer's social security number), fname (customer's first name), lname (customer's last name), and city (city in which the customer lives).

The following set of privileges exists on the cust_subset table:

• User alvin is the owner of the table.
• User barbara has the Insert and Index privileges on the table. She also has the Select privilege on the ssn and lname columns.
• User carrie has the Update privilege on the city column. She also has the Select privilege on the ssn column.
• User danny has the Alter privilege on the table.

Now user alvin starts a violations table named cust_subset_viols and a diagnostics table named cust_subset_diags for the cust_subset table, as follows:

START VIOLATIONS TABLE FOR cust_subset
   USING cust_subset_viols, cust_subset_diags

The database server grants the following set of initial privileges on the cust_subset_diags diagnostics table:

• User alvin is the owner of the diagnostics table, so he has all table-level privileges on the table.
• User barbara has the Insert, Delete, Select, and Index privileges on the diagnostics table.
• User carrie has the Insert, Delete, Select, and Update privileges on the diagnostics table.
• User danny has no privileges on the diagnostics table.