17.05 - Example: Searching Multiple Levels of the DBQL Rules Hierarchy - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

These examples show how SHOW QUERY LOGGING requests report query logging rules under conditions where it is not always possible for a rule to be matched at the first search level in the rules hierarchy. See Example: Report a DBQL Rule That Logs All Users for All Accounts Using Default Logging through Example: Report a DBQL Rule That Logs All Queries for the MultiLoad Utility.

Assume that the following query logging rules, as indicated by their SQL text, have been created in DBC.DBQLRulesTbl and as seen using the DBC.DBQLRules view. Note that except for a few differences in the specified logging options (see Rules 2, 3, and 5), these logging rules are identical to those for Example: SHOW QUERY LOGGING for Simple Conditions.

  1. BEGIN QUERY LOGGING ON ALL;

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

    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 
  2. BEGIN QUERY LOGGING WITH OBJECTS ON ALL ACCOUNT = 'finance';

    This request creates a logging rule for all users logged on under account name finance and logs objects.

    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 
  3. BEGIN QUERY LOGGING WITH STEPINFO ON user1 ACCOUNT = 'marketing';

    This request creates a logging rule for user1 when that user is logged on under account name marketing and logs step information.

    The row for Rule 3 in DBC.DBQLRules 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 
  4. BEGIN QUERY LOGGING ON user1 ACCOUNT = 'hr';

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

    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 
  5. BEGIN QUERY LOGGING WITH SQL ON user1;

    This request creates a logging rule for user1 when that user is logged on under any account name and logs SQL.

    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 
  6. BEGIN QUERY LOGGING WITH NONE ON APPLNAME = 'multload';

    This request creates a logging rule for the MultiLoad application with no logging.

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

    Rule UserName  "ALL"
    Rule UserId     00000000
    Account        (Rule for any Account)
    ApplicationName   MULTLOAD
    WITH NONE         (No DBQL Logging)

The hierarchy for these rules is the same as that for the first example set, as follows.

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