Wednesday , April 24 2024



Following query will verify if any DBQL rule exists:

locking table DBC.DBQLRuleTbl for access


Note: View DBC.DBQLRules OR DBC.DBQLRulesV can also be used in above query.


To review existing DBQL rules you can either use “SHOW QUERY LOGGING”


Use DBC tables/view mentioned earlier (e.g DBC.DBQLRuleTbl).





To enable DBQL logging use “BEGIN QUERY LOGGING”.




logging-option is


0 – Use default for this release

1 – Use classic V2R6 algorithm 1 for step adjustments

2 – Use AMP algorithm 2 (mainly diagnostic)

3 – Use AMP algorithm 3 (aborted & parallel steps)

If MODE is specified, “m” (an integer) is required.

If MODE is not specified, mode is controlled by DBSControl general parameter for “DBQL CPU/IO Collection”.

An ERRTEQDBQLMODENOTALLOWED (9573) is returned on the SQL request if the user is not authorized to use.

limit-option is


Note: Be cautious as there may be performance impact on the teradata database when query logging is enabled for tactical / huge queries which are submitted per minute.




To replace query logging i.e replacing an existing rule, use “REPLACE QUERY LOGGING”.

The replace query logging syntax corresponds to the begin query logging syntax. If a replace query logging request specifies a rule that does not already exist, the request creates a new rule, similar to a begin query logging request else it replaces the current rule with rule you specify.




To flush one, several, or all database query log caches or Teradata workload management caches to disk, use “FLUSH QUERY LOGGING”.




flush-option is




To stop query logging, use “END QUERY LOGGING”.





Additional Information:

1. Grant the EXECUTE privilege on the special macro DBQLAccessMacro to enable/disable query logging.

2. Options for Flushing the DBQL Cache

Before Teradata Database writes DBQL data to Data Dictionary tables, it holds the data in cache until either:

• The cache is full.

• You end query logging.

• The number of seconds has elapsed that you define in the DBS Control utility field DBQLFlushRate.

Note: You can select a rate from 1 to 3,600 seconds. However, Teradata recommends a flush rate of at least 10 minutes (600 seconds), which is the default. Less than 10 minutes can impact performance.

• You flush the DBQL cache manually.

3. Teradata Release 16.20 onwards :

• The DBQL cache size is now adjustable from 64 KB to 16 MB, allowing to decide how much data the cache holds before writing to the DBQL Data Dictionary tables.

• The default DBQL table cache size increases from 64 KB to 2 MB.

• The default block size increases from 64 KB to 1 MB. This value is not tunable.

•  To adjust the DBQL cache size, change the value for the DBS Control parameter DBQLDefCacheSize and restart Teradata Database.

• FLUSH QUERY LOGGING request now supports the USECOUNT option.

4. DBC.DBQLRulesVX view does not exist by default and can be created using TSET with below steps:

Start TSET

goto to Tools->options

Check on use X views (DBC and QCD) and press OK


About Saumya Maurya

Leave a Reply

Your email address will not be published. Required fields are marked *