Diagnostic and Extended Logging | VantageCloud Lake - Diagnostic and Extended Logging Capabilities - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2024-12-13
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
jbg1472252759029
lifecycle
latest
Product Category
Teradata Vantage™

The enhanced Extended DBQL statistics allows for more granular data collection to isolate specific express step executions at the AMP level and their associated user SQL.

Syntax Updates

  • The BEGIN/REPLACE QUERY LOGGING statements include the DETAILDIAG option; an optional sub-option STEPETTHRESHOLD can be specified when the user supplies a valid DETAILDIAG option.

    Any effective SUMMARY or THRESHOLD option can be overridden to diagnose SLA failures. This capability will allow you to collect detailed logs in DBC.DBQLogTbl and DBC.DBQLStepTbl.

  • FLUSH QUERY LOGGING includes AMPDATA as a new flush option. Note that when ALL or ALLDBQL options are used, it includes AMPDATA option as well to flush.
  • SHOW QUERY LOGGING output shows the effective DBQL options for a given user. .

Use Case

Let's say your tactical workload consistently fails to meet the SLA for user1 and user2. Extended DBQL statistics can accurately determine which specific express request corresponds to the user requests that may be causing these failures.

The elapsed times of step execution can be readily determined at the AMP level and be as precise as microseconds due to the new data being collected. Additionally, you can obtain the associated resource usage to identify potential bottlenecks such as CPU or IO.

  1. Enable extended DBQL logging or AMP diagnostics based on what parameters to check. Note that enabling Extended DBQL logging also collects detailed AMP level granular diagnostics.
    1. In the following example, the DETAILDIAG options ExtDBQLData (256) and SumThreshOverride (1024) are enabled on user1 and user2. See REPLACE QUERY LOGGING Syntax Elements for more information on these options.
      REPLACE QUERY LOGGING with STEPINFO DETAILDIAG=1280 on user1, user2; 
    2. To enable both options, DETAILDIAG is set 1280 (256 + 1024). The SHOW QUERY LOGGING output in the following example verifies that ExtDBQLData and SumThreshOverride are now both set to T (for TRUE).
      SHOW QUERY LOGGING on user1,user2;

      Result:

          Rule UserName   user1 (From a System and a User rule)
            Rule UserId   00007C05
            Rule ZoneId   00000000
                Account   (Rule for any Account)
             DBQL RULE: Active  System  User
                Explain   F       F      F
                 Object   F       F      F
                    SQL   T       T      F
                   Step   T       F      T
                XMLPlan   F       F      F
             StatsUsage   F       F      F
               UseCount   F       F      F
                  Param   F       F      F
           FeatureUsage   T       T      T
                Verbose   F       F      F
           StatsDetails   F       F      F
            UtilityInfo   T       T      F
              NoColumns   F       F      F
                Summary   F       F      F
              Threshold   F       F      F
        Text Size Limit   200     200    200
       DETAILDIAG RULE:  User
            AMPDataDiag   F
            ExtDBQLData   T
      SumThreshOverride   T
      
      
          Rule UserName   user2 (From a System and a User rule)
            Rule UserId   00007D05
            Rule ZoneId   00000000
                Account   (Rule for any Account)
             DBQL RULE: Active  System  User
                Explain   F       F      F
                 Object   F       F      F
                    SQL   T       T      F
                   Step   T       F      T
                XMLPlan   F       F      F
             StatsUsage   F       F      F
               UseCount   F       F      F
                  Param   F       F      F
           FeatureUsage   T       T      T
                Verbose   F       F      F
           StatsDetails   F       F      F
            UtilityInfo   T       T      F
              NoColumns   F       F      F
                Summary   F       F      F
              Threshold   F       F      F
        Text Size Limit   200     200    200
       DETAILDIAG RULE:  User
            AMPDataDiag   F
            ExtDBQLData   T
      SumThreshOverride   T

      Note that enabling Extended DBQL logging (ExtDBQLData 256) also collects granular AMP diagnostics (AMPDataDiag 128). Thus, it is unnecessary to enable AMPDataDiag (128) when ExtDBQLData (256) is enabled. However, as can be seen in the SHOW QUERY LOGGING statement, the flag for AMPDataDiag (128) will remain F (for FALSE) unless explicitly enabled in the DETAILDIAG rule.

  2. The REPLACE QUERY LOGGING SQL enabled extended DBQL logging with SUMMARY and THRESHOLD overridden. That means if the request did not meet SLA even though STEPINFO option not enabled, there will be rows logged in DBC.DBQLStepTbl for corresponding requests irrespective of SUMMARY and or THRESHOLD rules being active for the user due to any other applicable rule hierarchy.
  3. Use the new data from detailed AMP diagnostics (DBC.QryLogAMPDataV) to understand the express request elapsed times:
    SELECT StartTime, FirstStepTime, b.stepstarttime, b.stepstoptime, ((b.stepstoptime - b.StepStartTime) hour(4) to second) (Named ExpElapsedTime), b.StepClass, b.StepKind from dbqlogtbl a, qrylogampdatav b where a.queryid = b.queryid and b.stepclass='EXP' and b.stepstarttime >= a.starttime and b.stepstoptime <= a.firststeptime and ResponseTimeMet is NULL sample 5;

    Result:

                     StartTime               FirstStepTime               StepStartTime                StepStopTime      ExpElapsedTime  StepClass        StepKind
    --------------------------  --------------------------  --------------------------  --------------------------  ------------------  ---------------  ---------------
    2024-03-08 13:55:29.297572  2024-03-08 13:55:29.298092  2024-03-08 13:55:29.297844  2024-03-08 13:55:29.297995      0:00:00.000151  EXP              RERX3
    2024-03-08 13:53:03.744152  2024-03-08 13:53:03.761678  2024-03-08 13:53:03.745033  2024-03-08 13:53:03.745199      0:00:00.000166  EXP              RAR
    2024-03-08 13:52:51.403219  2024-03-08 13:52:51.418419  2024-03-08 13:52:51.416195  2024-03-08 13:52:51.416300      0:00:00.000105  EXP              FTH
    2024-03-08 13:53:05.889118  2024-03-08 13:53:05.903333  2024-03-08 13:53:05.902449  2024-03-08 13:53:05.902563      0:00:00.000114  EXP              FTH
    2024-03-08 13:53:07.143486  2024-03-08 13:53:07.158304  2024-03-08 13:53:07.157241  2024-03-08 13:53:07.157363      0:00:00.000122  EXP              FTH
  4. The above SQL shows which requests did not meet the SLA and their express request elapsed times before the first AMP execution step started. The steps shown are an example; a single request can include multiple express requests that might be contributing to the SLA failure.
  5. Disable the diagnostic rule after logging is complete:
    end query logging on user1, user2;
The detailed diagnostic and extended DBQL data collected into DBC.DBQLAMPDataTbl are currently not managed by Metric Streaming Service (MSS). You must clean up the data from DBC.DBQLAMPDataTbl on the primary and compute clusters. Default storage for these tables are BFS and must be managed manually. If MSS can manage as part of the dictionary table management process, no action is needed.