Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
TRUNCATE statementUse the TRUNCATE statement to quickly delete all rows from a local table and free the associated storage space. You can optionally reserve the space for the same table and its indexes. Only HCL OneDB™ supports this implementation of the TRUNCATE statement, which is an extension to the ANSI/ISO standard for SQL.
The Table Specification

The Table Specification

You must specify the name or synonym of a table in the local database to which you are currently connected. If the USETABLENAME environment variable is set, you must use the name of the table, rather than a synonym. The table can be of type STANDARD, RAW, or TEMP, but you cannot specify the name or synonym of a view, or an object that the CREATE EXTERNAL TABLE statement defined. (Categories of tables that are not valid with TRUNCATE are listed in the section Restrictions on the TRUNCATE statement.)

In a database that is ANSI-compliant, you must specify the owner qualifier if you are not the owner of the table or synonym.

After the TRUNCATE statement begins execution, HCL OneDB™ attempts to place an exclusive lock on the specified table, to prevent other sessions from locking the table until the TRUNCATE statement is committed or rolled back. Exclusive locks are also applied to any dependent tables of the truncated table within a table hierarchy.

While concurrent sessions that use the Dirty Read isolation level are reading the table, however, the TRUNCATE statement fails with an RSAM -106 error. To reduce this risk, you can set the IFX_DIRTY_WAIT environment variable to specify that the TRUNCATE operation wait for a specified number of seconds for Dirty Read transactions to commit or rollback.