Overview of Collection Options - Teradata Database

Overview of Collection Options

DBQL is flexible enough to log information on the variety of SQL requests that run on Teradata Database, from short transactions to longer-running analysis and mining queries.

Options enable you to control the volume and detail of the logged data. You can define rules, for example, 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. You could even log queries that exceed a specific CPU time specified in hundredths of a second. For example, specify “THRESHOLD=500 CPUTime” for queries that take longer than five seconds of AMP CPU time to complete.

The “WITH Logging Options” controls what details the system logs. The “LIMIT Logging Options” controls how much of information the system logs. Collection options include:

Default logging reports for each query with at least the leading SQL characters, the time of receipt, the number of processing steps completed, the time the first step was dispatched, and the times the packets were returned to the host.

Summary logging reports the count of all queries that completed processing time within 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 elapsed time, I/O counts, or CPU time as a criterion for threshold logging of details or summary

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

XML query plan

Preventing logging for a specific user, user/account pair, user/account list, or application name.

Logging Rules for Applications

Enable or disable logging for a utility by specifying a rule for the application name. The system logs information for any user that logs on under the application name. For example, to log all queries that result from a FastLoad job, submit:

BEGIN QUERY LOGGING ON APPLNAME= 'FASTLOAD';

The application names you specify with the APPLNAME option are the names the system passes in the LogonSource string. (For a list of the commonly seen LogonSource string application names, see “Application Names” in SQL Data Definition Language Detailed Topics.)

DBQL first searches for a rule for an application name before searching for rules for the user and account.

Creating logging rules by application name allows control over certain utilities, such as load and unload utilities, regardless of what user/account that the application runs under. For example, if you run utilities under the same users and accounts that are used for interactive queries, creating logging rules for utilities allows you to specify minimal logging for utilities and more extensive logging for interactive users.