Examples of START VIOLATIONS TABLE statements

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

Start 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 are independent of the schema of the target table. The diagnostics table created by any START VIOLATIONS TABLE statement always has the four columns in the list above, with the same column names and data types.

Start violations and diagnostics tables and specify their names

The following statement starts a violations and diagnostics table for the target table named items. The USING clause declares explicit names for 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;

Specify 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 orders_diadiagnostics table when a single statement, such as an INSERT, MERGE, or SET Database Object Mode, is executed on the target table.

START VIOLATIONS TABLE FOR orders MAX ROWS 50000;

If you do not specify a value for MAX ROWS in the START VIOLATIONS TABLE statement, there is no default limit on the number of rows in the diagnostics table, apart from the available disk space.

The MAX ROWS clause limits the number of rows only for operations in which the table functions as a diagnostics table.