New Features in Dynamic Server, Version 10.00.xC4

For a comprehensive list of new database server features in Version 10.00.xC4, 4see the IBM Informix Getting Started Guide. In addition to documenting 4the new feature of Version 10.00.xC4 that is described in this section, this 4manual corrects errata that have been identified since the previous edition.

A new TRUNCATE data definittion language (DDL) statement of SQL removes 4all active rows and the entire B-tree structure of all indexes from all partitions 4of a specified table in the local database. Storage formerly occupied by these 4data rows and indexes becomes available for other tables, or you can optionally 4reserve the space for reuse in subsequent operations on the same table or 4index partition.

While the TRUNCATE statement is executing, no other session can lock or 4modify the table until TRUNCATE completes and is either committed or rolled 4back. In a database with transaction logging, a subsequent ROLLBACK statement 4can restore the table to its state before the transaction that included TRUNCATE 4began, but no operation except COMMIT or ROLLBACK can follow TRUNCATE within 4a transaction.

After the TRUNCATE statement successfully executes, the database server 4automatically resets the statistics and distributions for the table and for 4its indexes, as if UPDATE STATISTICS had been executed for that table. Any 4Delete trigger that is defined on the table is ignored when TRUNCATE executes, 4and the serial counter for SERIAL and SERIAL8 columns is not reset.

The table that you truncate can be a raw, temporary, or standard table. 4It can also be a virtual table, or a table with a virtual-index interface, 4provided that the virtual table or the virtual-index interface has a valid am_truncate( ) purpose function that was explicitly registered 4in the sysams system catalog table.

This feature offers performance advantages over the DELETE table statement, because TRUNCATE empties a table without logging individual 4deleted rows or index updates, and without activating Delete triggers. Unlike 4the DROP TABLE statement, TRUNCATE does not remove the definitions of the 4table or its indexes, triggers, views, constraints, privileges, and other 4properties from the system catalog. The TRUNCATE statement can also make storage 4management more efficient through its ability to free space, or to reuse the 4space for the same table and its indexes.