16.10 - Reviewing Rules - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

You can review DBQL rules one of two ways:

  • Use the SHOW QUERY LOGGING statement to determine which rule would be used for a specified user/account/application. For example:
    SHOW QUERY LOGGING ON marketinguser;
  • Use the DBC.DBQLRules[V] view as a window into the contents of the DBC.RuleTbl which stores all DBQL rules in the system.

The following table describes what kind of rules the system checks for depending on the SHOW QUERY LOGGING statement you submit. If you only list a user name or an account string, the SHOW QUERY LOGGING statement shows the Best Fit Rule.

Only a user with EXECUTE privilege on DBC.DBQLAccessMacro can execute the SHOW QUERY LOGGING statement.
IF you submit a SHOW QUERY LOGGING statement for… THEN the system will show…
ALL the first rule in the hierarchy that applies, looking only at 
all/all accounts
user1 acct1 the first rule in the hierarchy that applies, looking at:
  • user1/account1
  • user1/all accounts
  • all/account1
  • all/all accounts
user2 the first rule in the hierarchy that applies, looking only at:
  • user2/all accounts
  • all/all accounts
all acct3 the rule hierarchy (in hierarchy order) that would apply for any user that logged on under acct 3. The system will look only at:
  • all/account3
  • all/all accounts
APPLNAME='xxx' APPLNAME='xxx' which is the only APPLNAME rule that applies.

The DBC.DBQLRules[V] view provides a window into the contents of the DBQLRuleTbl table and shows which rules would be applied by the system. Only a user with SELECT privilege on DBC.DBQLRules[V] can access the view. For Teradata systems configured with Teradata Secure Zones, access to some Data Dictionary views is constrained by zone. DBQLRules[V] is constrained by zone. For more information, see Data Dictionary.

A SELECT on the DBQLRules[V] view displays the rules currently in effect. You can qualify the response by user name, or account string (if it exists), or both. For example:

SELECT * FROM DBC.DBQLRULESV WHERE ACCOUNTSTRING='$L00Test&D&H';

The following table describes the fields of DBC.DBQLRules[V]:

DBC.DBQLRules[V] Fields Description
AccountString The default or specified user account.
ApplName The application name for the rule.
AlgMode The DBQL CPU/IO Collection algorithm to use for this rule.
StatsUsageFlag Indicates whether statistics usage is logged. The possible values are T (true) or F (false).
ExplainFlag Whether explain text is stored. Possible values are T (true) or F (false).
LockDelay An integer indicating in centiseconds the minimum threshold length of a lock contention. Any lock contention greater than this length will be recorded in DBC.DBQLXMLLOCKTbl.
AlgMode For the BEGIN/REPLACE QUERY LOGGING options:
  • STATSUSAGE: displays if the DETAILED suboption is enabled (T) or disabled (F).
  • XMLPLAN: displays if the VERBOSE suboption is enabled (T) or disabled (F).
DetailDiag Detailed diagnostic control information represented in bit format.
DetailedStats Whether detailed statistics are being logged. Possible values are T or F.
FeatureUsage Whether feature usage information is being logged. Possible values are T or F.
ObjFlag Whether object data (columns and indexes) are stored. Possible values are T or F.
NoColumns Whether columns are logged as part of object logging. Possible values are T or F.
ParamFlag Whether parameters and values are stored for parameterized requests. Possible values are T or F.
SqlFlag Whether SQL text is stored. Possible values are T or F.
StepFlag Whether step-level data is stored. Possible values are T or F.
SummaryFlag Whether summary information is stored in DBC.DBQLSummaryTbl. Possible values are T or F.
SummaryVal2 Returns the high value for interval 2 if SummaryFlag is True.

The value can be in seconds, I/Os, or hundredths of CPU seconds (see TypeOfCriterion).

SummaryVal3 Returns the high value for interval 3 if SummaryFlag is True.

This same value is used for interval 4.

The value can be in seconds, I/Os, or hundredths of CPU seconds (see TypeOfCriterion).

TextSizeLimit Indicates the number of characters of SQL text to be stored.
ThresholdFlag Possible values are:

T = Count the queries that are shorter or equal to SummaryVal1 in DBC.DBQLSummaryTbl. Provide detailed data on longer queries in DBC.DBQLogTbl.

F = Provide detailed data for all queries in DBC.DBQLogTbl unless the SummaryFlag is True.

TypeOfCriterion Specify one of the following criteria with summary and threshold options:
  • ElapsedSec: Elapsed seconds
  • CPUTime: CPU time (hundredths of a second)
  • IOCount: I/O count
  • CPUTimeNorm: Normalized CPU time (hundredths of a second) for co-existence systems
  • ElapsedTime: Elapsed time (hundredths of a second)
  • No criteria
TypeOfRule Whether logging is enabled or disabled by this rule. The value is either:
  • Logging enabled
  • WITH NONE (No logging)
UserName The user name associated with the rule.
Verbose Whether verbose EXPLAIN and related information in XML format are provided. Possible values are T or F.
XMLPlanFlag Whether XML plans are stored. Possible values are T or F.