Preparing User Privileges and the Violations Table

Home | Previous Page | Next Page Loading Data to a Database Table > Components of the Load Job >

Preparing User Privileges and the Violations Table

You must make sure that the user who runs a load job has sufficient privileges to manage the constraints and the violations table. The following table summarizes the actions that you must take. The following sections discuss these actions in more detail.

Table Status	User Privileges	Action
Owned by user		No further action is required.
Not owned by user	User has DBA privileges on the table.	No further action is required.
Not owned by user	User does not have DBA privileges on the table.	User must have: Resource privileges on database. Alter privileges on table. Owner must start violations table.

For detailed information about user privileges and violations tables, see the IBM Informix: Guide to SQL Syntax and the IBM Informix: Guide to SQL Reference.

Setting User Constraints

To modify any constraint, index, or trigger, a user must have both Alter privileges on the table and the Resource privilege on the database. The user must also have these privileges to start or stop a violations table. You use the GRANT statement to set these privileges.

Managing the Violations and Diagnostics Tables

You can turn on or off the generation of constraint-violation information. If you turn on the generation of constraint-violation information, onpload writes the information to the violations and diagnostics tables. For more information, see Changing the Load Options.

The HPL manages the violations and diagnostics tables in the following manner:

Starts the load job.
Starts the violations and diagnostics tables if they do not exist already. (If a violations and diagnostics table already exists, the HPL uses that table).
The HPL uses the following SQL statement to start the violations table:
```
START VIOLATIONS TABLE FOR tablename
```
Performs the load job.
Stops the violations and diagnostics tables if they were started at step 2.
The HPL uses the following SQL statement to stop the violations and diagnostics tables:
```
STOP VIOLATIONS TABLE FOR tablename
```
Drops the violations table if the violations table is empty.

The START VIOLATIONS statement creates the violations and diagnostics tables and associates them with the load table. The STOP VIOLATIONS statement dissociates the violations and diagnostics tables from the load table. For more information about the START VIOLATIONS and STOP VIOLATIONS statements, see the IBM Informix: Guide to SQL Syntax.

The violations table (tablename_vio) and the diagnostics table (tablename_dia) are always owned by the owner of the table with which they are associated. The Resource privilege lets a user start and stop a violations table, but it does not let the user drop a table that he or she does not own. Thus, the HPL cannot drop the violations table in step 5 if the user is not the owner.

Failure to drop the violations table does not cause the load job to fail. However, this failure leaves in the database a violations table that is not associated with a table. If the user tries to run the job again, the START VIOLATIONS TABLE statement in step 2 fails because the table tablename_vio already exists.

To solve this problem, the owner of the table or the database administrator must explicitly create the violations and diagnostics tables using the START VIOLATIONS statement. When the owner creates the violations table, the following actions take place:

In step 2, the HPL uses the existing violations table.
In step 4, the HPL does not stop the violations table because the table was not started in step 2.
In step 5, the HPL does not drop the violations table because the user does not own the table.

After the load job is complete, an active violations table remains in the database. This table might be empty, but does no harm. When the user runs the load job a second time, the violations table is available, and the load job succeeds.