Download (direct link):
- Object usage per query
- Full SQL text
Populating the Log Tables
Like other system tables, the predefined DBQL logs are created as relational tables in database DBC during normal Teradata RDBMS installation. However, while most system tables are populated automatically, you can choose whether you want to populate the DBQL tables.
If you choose not to use the feature, the tables remain empty. If you want to use the feature, simply submit a BEGIN/END QUERY LOGGING statement, with or without options, to control the start, magnitude, and end of logging activity.
The options enable you to control the volume and detail of the logged data. You can define rules, for instance, that log the first 5,000 characters of any query that runs during a session invoked by a specific user under a specific account, if the time to complete that query exceeds the specified time threshold.
14 - 2
Teradata RDBMS Database AdministrationChapter 14: Tracking Processing Behavior with the Database Query Log (DBQL)
The DBQL Components
The BEGIN/END QUERY LOGGING Statements
DBQL is controlled by the Teradata SQL statements BEGIN QUERY LOGGING and END QUERY LOGGING. Only a user with DBC or SystemFE privileges can invoke the statements. (For statement syntax, see Teradata RDBMS SQL Reference, Volume 4. To enable other users to submit these statements, see "Logging Implementation and Validation" on page 14-21.)
The purpose of each statement is as follows:
Type Statement Purpose Reference
Teradata SQL extensions to the DCL repertoire BEGIN QUERY LOGGING When submitted by a user with EXECUTE privileges on DBQLAccessMacro, enables logging for the named users and/or accounts. (NCR recommends a maximum of 100 user/account pairs per statement.) For active sessions, logging begins when the next query is received. "Logging Scenarios" on page 14-23.
END QUERY LOGGING When submitted by a user with EXECUTE privileges on DBQLAccessMacro, stops logging for the named users and/or accounts. For up to 100 active sessions, a routine is called that commits the data and flushes the cache. "Dynamically Enable/Disable Logging" on page 14-19.
The Statement Options
The options to the BEGIN QUERY LOGGING statement include the following:
Parameter Logging Behavior
WITH ALL ALL logs all the information generated by all the WITH rules (OBJECT, SQL, and STEPINFO). No other WITH rule is necessary, because ALL generates: • One default row per query in DBQLogTbl • One row per target object per query in DBQLObjTbl • One row per step per query in DBQLStepTbl • One or more rows per complete SQL statement in DBQLSQLTbl (plus, unless you define LIMIT SQLTEXT=0 , the first 200 characters of the statement in the default row) Caution: Use this option sparingly and only for selected users, because it can consume excessive CPU resources and grow the logs (which consume DBC PERM space) very quickly.
WITH OBJECTS This option inserts • One row per target object per query in DBLObjTbl • A default row in DBQLogTbl. Use this option selectively. Object data is useful for analyzing queries that make heavy use of join indexes and indexed access, but can generate many rows.
WITH SQL This option logs the entire SQL statement for each request for each user being logged. Large statements can cause multiple rows to be written in order to log the full query text. Note: Also set LIMIT SQLTEXT=0 if you specify the WITH ALL option, which also logs SQL.
14 - 2 Teradata RDBMS Database Administration
Chapter 14: Tracking Processing Behavior with the Database Query Log (DBQL)
The DBQL Components
Parameter Logging Behavior
WITH STEPINFO This option inserts one row per step per query in the DBQLStepTbl. Use this option selectively. Although step data is useful for analyzing queries, this option can generate many rows.
LIMIT SQLTEXT Use this option if you want to capture less than or more than the first 200 characters in the default row. To turn off text capture completely, specify 0 (zero). The maximum limit is 10,000 characters. If you specify the option keyword but not a value, up to 10,000 characters are logged in DBQLogTbl. To store the complete statement regardless of length, specify the SQL option; as many rows as needed to contain the full text will be logged in DBQLSQLTbl. (If you do this, define LIMIT SQLTEXT=0 to avoid redundant logging in both the default row and DBSQLTbl.) Note: Also set LIMIT SQLTEXT=0 if you specify the WITH ALL option, which also logs SQL.
LIMIT SUMMARY SUMMARY is useful for tracking voluminous short queries, such as for OLTP applications, because it does not grow the DBQLogTbl. It simply counts queries based on specified time differentials and stores the count results in DBQLSummaryTbl. (For an output example, see "DBQLSummaryTbl" on page 14-12.) The SUMMARY option is unique in that it: • Does not generate default rows in DBQLogTbl • If the count is greater than 0, writes the summary row and flushes the DBQL cache segment at system-controlled 10-minute intervals • If the count is less than 0 for a 10-minute interval, no data is written