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.
- 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 |