Main Log Table: DBQLogTbl - Teradata Database

Teradata Database Administration

prodname
Teradata Database
vrm_release
15.10
category
Administration
featnum
B035-1093-151K

Default rows are stored in the DBC.DBQLogTbl table, the foundation of the DBQL feature. The default is one default row per query.

Use the LIMIT SUMMARY and LIMIT THRESHOLD options to control whether a default row is logged for a query. Using:

  • LIMIT THRESHOLD reduces the amount of logged rows to one row for each query above the threshold limit. Other queries are counted in the DBQLSummaryTbl.
  • LIMIT SUMMARY eliminates default row logging and counts all queries in four “buckets” based on the limit criteria.
  • In addition to the default row logging, you may specify a WITH option to log OBJECTS, SQL, STEPINFO and/or EXPLAIN. The LIMIT THRESHOLD can accept SQL, STEPINFO and/or OBJECTS combination.

    Note: If you specify the WITH options that result in more detailed information, a default row is still generated in DBQLogTbl.

    The Default Row

    The fields of the default row provide general query information that is usually adequate for investigating a query that is interfering with performance. When no options are specified, a default row includes:

  • User ID and user name under which the session being logged was initiated
  • Unique ID for the process, session, and host (client) connection
  • Account string, expanded as appropriate, that was current when the query completed
  • First 200 characters of the query SQL statement
  • CPU and I/O statistics
  • Default database name that was current when the query completed
  • Row Values

    The available fields in a DBQLogTbl row are listed in the following table.

     

    DBC.DBQLogTbl Field

    Description

    AbortFlag

    If the query being logged was aborted, the collected data could be incomplete.

    Note: A query consisting of a multistatement or iterated request with simple INSERT statements can be successful and yet have statement errors, as indicated by the ErrorCode column.

    AcctString

    The account string at the time the row was committed. Because accounts can be changed dynamically at the query or session level, this may not be the same account that:

  • DBQL verified for the user at logon time
  • Was in effect when processing of the query began
  • AcctStringDate

    Value of the expanded &D ASE code in the account string. If none exists, the value is denoted with a “?” and is null.

    Even if ASE truncates the expanded account string, DBQL will report the full value. For example, if &D is expanded into position 26 or higher in the account definition and truncation occurs, DBQL will still report the entire value of the date in the AcctStringDate field.

    AcctStringHour

    Value of the expanded &H ASE code in the account string. If none exists, the value is denoted with a “?” and is null.

    AcctStringTime

    Value of the expanded &T ASE code in the account string. If none exists, the value is denoted with a “?” and is null.

    Even if ASE truncates the expanded account string, DBQL will report the full value.

    AMPCPUTime

    Total AMP time used for the query in CPU seconds.

    Note: The I/O, CPU, and spool usage may not be accurate for aborted queries.

    AMPCPUTimeNorm

    Normalized AMP CPU seconds for co-existence systems.

    AppID

    Application (executable) or utility name; for example, BTEQ from LogonSource for network clients.

    CacheFlag

    This field is blank if the query is not found in the request cache. It could also be one of the following:

  • “T” if the query plan is generic and in the request cache.
  • “G” if the query plan is generic and not in the request cache but is a candidate for caching in subsequent runs of the same query.
  • “S” if the query plan is specific (either a static or dynamic plan).
  • “A” if a Specific Always decision is taken. That is, for each query USING values are peeked during request parsing. The optimizer may choose a dynamic plan with results feedback.
  • For more information on specific plans and generic plans, see “Peeking at Parameterized Values in the Data Parcel” in SQL Request and Transaction Processing.

    CalendarName

    The name of the business calendar the session uses. Possible values include ISO, COMPATIBLE, and TERADATA (the default).

    CallNestingLevel

    The level of nesting when running in stored procedures.

    CheckpointNum

    The checkpoint number when loading data into an MLOADX staging table. The default is zero.

    ClientAddr

    The client IP address of the submitted query as obtained from Logonsource from a network client.

    ClientID

    The network client user name under which this session is logged (for example, ADMIN) and the PK/FK for the table.

    CollectTimeStamp

    A date and time unique to each buffer cache, which changes for each new buffer. Part of the multi-column NUPI for the table (see also ProcID).

    This time will be set when the cache is ready to receive new data, not when rows are written to the database.

    For more information on CollectTimeStamp, see “Comparing CollectTimeStamp Value Between Tables” on page 380.

    CPUDecayLevel

    Contains the most severe decay level reached for CPU usage on any one node for requests using the default timeshare method in Priority Scheduler for SUSE Linux Enterprise Server 11 or above. Values are 0, 1, or 2. A value of 0 indicates that the request is running at the default priority. A value of 1 indicates that the priority level is reduced and the resource allotment is halved. A value of 2 indicates that the priority level is reduced again, the resource allotment is halved again, and it will not be reduced more.

    DataCollectAlg

    CPU/IO collection algorithm used by DBQL. Possible values include:

  • 1 = Use the classic algorithm with step adjustments.
  • 2 = Use AMP algorithm 2 (diagnostic only).
  • 3 = Use AMP algorithm 3 (includes information on aborted and parallel steps).
  • DBQLStatus

    Internal DBQL logging status. Zero indicates no status conditions. This field is NULL if you are not using DBQL CPU/IO Collection algorithm 3.

    DefaultDatabase

    Name of the default database for the query.

    DelayTime

    The seconds a query was delayed by the Teradata dynamic workload management software.

    DisCPUTime

    Dispatcher CPU time in seconds.

    DisCPUTimeNorm

    Dispatcher normalized CPU time in seconds.

    ErrorCode

    If greater than 0, this field is the number of the error message in ErrorText. This field contains a code if the query caused a Parser syntax error.

    ErrorText

    If not null, this field provides processing errors and Parser errors. For example, the text would explain that the query was not processed due to a Parser syntax error.

    EstMaxRowCount

    The largest number of rows handled by a single step in the query, as estimated by the Optimizer.

    EstProcTime

    Sum of minimum estimated processing time for steps in the query as generated by the Optimizer.

    Note: This time is reported in seconds.

    EstResultRows

    Estimated result rows as generated by the Optimizer.

    ExceptionValue

    Specifies what type of Teradata dynamic workload management software exception occurred.

    ExpandAcctString

    The expanded account name under which the query is submitted if account expansion is invoked.

    Note: The &I, which gives the host, session, and request number for the query is different for a stored procedure call. For stored procedure calls, the request number is the client request number which is the request of the CALL statement.

    ExtraField20

    The persistent portion of the DBQLogTbl.SpoolUsage column.

    ExtraField22

    The number of seconds the query was held to meet the TASM minimum response time.

    ExtraField23

    The total time (in seconds) from when the request was submitted to the time the first response was sent.

    FinalWDID

    Indicates the workload definition in which the query completed execution.

    FirstRespTime

    The timestamp when the first response packet is sent to the client unless the request is held by TASM to meet the minimum response time. In this case, it is the timestamp when the first response packet is ready to be sent to the client.

    FirstStepTime

    The timestamp when the first step is dispatched.

    InternalRequestNum

    The internal request number.

    For commands, other than those within a stored procedure, the internal request number and the number in the RequestNum field will be the same.

    For stored procedures invoked within a session, the internal request number increments by 1 for every request made by the stored procedure. This means that RequestNum will be the request number of the CALL and the value in InternalRequestNum will continue to increment for all other queries issued by the session.

    IODecayLevel

    Contains the most severe decay level reached for I/O usage on any one node for requests using the default timeshare method in Priority Scheduler for SUSE Linux Enterprise Server 11 or above. Values are 0, 1, or 2. A value of 0 indicates that the request is running at the default priority. A value of 1 indicates that the priority level is reduced and the resource allotment is halved. A value of 2 indicates that the priority level is reduced again, the resource allotment is halved again, and it will not be reduced more.

    IterationCount

    The number of data rows used by a parameterized request.

    KeepFlag

    Whether the response parcel should be kept until the client responds that it is no longer needed. The value is either Y or N.

    LastRespTime

    The timestamp of the last response. This field is NULL if you are not using DBQL CPU/IO Collection algorithm 3.

    LastStateChange

    Time of the last State change by the Teradata dynamic workload management software.

    LockDelay

    Maximum wait time to get a lock on an object in centiseconds. The maximum value is 32,767 centiseconds.

    LockLevel

    The highest lock level (Access/Read/Write/Exclusive) for locks associated with this request. For explicit transactions, the lock level for the transaction. Does not include row hash locks.

    LogicalHostID

    Unique identifier of the logon source for the logged query. A value of 0 indicates an internal session.

    LogonDateTime

    The timestamp of the start of the session.

    LogonSource

    The logon source string text.

    LSN

    The Logon Sequence Number used for a load utility.

    MaxAMPCPUTime

    CPU seconds of the highest CPU utilized AMP in the query.

    MaxAMPCPUTimeNorm

    Normalized maximum CPU seconds for an AMP.

    MaxAmpIO

    Logical I/O count of the highest utilized AMP in the query.

    MaxCPUAmpNumber

    The number of the AMP with the highest CPU activity.

    MaxCPUAmpNumberNorm

    Number of the AMP with the maximum normalized CPU seconds for co-existence systems.

    MaxIOAmpNumber

    The number of the AMP with the highest I/O usage for this step.

    MaxStepMemory

    The maximum amount of memory in MBs used by any one AMP in any step in this request. This field can be compared with the TDWM estimated memory usage field, TDWMEstMemUsage, also in DBQLogTbl. This field does not apply to INSERT/UPDATE/DELETE requests.

    MaxStepsInPar

    The maximum number of Level 2 steps done in parallel for the query.

    MinAmpCPUTime

    CPU seconds of the lowest CPU utilized AMP in the query.

    MinAmpCPUTimeNorm

    Normalized minimum CPU seconds for an AMP.

    Note: Normalized columns, such as MinAmpCPUTimeNorm, are for co-existence systems only.

    MinAmpIO

    Logical I/O count of the lowest logical I/O utilized AMP in the query.

    NoClassification

    Indicates if the query was not classified.

    NumFragments

    For a request for which a dynamic plan is generated, this field indicates the number of plan fragments. For a static plan, this field is NULL.

    NumOfActiveAmps

    Number of AMPs active for the query. Use it to compute the average CPU or I/O per AMP.

    NumRequestCtx

    The number of request contexts associated with the session.

    NumResultRows

    The total number of rows returned for the query.

    NumSteps

    Total number of Level 1 steps required to process the query. The value is 0 if:

  • The query was aborted or you ended logging for the user before steps were generated.
  • It is a PREPARE statement. The PREPARE statement is logged separately from the corresponding SQL.
  • NumStepswPar

    Total number of Level 1 steps with parallel steps.

    OpEnvID

    The internal identifier of the Operating Environment / Planned Environment currently enforced by the Teradata dynamic workload management software.

    ParserCPUTime

    Total Parser and Dispatcher CPU seconds used for the query.

    ParserCPUTimeNorm

    Normalized parser CPU seconds for co-existence systems.

    ParserExpReq

    Seconds the parser waited on an express request.

    ProcID

    Unique processor ID of the Dispatcher and part of the multi-column NUPI for the table (see also CollectTimeStamp).

    ProfileID

    The name of the profile, if any, under which the user submitted the query.

    ProxyRole

    The rolename of a proxy connection.

    ProxyUser

    The username of a proxy connection.

    ProxyUserID

    The ID of the ProxyUser being charged for spool and temp space. If the field is NULL, the session user ID was used for spool and temp space.

    QueryBand

    The query band under which the query is submitted.

    Possible values include:

  • =T> transaction query band pairs
  • =S> session query band pairs
  • =P> profile query band pairs
  • QueryID

    Internally generated identifier of the query and the FK to other DBQL tables.

    Note: QueryID is a system-wide unique field; you can use QueryID to join DBQL tables or the DBC.TdwmExceptionLog table with DBQL tables without needing ProcID as an additional join field.

    QueryRedriven

    Whether the query was redriven if redrive protection was enabled. Possible values are:

  • R = The request was redriven
  • N = The request was not redriven
  • E = The request was reparsed due to an internal Parser error.
  • M = The request was reparsed due to an internal Parser memory limit.
  • QueryText

    The first characters of the SQL query. Up to 200 characters are captured by default.

    If you use the LIMIT SQLTEXT option, you can specify that the first n characters be captured where n is 0-10000.

    RedriveKind

    Whether the query has redrive protection. Possible values are:

  • ' ' = Not participating in redrive
  • MN = memory-based protection, no fallback spool
  • ReqIOKB

    Total logical I/O usage in kilobytes.

    ReqPhysIO

    The number of physical I/Os.

    ReqPhysIOKB

    Total physical I/O usage in kilobytes.

    RequestMode

    Request mode from parcel request processing option:

  • Prep: Prepare mode (P)
  • Exec: Execute mode (E)
  • Both: Prepare and execute mode (B)
  • PrepS: Prepare, supporting parameterized SQL (S)
  • RequestNum

    Unique identifier of the query.

    Note: The request number for the commands that occur within a stored procedure CALL statement will all appear as the same number of the CALL statement itself. For more information on request numbers for stored procedures, see note for ExpandAcctString.

    ResponseTimeMet

    Indicates if the query met service level goals.

    SeqRespTime

    Sum of the response time of all steps as if they had been executed sequentially, in seconds.

    SessionID

    Unique session identifier.

    SessionTemporalQualifier

    When a DML or SELECT request refers to a temporal table but omits a temporal qualifier, the system uses the value of the session temporal qualifier. This is the session temporal qualifier in effect when the query is logged. For more information on the qualifier, see ANSI Temporal Table Support and Temporal Table Support.

    SessionWDID

    Workload ID class associated with the session. It determines the priority of the session when the TDWM workload classification is active.

    SpoolUsage

    If you are using algorithm 3 (the DBQLogTbl DataCollectAlg field= 3), the peak spool usage in bytes for any step of the query.

    For other algorithms, the maximum amount of spool used while processing the query.

    If SpoolUsage is 0, then there is no usage. If it is null, the data on usage was not valid.

    StartTime

    The timestamp when the query is submitted.

    Statements

    Number of statements in a request:

  • 1 = single-statement.
  • > 1 = multiple statements.
  • StatementGroup

    If there is a DDL statement in a request, StatementGroup reports which type:

  • DDL ALTER
  • DDL CREATE
  • DDL GRANT
  • If the statement has only one DML statement or multiple DML statements that are all of the same type, StatementGroup will indicate the type. For example if there are three DELETE statements in a request, StatementGroup will report:

    DML DELETE

    Similarly, for requests with individual or multiple INSERT, INSERT... SELECT, UPDATE or SELECT statements, StatementGroup will report:

  • DML INSERT
  • DML INSERT... SELECT
  • DML UPDATE
  • SELECT
  • In a multistatement request with different types of DML statements, you will see a list showing the number of statements of each type in the request. For example, a request with one insert and two update statements will appear as:

    DML      Del=0   Ins=1   InsSel=0    Upd=2    Sel=0

    StatementType

    The type of statement of the query.

    In a multistatement request, this is the last statement of the request. However, this may not accurately describe the request. For more statement information, see StatementGroup.

    SysConID

    The internal identifier of the System Condition (SysCon)/ System Health currently enforced by the Teradata dynamic workload management software.

     

     

    TacticalCPUException

    The number of nodes that had a CPU exception for the request.

    TacticalIOException

    The number of nodes that had an I/O exception for the request.

    TDWMAllAmpFlag

    Indicates whether one of the steps of the query is an all-amp step. This is used to classify a query into a workload definition.

    TDWMConfLevelUsed

    Note: No longer a TASM rule attribute. The letter 'N' will always be logged.

    TDWMEstLastRows

    The estimated last row count generated by the Optimizer used to classify a query into a workload definition.

    TDWMEstMaxRows

    The estimated maximum row count generated by the Optimizer used to classify a query into a workload definition.

    TDWMEstMemUsage

    The estimated memory used by the request (in megabytes). It represents the largest step in the request.

    TDWMEstTotalTime

    The total estimated time of a query by the Teradata dynamic workload management software (based on Optimizer estimates) and applied against rules read from the TDWM database.

    Note: This time is reported in milliseconds in DBC.DBQLogTbl, but it is reported in seconds in the view DBC.QryLogTDWM[V].

    TDWMRuleID

    Rule identifier of the query.

    ThrottleBypassed

    Whether an active request is active solely due to the ThrottleBypass ruleset attribute. This attribute overrides the throttle limits if the session owning the request has an object lock higher than the Access level. Possible values include:

  • ThrottleBypass ruleset attribute is set.
  • ThrottleBypass ruleset attribute is not set.
  • TotalIOCount

    Total logical I/O used by the query.

    Note: The I/O, CPU, and spool usage may not be accurate for aborted queries.

    TTGranularity

    Possible values include:

  • LogicalRow: Row is timestamped with the time the row is processed by the AMP.
  • Request: Row is timestamped with the time the request is submitted.
  • Transaction: Row is timestamped with the time when the first non-locking reference is made to a temporal table, or when the built-in function TEMPORAL_TIMESTAMP is first accessed during the transaction.
  • For more information, see Temporal Table Support.

    TxnMode

    Transaction mode:

  • ANSI: ANSI transaction mode
  • BTET: Teradata transaction mode
  • TnxUniq

    The unique value within the transaction number. Other transaction number components (userid, procid) are already in DBQLogTbl. Combined with TxnUniq, these fields represent the complete transaction id for the logged request.

    UnitySQL

    Time in seconds the stored procedure request was processed by Unity.

    UnityTime

    Time in seconds the stored procedure request was processed by Unity.

    UserID

    The unique ID of the user whose query is logged.

    UserName

    Name under which the session being logged was initiated.

    UtilityByteCount

    The number of bytes transferred by MultiLoad or FastLoad insertion, updates, or deletions.

    UtilityInfoAvailable

    Indicates whether an SQL request has utility information available in DBQLUtilityTbl. Possible values include:

  • Y for SQL requests from DSA and load/export utilities
  • N for SQL requests from other sources
  • UtilityRowCount

    The number of rows inserted, updated, and deleted by MultiLoad or FastLoad for target tables.

    VHLogicalIO

    VERYHOT logical I/O count per query.

    VHLogicalIOKB

    VERYHOT logical I/Os in KB per query.

    VHPhysicalIOKB

    VERYHOT physical I/Os in KB per query.

    VHPhysIO

    VERYHOT physical I/O count per query (VERYHOT cylinder and data block I/Os that were handled by physical disk I/O).

    WarningOnly

    Indicator if the error was only a warning. That is, warning mode.

    WDID

    The workload definition number assigned to the query.

    WDOverride

    Indicates if the workload definition assignment was overridden.

    Note: The ExtraFieldXX columns will be assigned to new column names in the next major release of Teradata Database, where XX is the number of the column (for example, DBC.DBQLStepTbl.ExtraField6).