To use DBQL:
- Grant the proper privileges to your administrative user for query logging. See Granting DBQL Administrative Privileges to Other Users.
- Determine what type of information you want to collect. This section lists the descriptions of each field in each DBQL log. Use the descriptions to get an idea of what type of information you want to collect and then look at the options you can employ to control the type of details and the amount of data logged. The options are discussed in WITH Logging Options and LIMIT Logging Options.
- Determine what you want to collect data for. Create DBQL rules
to log queries of a user, group of users, an account, a list of accounts, or an
application.
Teradata recommends creating rules for accounts so that queries will be logged for any user who logs in under that account.
- Run the Database Initialization Program (DIP) script DIPPDCR to create archival tables for longer-term storage of DBQL and other performance data. DIPPDCR also creates the data extraction macros that populate these tables.
- Run the Viewpoint portlet Performance Data Collection to set up a recurring daily DBQL job. This job moves data from the DBQL log tables in DBC to the archival tables in the PDCRDATA database.
- Use the BEGIN QUERY LOGGING statement to create logging rules or
use the REPLACE QUERY LOGGING statement to replace existing logging rules with
new logging rules. Teradata recommends using scripts to enable and disable
logging to avoid typographical errors.You must have the EXECUTE privilege on the DBC.DBQLAccessMacro macro to enable or disable DBQL logging. That is, you must have this privilege to submit the BEGIN QUERY LOGGING, REPLACE QUERY LOGGING, and END QUERY LOGGING statements.
As users establish sessions and submit queries, DBQL will automatically enable rules according to the Best Fit Hierarchy. (See Best Fit Rule Hierarchy.)
DBQL collects query information into a data cache based on rules you specify, flushes the cache periodically (see Options for Flushing the DBQL Cache), and writes the information to the DBQL dictionary tables. This information includes historical records of queries and their duration, performance data, and target activity.
- Upon examining the query log data, you may decide you need to adjust rules. You can use the REPLACE QUERY LOGGING statement to replace or delete existing rules using the END QUERY LOGGING statement.