15.10 - DBQLRulesV - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

Query

DBC

 

View Column

Data Type

Format

Source Table.Column

UserName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

AccountString

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

DBQLRuleTbl.AccountString (Composite UPI)

ApplName

CHAR(30) LATIN

NOT CASESPECIFIC

NOT NULL

X(30)

DBQLRuleTbl.ApplName (Composite UPI)

TypeOfRule

VARCHAR(22)

UNICODE

X(22)

DBQLRuleTbl.TypeOfCriterion

ExplainFlag

CHAR(1) LATIN

NOT CASESPECIFIC

NOT NULL

X(1)

DBQLRuleTbl.ExplainFlag

ObjFlag

CHAR(1) LATIN

NOT CASESPECIFIC

NOT NULL

X(1)

DBQLRuleTbl.ObjFlag

SqlFlag

CHAR(1) LATIN

NOT CASESPECIFIC

NOT NULL

X(1)

DBQLRuleTbl.SqlFlag

StepFlag

CHAR(1) LATIN

NOT CASESPECIFIC

NOT NULL

X(1)

DBQLRuleTbl.StepFlag

XMLPlanFlag

CHAR(1) LATIN

NOT CASESPECIFIC

NOT NULL

X(1)

DBQLRuleTbl.XMLPlanFlag

StatsUsageFlag

CHAR(1) LATIN

NOT CASESPECIFIC

NOT NULL

X(1)

DBQLRuleTbl.StatsUsageFlag

Verbose

CHAR(1) LATIN

NOT CASESPECIFIC

X(1)

DBQLRuleTbl.DBQLOptions

DetailedStats

CHAR(1) LATIN

NOT CASESPECIFIC

X(1)

DBQLRuleTbl.DBQLOptions

NoColumns

CHAR(1) LATIN

NOT CASESPECIFIC

X(1)

DBQLRuleTbl.DBQLOptions

SummaryFlag

CHAR(1) LATIN

NOT CASESPECIFIC

NOT NULL

X(1)

DBQLRuleTbl.SummaryFlag

ThresholdFlag

CHAR(1) LATIN

NOT CASESPECIFIC

NOT NULL

X(1)

DBQLRuleTbl.ThresholdFlag

ObjectUsage

CHAR(1) LATIN

NOT NULL

X(1)

DBQLRuleTbl.ObjectUsage

ParamFlag

CHAR(1) LATIN

NOT CASESPECIFIC

X(1)

DBQLRuleTbl.ParamFlag

UtilityInfoFlag

CHAR(1) LATIN

NOT CASESPECIFIC

X(1)

DBQLRuleTbl.UtilityInfoFlag

TextSizeLimit

INTEGER

--,---,---,--9 (explicit)

DBQLRuleTbl.TextSizeLimit

SummaryVal1 through SummaryVal3

SMALLINT

---,--9 (explicit)

DBQLRuleTbl.SummaryVal1 through DBQLRuleTbl.SummaryVal3

LockDelay

INTEGER

--,---,---,--9

DBQLRuleTbl.LockDelay

AlgMode

BYTEINT

--Z(2)9

DBQLRuleTbl.AlgMode

TypeOfCriterion

VARCHAR(22)

UNICODE

X(22)

DBQLRuleTbl.TypeOfCriterion

DetailDiag

INTEGER

NOT NULL

--,---,---,--9

DBQLRuleTbl.DetailDiag

Only users with DBC or SystemFE privileges are able to use this view.

LockDelay

The LockDelay column returns the minimum elapsed time of a lock contention in centiseconds. Any lock contention greater than this time will be recorded in the DBC.DBQLXMLLockTbl table as specified in the BEGIN QUERY LOGGING WITH LOCK statement. The minimum LockDelay value is 5 centiseconds.

Possible Values for DetailedStats

 

Value

Description

T

Detailed statistics are provided.

F

Detailed statistics are not provided.

Possible Values for ExplainFlag

 

Value

Description

T

EXPLAIN text is logged.

F

EXPLAIN text is not provided.

Possible Values for NoColumns

The NoColumns column indicates if columns are logged as part of object logging. When query logging is enabled with the OBJECTS option, a separate row for each table and column referenced is added to the DBQL object table. With NoColumns set to T, the columns are not logged.

 

Value

Description

T

Query logging is enabled without column logging.

F

Query logging is enabled with column logging.

Possible Values for ParamFlag

 

Value

Description

T

Query parameters and values are logged.

F

Query parameters and values are not logged.

Possible Values for ObjFlag

 

Value

Description

T

Object data is logged.

F

Object data is not logged.

Possible Values for ObjectUsage

 

Value

Description

T

Object use counts are logged.

F

Object use counts are not logged.

Possible Values for SQLFlag

 

Value

Description

T

SQL text is logged.

F

SQL text is not provided.

Possible Values for StatsUsageFlag

 

Value

Description

T

Statistics usage is logged.

F

Statistics usage is not logged.

Possible Values for StepFlag

 

Value

Description

T

Step-level data is logged.

F

Step-level data is not provided.

Possible Values for SummaryFlag

 

Value

Description

T

Summary information is logged.

F

Summary information is not provided.

Possible Values for ThresholdFlag

 

Value

Description

T

Counts the queries that are shorter or equal to SummaryVal1 in DBC.DBQLSummaryTbl. Provides detailed data on longer queries in DBC.DBQLogTbl.

F

Provides detailed data for all queries in DBC.DBQLogTbl unless the SummaryFlag is True.

Possible values for TypeOfCriterion

 

Value

Description

0

ElapsedSec (Elapsed seconds)

1

CPUTime (CPU time in centiseconds)

2

IOCount (I/O count)

3

CPUTimeNorm (Normalized CPU time in centiseconds for coexistence systems)

4

ElapsedTime (Elapsed time in centiseconds)

5

No logging criterion.

Possible values for TypeOfRule

  • Logging enabled
  • WITH NONE (No logging)
  • Possible Values for UtilityInfoFlag

     

    Value

    Description

    T

    TASM utility statistics are logged.

    F

    TASM utility statistics are not logged.

    Possible Values for Verbose

     

    Value

    Description

    T

    Verbose EXPLAIN and related information in XML format are provided.

    F

    Verbose EXPLAIN and related information in XML format are not provided.

    Possible Values for XMLPlanFlag

     

    Value

    Description

    T

    XML query plans are logged.

    F

    XML query plans are not logged.

    SummaryVal1, SummaryVal2, and SummaryVal3

     

    Column

    Description

    SummaryVal1

    Returns the high value for interval 1 if the SummaryFlag or ThresholdFlag columns are T.

    This value can be in seconds, CPU centiseconds, or I/Os.

    SummaryVal2

    Returns the high value for interval 2 if the SummaryFlag or ThresholdFlag columns are T.

    This value can be in seconds, CPU centiseconds, or I/Os.

    SummaryVal3

    Returns the high value for interval 3 if the SummaryFlag or ThresholdFlag columns are T.

    The same value is used for interval 4.

    This value can be in seconds, CPU centiseconds, or I/Os.

    The following SELECT statement retrieves the rules in effect for users:

    SELECT * from DBC.DBQLRulesV;

    Result:

           UserName  All
            Account
    ApplicationName
         TypeOfRule  Logging enabled
            Explain  F
             Object  F
                SQL  F
               Step  F
            XMLPlan  F
         StatsUsage  F
            Verbose  F
      DetailedStats  F
            Summary  F
          Threshold  T
        ObjectUsage  F
              Param  F
        UtilityInfo  F
           TextSize           1,000
       Summary//Low        3
                Med        ?
               High        ?
          LockDelay               0
            AlgMode      ?
    TypeOfCriterion  ElapsedSec
         DetailDiag               ?

    Note: This rule was initiated as:

    BEGIN QUERY LOGGING LIMIT THRESHOLD = 3 and SQLText =1,000 on All;

    Queries longer than 3 seconds are logged in the DBQLogTbl (QryLog view). Shorter queries are counted and appear in the DBQLSummaryTbl (QryLogSummary view).

    Summary Low is for either Threshold or Summary.