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.

However, when logged on as user DBC, SystemFE, or administrative user with the proper privileges, you can delete rows in the DBQL logs. This is necessary for controlling their size. It is a best practice to regularly offload the DBQL data from DBC and into another database.

Note: Offload the DBQL logs from DBC as often as possible. Query logging consumes DBC PERM space and the rows remain in the DBQL tables until you remove them. Even though logging is not designed as a standard operation to be performed against all users all the time, the tables can fill up very quickly. Especially if you are tracking target objects for a large group of users.

Note: Do not offload data during peak busy times as this may block ongoing queries.

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 Set up a regular schedule to periodically perform the following maintenance:

a 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.

b Then submit the statement DELETE * FROM DBQL tablename; on each of the DBQL log tables to empty them and free up DBC PERM space.

Schedule clean up activities during off-peak times. You may want to consider disabling DBQL logging before you perform clean up activities on the logs although this is recommended but not necessary. Otherwise, the delete process locks the DBQL table and if DBQL needs to flush a cache to the same table to continue logging queries, the whole system could experience a slow-down.

Database Query Log Setup and Maintenance Scripts are available online at http://downloads.teradata.com/download.