Scenarios of Threshold and Summary Options - Teradata Database

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

Scenarios of Threshold and Summary Options

 

IF you…

THEN …

create rules for a user and specify LIMIT SUMMARY=5,10,15 and during the next session, and every query takes longer than 5 seconds but less than 10 seconds to complete

all queries fall into the second bucket (5 to 10 seconds), so the second group is the only query count logged for the session in DBQLSummaryTbl.

create rules for a user and specify LIMIT SUMMARY = 5,10,15 and during the next session, every query completes in less than 5 seconds

all queries fall into the first bucket (up to 5 seconds), so the first group is the only query count logged for the session in DBQLSummaryTbl.

create rules and specify LIMIT SUMMARY=1,15,10

the statement is accepted (no checking is performed on SUMMARY input values) but the results are unpredictable.

create rules for UserA and specify SQL, OBJECTS LIMIT THRESHOLD (without a time value), and UserA then processes four queries, where:

  • One query takes more than 5 seconds to complete
  • Three queries complete in less than 5 seconds
    		•

    the statement is accepted and the default value of 5 seconds is assigned. For the next session of UserA:

  • The longer query is logged in DBQLSqlTbl, DBQLObjTbl, and DBQLogTbl, with values in all valid fields of the rows.
  • For each of the three shorter queries:
  • No entries are made in DBQLSqlTbl, DBQLObjTbl, and DBQLogTbl.
  • DBQLSummaryTbl will have a row with these values:
    • COUNT = 3
    SECONDS = 10
    LOWHIST = 5
    HIGHHIST = 0

    create a rule and specify LIMIT THRESHOLD=100000

    An error is returned; THRESHOLD must be less than 32K.

    Example of OBJECT Data for One Query

    The following example illustrates the rows in DBQLObjTbl resulting from a query logged with the WITH OBJECT option:

    ObjDBName     ObjTblName    ObjColName    ObjID      ObjNum    ObjType   FreqofUse
    -----------   -----------   -----------   --------   -------   -------   ---------
    D_PERSONNEL   ?             ?             00001604   0         DB        1
    D_PERSONNEL   DEPARTMENT    ?             00009005   0         Tab       1
    D_PERSONNEL   DEPARTMENT    DeptNo        00009005   1,025     Col       2
    D_PERSONNEL   DEPARTMENT    DeptName      00009005   1,026     Col       1
    D_PERSONNEL   DEPARTMENT    EmpCount      00009005   1,027     Col       1
    D_PERSONNEL   DEPARTMENT    Loc           00009005   1,028     Col       1

    Example of STEP Data for One Query

    The following example illustrates the rows in DBQLStepTbl resulting from a query logged with the WITH STEPINFO option:

    StepLev1   StepLev2  StepName   StepStartTime                 StepStopTime                 RowCount
    --------   --------  --------   --------------------------   --------------------------   --------
     1         0         MLK        2004-07-08 20:37:22.770000   2004-07-08 20:37:23.770000    1
     2         0         MLK        2004-07-08 20:37:23.770000   2004-07-08 20:37:23.780000    0
     3         0         MLK        2004-07-08 20:37:23.780000   2004-07-08 20:37:23.790000    1
     4         1         SAT        2004-07-08 20:37:23.790000   2004-07-08 20:37:23.790000    0
     4         2         SAT        2004-07-08 20:37:23.790000   2004-07-08 20:37:23.790000    0
     4         3         INS        2004-07-08 20:37:23.800000   2004-07-08 20:37:23.800000    1
     4         4         INS        2004-07-08 20:37:23.800000   2004-07-08 20:37:23.810000    1
     4         5         INS        2004-07-08 20:37:23.820000   2004-07-08 20:37:24.830000    1
     4         6          INS         2004-07-08 20:37:23.830000   2004-07-08 20:37:24.840000    1
     4         7          CTRts       2004-07-08 20:37:24.110000   2004-07-08 20:37:25.060000    1
     5         0          Ctb         2004-07-08 20:37:25.080000   2004-07-08 20:37:25.100000    1
     6         0          Edt         2004-07-08 20:37:25.120000   2004-07-08 20:37:25.130000    1