SHOW QUERY LOGGING Examples | Teradata Vantage - Example: SHOW QUERY LOGGING for Simple Conditions - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

These examples show how SHOW QUERY LOGGING requests report query logging rules under simple conditions where it is possible for rule matches to be made at the first search level in the rule hierarchy. See Example: Report a DBQL Rule That Logs All Users for Any Account String through Example: Report the DBQL Logging Rule for the MultiLoad Utility.

Assume that the following query logging rules, as indicated by the SQL text and the text of the rule in the DBC.DBQLRules view, have been created.

BEGIN QUERY LOGGING ON ALL;

This statement creates a logging rule for all users and all accounts using default options.

The row for rule 1 in DBC.DBQLRules is as follows.

    Rule UserName   "ALL" (From an ALL rule)
      Rule UserId   00000000
          Account   (Rule for any Account)

       DBQL RULE:
          Explain   F
           Object   F
              SQL   F
             Step   F
          XMLPlan   F
       StatsUsage   F
         UseCount   F
            Param   F
          Verbose   F
     StatsDetails   F
      UtilityInfo   F
          Summary   F
        Threshold   F
  Text Size Limit   200

BEGIN QUERY LOGGING ON ALL ACCOUNT = 'finance';

This request creates a logging rule for all users logged on under account name finance using default logging options.

The row for rule 2 in DBC.DBQLRules is as follows.

    Rule UserName   "ALL" (From an ALL rule)
      Rule UserId   00000000
          Account   FINANCE

       DBQL RULE:
          Explain   F
           Object   F
              SQL   F
             Step   F
          XMLPlan   F
       StatsUsage   F
         UseCount   F
            Param   F
          Verbose   F
     StatsDetails   F
      UtilityInfo   F
          Summary   F
        Threshold   F
  Text Size Limit   200

BEGIN QUERY LOGGING ON user1 ACCOUNT = 'marketing';

This request creates a logging rule for user1 when logged on under account name marketing using default options.

The row for rule 3 as seen using the DBC.DBQLRules view is as follows.

Rule UserName   user1
      Rule UserId   00001244
          Account   MARKETING

       DBQL RULE:
          Explain   F
           Object   F
              SQL   F
             Step   F
          XMLPlan   F
       StatsUsage   F
         UseCount   F
            Param   F
          Verbose   F
     StatsDetails   F
      UtilityInfo   F
          Summary   F
        Threshold   F
  Text Size Limit   200

BEGIN QUERY LOGGING ON user1 ACCOUNT = 'hr';

This request creates a logging rule for user1 when logged on under account name hr using default logging options.

The row for rule 4 in DBC.DBQLRules is as follows.

Rule UserName   user1
      Rule UserId   00001244
          Account   HR

       DBQL RULE:
          Explain   F
           Object   F
              SQL   F
             Step   F
          XMLPlan   F
       StatsUsage   F
         UseCount   F
            Param   F
          Verbose   F
     StatsDetails   F
      UtilityInfo   F
          Summary   F
        Threshold   F
  Text Size Limit   200

BEGIN QUERY LOGGING ON user1;

This request creates a logging rule for user1 when logged on under any account name using default options.

The row for rule 5 in DBC.DBQLRules is as follows.

Rule UserName   user1
      Rule UserId   00001244
          Account   (Rule for any Account)

       DBQL RULE:
          Explain   F
           Object   F
              SQL   F
             Step   F
          XMLPlan   F
       StatsUsage   F
         UseCount   F
            Param   F
          Verbose   F
     StatsDetails   F
      UtilityInfo   F
          Summary   F
        Threshold   F
  Text Size Limit   200

BEGIN QUERY LOGGING ON APPLNAME = 'multload';

This request creates a logging rule for the MultiLoad application using default options.

The row for rule 6 in DBC.DBQLRules is as follows.

Rule UserName   "ALL" (From an ALL rule)
      Rule UserId   00000000
          Account   (Rule for any Account)
  ApplicationName   MULTLOAD

       DBQL RULE:
          Explain   F
           Object   F
              SQL   F
             Step   F
          XMLPlan   F
       StatsUsage   F
         UseCount   F
            Param   F
          Verbose   F
     StatsDetails   F
      UtilityInfo   F
          Summary   F
        Threshold   F
  Text Size Limit   200

The hierarchy for these rules is as follows.

Hierarchy Level Rule Number
1 6
2 3
3 4
4 5
5 2
6 1