Overview of Collection Options | Teradata Vantage - 17.00 - 17.05 - Overview of Collection Options - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval

DBQL is flexible enough to log information on the variety of SQL requests that run on Vantage, 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, the times the packets were returned to the host, and the number of rows updated, inserted, and deleted.
  • 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
    • 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';

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.

The application names you specify with the APPLNAME option are the names the system passes in the reserved query band UtilityName.

The following table lists the UtilityName strings.

Client/DBS Protocol Utility Type Utility Name Value
FastExport Standalone FastExport FASTEXP
Teradata Parallel Transporter EXPORT operator TPTEXP
JDBC FastExport JDBCE
.NET FastExport DOTNETE
FastLoad Standalone FastLoad FASTLOAD
Teradata Parallel Transporter LOAD operator TPTLOAD
JDBC FastLoad JDBCL
.NET FastLoad DOTNETL
Crashdumps Save Program (CSP) Save Dump CSPLOAD
MultiLoad Standalone MultiLoad MULTLOAD
Teradata Parallel Transporter UPDATE operator TPTUPD
JDBC MultiLoad JDBCM
.NET MultiLoad DOTNETM