Daily Maintenance Process - Teradata Database

Maintenance means the recommended practice of clearing out the DBQL dictionary tables after copying relevant data into the user DBQL temporary and DBQL history tables.

Note: DBQL temporary and DBQL history tables are user tables that the Database Administrator (DBA) generates. They are not DBQL tables. Rather they are tables that mirror the DBQL tables and serve to simplify the analysis process and the clear the raw data from the DBQL dictionary tables. DBQL dictionary tables are internal to the DBC.

There are 2 steps to the daily maintenance process. Each step is restartable.

Note: Step 1 must finish before Step 2 can start.

Step 1

Checks to make sure that DBQLOGTBL_TMP, DBQLSQLTBL_TMP, DBQLOBJTBL_TMP and DBQLSummaryTBL_TMP are empty.

Executes the END QUERY LOGGING statement on one user ID to get the buffers to dump.

Executes the macro SYS_MGMT.LoadDBQLTMP.

This macro loads the DBQL temporary tables from the DBC DBQL tables and deletes data from the DBC DBQL tables

Executes the BEGIN QUERY LOGGING statement on the user ID for which logging has just ended.

Executes the COLLECT STATISTICS statement on the DBQL temporary tables.

Step 2

Checks to make sure that the DBQL temporary tables have rows in them.

Executes the macro SYS_MGMT.LoadDBQLHSTTBLS.

This macro loads DBQLOGTBL_HST, DBQLSQLTBL_HST, DBQLOBJTBL_HST, DBQLOBJTBL_SUM and DBQLSummaryTBL_HST and deletes data from the DBQL temporary tables.

Executes the COLLECT STATISTICS statement on DBQLOGTBL_HST, DBQLSQLTBL_HST, DBQLOBJTBL_HST, DBQLOBJTBL_SUM and DBQLSummaryTBL_HST