How to clear out data from DBQL tables

Objective   ;

The objective is to be able to clear out DBQL (Database Query Logging) tables quickly in order to minimize locking and blocking

Environment/Conditions/Configuration
All versions of Teradata

Procedure

The quickest and least invasive way to clear out DBQL data is as follows. See the additional information section below about more comprehensive methods and additional options.

1) Optional: You can turn off DBQL logging temporarily while doing this procedure then turn it back on when you are done

2) Create an empty table in a database other than DBC and run an insert/select of all of the DBQL data from the original table into the new empty table

3) Run a delete all on the original DBQL table (Example delete from dbc.dbqlogtbl;). Running a delete all uses something called fastpath which avoids using the transient journal and makes the delete complete fast (in a matter of seconds).

4) Reenable DBQL logging if it was turned off in step 1.

Special Considerations
It is best to avoid doing partial deletes from the original DBQL tables (such as deleting based on a date range) because it can potentially lock the table for long periods of time and cause problems with the system trying to write to the DBQL tables at the same time. Deleting by date ranges from the original table can potentially lock the DBQL table for hours and cause other unintended site effects.

Additional Information

More comprehensive methods are available in the Teradata Database Administration Manuals for each Teradata Version.