DBQL rules – QUERY LOGGING

 

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”

or

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

Syntax:

 

Example:

 

To enable DBQL logging use “BEGIN QUERY LOGGING”.

Syntax:

 

where:

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.

 

Example:

 

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.

 

Example:

 

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

Syntax:

 

where:

flush-option is

 

Example:

 

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

Syntax:

 

Example:

 

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

Leave a Reply