Reviewing Rules - Teradata Database

Teradata Database Administration
Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
Product Category
Software

Reviewing Rules

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.

    Note: 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 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='$LTest&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.

    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.