Truncate Table Messages

The table cannot be truncated if it has an open cursor or dirty readers.

Cause:

You must have exclusive access to the table.

Action:

Wait for dirty readers to complete or close all the open cursors and reissue the TRUNCATE TABLE command.

The table cannot be truncated. It has at least one non-empty child table with referential constraints.

Cause:

You cannot truncate a table if it has child tables with referential constraints and at least one row.

Action:

Empty the child tables before you truncate this table.

TRUNCATE table statement cannot be executed if already inside a transaction.

Cause:

Because you must issue the TRUNCATE TABLE statement as a single transaction, you cannot nest it inside another transaction. For example, you cannot issue TRUNCATE TABLE within a BEGIN WORK and COMMIT WORK block or inside a trigger.

Action:

Issue the TRUNCATE TABLE statement as a single transaction, outside of a BEGIN WORK and COMMIT WORK block or trigger.

Break the transaction into several parts, as follows:

Commit the first part of the transaction.
You can roll back only the first part of the transaction.
Issue the TRUNCATE TABLE statement.
Execute the remaining part of the transaction as a new transaction inside a BEGIN WORK and COMMIT WORK block.

Example:

COMMIT WORK;|
TRUNCATE TABLE tab1;
BEGIN WORK;
INSERT INTO tabl1 VALUES ("James");
...
COMMIT WORK;