Scenarios of Logging Accounts - Advanced SQL Engine - Teradata Database

Database Administration
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantage™
IF you… THEN …
submit one of the following statements:

BEGIN QUERY LOGGING ON ALL;

Or

REPLACE QUERY LOGGING ON ALL;

the system creates one rule for the user named “ALL.” If the DBQL rule for ALL is in the system, each query is logged for every user that logs on.
submit one of the following statements:

BEGIN QUERY LOGGING ON ALL ACCOUNT = 'ABC';

Or

REPLACE QUERY LOGGING ON ALL ACCOUNT = 'ABC';

any user that logs on with the account string 'ABC' will be logged.
begin query logging for a specific account for a user, but the account does not exist the BEGIN QUERY LOGGING statement is accepted (accounts do not need to exist).

DBQLRules[V] shows a rule for the user, but queries run by that user are not logged because the sessions never match the user/account/application triplet.
begin query logging for a specific account (define a rule) for a user, and that user logs on under an account that does not match the rule no rows are logged for any queries run during that session.
want to log SQL for all users except specifically when user2 logs on under account 'ABC' submit the following statements:

BEGIN QUERY LOGGING WITH SQL ON ALL;

BEGIN QUERY LOGGING WITH NONE ON myuser2 ACCOUNT='abc';

If user2 issues a query under account ABC, the system will not log the query. However, if user2 logs on with another account such as DEF, the system will log the query and DBQLSQLTbl will show the SQL for user2/DEF queries.

To stop these logging rules, submit the END QUERY statement for each rule. The rule for user2 must be specifically removed with a separate END QUERY LOGGING statement.

END QUERY LOGGING ON ALL;

END QUERY LOGGING ON myuser2 ACCOUNT='ABC';

begin query logging for a specific account for a user, and the account includes account string expansion (ASE) codes both the input account string and the expanded account string are logged. For details on ASE codes, see Logging Resource Usage Data with Account String Variables.

The system automatically strips away leading blanks and converts all letters in an account string to upper case before storing it in DBC.DBQLRuleTbl.
submit the following:

BEGIN QUERY LOGGING WITH OBJECTS ON ALL ACCOUNT='WebQry&D&H';

A row in DBQLObjTbl and DBQLogTbl for each object for each query during each 'WebQry&D&H' session.
submit the following:

BEGIN QUERY LOGGING WITH STEPINFO ON ALL ACCOUNT='$HTactQry&D&H';

A row in DBQLStepTbl and DBQLogTbl for each step of each query during each '$HTactQry&D&H' session.
submit the following:

BEGIN QUERY LOGGING WITH SQL, STEPINFO, OBJECTS LIMIT THRESHOLD=3 ON ALL ACCOUNT='$LStratQry&D&H';

For each '$LStratQry&D&H' session:
  • One row of count data in DBQLSummaryTbl for all queries that completed in less than three seconds (within the 10-minute logging intervals)
  • For each query that ran longer than three seconds, one row of data is logged in DBQLSQLTbl, DBQLStepTbl, DBQLObjTbl, and DBQLogTbl
If you define more than one account string in your SQL statement, use a comma to separate the delimited list and enclose the account strings in parentheses. See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 for more information on syntax.