Using the Diagnostics Table

For information on the relationship between the diagnostics table and the violations table, see Relationships Among the Target, Violations, and Diagnostics Tables.

The following issues concern the structure and use of the diagnostics table:

• The MAX ROWS clause of the START VIOLATIONS TABLE statement sets a limit on the number of rows that can be inserted into the diagnostics table when you execute a single statement, such as an INSERT or SET Database Object Mode statement, on the target table.
• The MAX ROWS clause limits the number of rows only for operations in which the table functions as a diagnostics table.
• When a table functions as a diagnostics table, it cannot have triggers or constraints defined on it.
• When a table functions as a diagnostics table, users can create indexes on the table, but the existence of an index affects performance. You cannot set unique indexes on a diagnostics table to FILTERING database object mode.
• If a target table has a violations and diagnostics table associated with it, dropping the target table in cascade mode (the default mode) causes the violations and diagnostics tables to be dropped also.
• If the target table is dropped in restricted mode, the DROP TABLE operation fails (because the violations and diagnostics tables exist).
• Once a violations table is started for a target table, you cannot use the ALTER TABLE statement to add, modify, or drop columns in the target table, violations table, or diagnostics table. Before you can alter any of these tables, you must issue a STOP VIOLATIONS TABLE statement for the target table.
• The database server does not clear out the contents of the diagnostics table before or after it uses the diagnostics table during an Insert, Update, Delete, or Set operation.
• If the target table that is specified in the START VIOLATIONS TABLE statement is fragmented, the diagnostics table is fragmented with a round-robin strategy over the same dbspaces in which the target table is fragmented.

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

START VIOLATIONS TABLE FOR stock

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

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.

Related Information