16.10 - Maintaining the Logs - Teradata Database

You can access the contents of the DBQL logs, but the following DBQL objects are protected:

• No user, including DBC and SystemFE, can access the DBQLAccessMacro macro or the DBQLRuleTbl or DBQLRuleCountTbl tables.
• No user can modify DBQLAccessMacro or alter, drop, or update any of the DBQL table definitions.
• You cannot delete rows from DBQLRuleTbl or DBQLRuleCountTbl, because they are manipulated by BEGIN QUERY LOGGING, REPLACE QUERY LOGGING, and END QUERY LOGGING processing.

When you are ready to use DBQL on your production queries, follow this procedure:

1. Create and maintain an executable BTEQ script file to submit (or keep a copy of) your final BEGIN QUERY LOGGING or REPLACE QUERY LOGGING statements in case a Sysinit is ever required (for disaster recovery or to migrate to a new platform).

   After the database has been restored, you can start a BTEQ session and run the script to easily repopulate the DBQLRuleTbl and rebuild your rules cache.

2. Offload the logs daily to archival tables by using the Viewpoint portlet Performance Data Collection. You must run the DIPPDCR script first to create those tables.Set up a regular schedule to periodically perform the following maintenance:
   1. Regularly summarize the data you want to retain permanently for analysis and planning. You can save it in a user database, external files, applications, or BTEQ reports, and so forth.
   2. Then submit the statement DELETE * FROM DBQL tablename ; on each of the DBQL log tables to empty them and free up DBC PERM space.