Database Query Log (DBQL) in Teradata

DBQL provides a series of pre-defined tables that can store, based on rules you specify, historical records of queries and their duration, performance, and target activity.

DBQL is flexible enough to log information on the variety of SQL requests that run on the Teradata Database, from short transactions to longer-running analysis and  mining queries. You begin and end collection for a user, group of users, account, or a list of accounts.

Collection option include:

• Default logging reports for each query with at least the leading SQL characters, the time of receipt, the number of processing steps completed, and the time the first step was dispatched, and the times the packets were returned to the host.
• Summary logging reports at each logging interval the count of all queries that completed processing time within the specified time intervals, I/O criteria, or CPU usage criteria. 
• Threshold logging can log a combination of default and summary data:
• Default data for each query that ran beyond the threshold limit
• Summary counts of all queries that ran within the threshold time
• Use I/O or CPU time as a criterion for detail or summary logging
• Detail logging, which includes:
• Default Data
• Any or all of the following:
• Step level Activity, including parallel steps
• Object Usage per Query
• Full SQL text
• Explain Text

Note: Any DBC database tables and columns used by the system while processing a query are not reflected in the DBQL object rows for that query. This means, for example, that statements like CREATE TABLE or SELECT FROM DBC.xxx will not have objects logged through DBQL because they deal with DBC tables and columns.

Also, DBQL is limited to logging information about base tables and logging direct SQL statements. Macros, Views and triggers are not reported.

The DBQL Components

The DBQL logs are a series of system tables created in database DBC during the Teradata Database installation process. The suite of DBQL components includes a security macro and a view for each table, which are created in database DBC by the DIP utility.

DBQL tables that are being populated include:

• DBQLogTbl  table stores default rows. The foundation of the DBQL feature.
• DBQLObjTbl  table stores information on the target objects of the query being logged. One row is logged for each object referenced in the query.
• DBQLSQLTbl  table logs the full SQL statement text, no matter how large. Multiple rows are generated if necessary.
• DBQLStepTbl  table logs the AMP step-level information of the query being logged
• DBQLExplainTbl  table logs the full unformatted Explain text.

DBQL Data Capture

Like other system tables, the predefined DBQL logs are created as relational tables in database DBC during normal Teradata Database 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 QUERY LOGGING statement, with or without options.

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. Or you could define a rule to log queries that take more time to complete than the specified time threshold or queries with CPU usage that exceed a specific CPU hundredths of a second.

DBQL is controlled by the Teradata SQL statements BEGIN QUERY LOGGING and END QUERY LOGGING. Only a user with EXECUTE privilege on DBC.DBQLAccessMacro can invoke the statements.

DBQL Log Table

Default rows are stored in DBQLogTbl, the foundation of the DBQL feature.

When you do not specify a LIMIT SUMMARY or LIMIT THRESHOLD option, one default row of query-level information is logged in DBQLogTbl for each query processed during a session that is initiated by any user for whom a query logging rule exists.

If you specify options that result in more detailed information, a default row is still generated in DBQLogTbl (except with the SUMMARY option or a query that completes within the limit specified with the THRESHOLD option), plus one or more additional logs are populated with one or more additional rows.

Note: DBQL is limited to logging information about base tables and logging direct SQL statements. Macros, views, and triggers do not result in complete logging information.

The Default Row

The fields of the default row provide general query information that is usually adequate for investigating a query that is interfering with performance. When no options are specified, a default row includes at least the:

• User ID and user name under which the session being logged was initiated
• Unique ID for the process, session, and host (client) connection
• Account string, expanded as appropriate, that was current when the query completed
• First 200 characters of the query SQL statement
• Times - Start and Last Response
• CPU and I/O statitics
• Default Database name that was current when the query completed
• Numerous other performance metrics

DBQL Object Table

With the OBJECTS option, DBQL logs one row in DBQLObjTbl for each data object referenced by the query. An object can be a database, data table, column, secondary index, join index, or journal table. (If the object is a secondary index, its number is logged rather than a name.) Also logged is a count of the number of times the object was accessed by the optimizer.

Note: Any DBC database tables and columns used by the system while processing a query are not reflected in the DBQL object rows for that query. This means, for example, that statements like CREATE TABLE or SELECT FROM DBC.xxx will not have objects logged through DBQL because they deal with DBC tables and columns.

Only objects from base tables are logged. Objects from views and macros are not logged.

Scenario 1

User runs a SELECT statement that joins two tables owned by the same database:

• One row for the query is logged in DBQLogTbl
• Rows are logged in DBQLObjTbl as follows:
• A row for the database
• One row for each table
• One row for each selected column

Scenario 2

User runs a query that causes the Optimizer to reference the same object twice:

• One row for the object is logged in DBQLObjTbl
• The value in the FreqofUse field is incremented to 2.

DBQL SQL Table

The DBQLSQLTbl is populated if you specify the SQL option in addition to default information. The SQL option logs the full statement text, no matter how large, into DBQLSqlTbl. Multiple rows are generated as necessary.

Note: The full SQL text of a CREATE/ALTER/REPLACE PROCEDURE/ FUNCTION is not logged in DBQLSQLTbl when the statement is submitted with the option to not save the source in the database.

DBQL STEP Table

DBQLStepTbl is populated if you specify the STEPINFO option. When the query completes, the system logs one row for each query step, including parallel steps.

DBQL EXPLAIN Table

This table is populated if the WITH EXPLAIN option is requested in addition to the default information row. If the additional Explain text is greater than 64KB, multiple rows are generated.The DBQLExplainTblis populated with unformatted explain text.