How to Use DBQL
To use DBQL:
1 Grant the proper privileges to your administrative user for query logging. See “Granting DBQL Administrative Privileges to Other Users” on page 390.
2 Determine what type of information you want to collect. This chapter 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” on page 267 and “LIMIT Logging Options” on page 274.
3 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.
4 Create temporary and history tables to hold log data in another database. For example, name these tables DBQLogTbl_Temp or DBQLStepTbl_Hist to associate the actual DBQL log with the temporary and history tables. Using these tables to load the DBQL data off DBC keeps DBC from running out of space.
5 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 can also use Query Logging under the Tools menu of the Teradata Administrator utility to create, enable, replace, and disable DBQL rules.
Note: 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” on page 262.)
DBQL collects query information into a data cache based on rules you specify, flushes the cache periodically (see “Options for Flushing the DBQL Cache” on page 372), and writes the information to the DBQL dictionary tables. This information includes historical records of queries and their duration, performance data, and target activity.
6 During off-peak hours, load DBQL data off the DBC into your temporary tables in another database. Teradata recommends doing this nightly so that the DBQL logs do not take up space in DBC and impact the system due to space it needs for other system processing.
7 You can then load the data off these temporary tables into history tables for analysis and reporting. Examine data, create reports, and archive or delete old log information as necessary. Database Query Log Setup and Maintenance Scripts are available online at http://downloads.teradata.com/download.
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.