Following query will verify if any DBQL rule exists:
locking table DBC.DBQLRuleTbl for access
|
1 |
select * from DBC.DBQLRuleTbl; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
SHOW QUERY LOGGING { ALL [ ACCOUNT = ('account id' [ ... ,'account id' ] ) ] } ON { user-name [ ACCOUNT = ('account id' [ ... ,'account id' ] ) ] } { user-name [ ... ,user-name ] } { database-name [ ... ,database-name ] } { APPLNAME = ('application-name' [ ... ,'application-name' ] ) } ; |
Example:
|
1 2 3 4 5 6 7 8 9 |
show query logging on all; show query logging on username; show query logging on all account = 'accountname'; show query logging on username account = 'accountname'; show query logging on applname = 'multload'; |
To enable DBQL logging use “BEGIN QUERY LOGGING”.
Syntax:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
BEGIN QUERY LOGGING [ WITH logging-option [ ... ,logging-option ] ] [ MODE = m ] [ LIMIT limit-option [AND limit-option ] ] { ALL [ ACCOUNT = ('account id' [ ... ,'account id' ] ) ] } ON { user-name [ ACCOUNT = ('account id' [ ... ,'account id' ] ) ] } { user-name [ ... ,user-name ] } { database-name [ ... ,database-name ] } { APPLNAME = ('application-name' [ ... ,'application-name' ] ) } ; |
where:
logging-option is
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
OBJECTS database, table, column and index information in DBQLObjTbl. SQL log the full text of all SQL statements in DBQLSqlTbl. STEPINFO log AMP step-level information in DBQLStepTbl. EXPLAIN generate and log the 'Explain' for the query in DBQLExplainT XMLPLAN log the query plan in XML format in DBQLXMLTbl. LOCK=n log any lock contention that exceeds centiseconds in XML format in DBQLXMLLOCKTbl. Minimum acceptable value for n is 5. STATSUSAGE log query optimizer statistics usage and recommendations PARAMINFO log parameter information and data parcels into DBQLParamTbl USECOUNT log object use counts. UTILITYINFO log utility information in DBQLUtilityTbl. VERBOSE log verbose explain and related information in XML format,is a sub-option to XMLPLAN. DETAILED log summary stats details from the interval zero of the hist for the existing stats on all objects referenced in the query plan.It is a sub-option to STATSUSAGE option. NO COLUMNS Sub-option to the OBJECTS logging rule to control the logging of column information. ALL log object, step information, explain, and SQL. NONE do not enable DBQL for specified application(s), user(s) or account(s). Logging option ::= (ALL | EXPLAIN | [NO COLUMNS] OBJECTS | SQL | STEPINFO PARAMINFO | [VERBOSE] XMLPLAN | [DETAILED] STATSUSAGE) MODE = m Use the CPU/IO Collection algorithm specified by "m" |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
SQLTEXT[=n] maximum number of SQL text characters in DBQLogTbl. Without n: 10,000 characters, n=0 no SQL. SUMMARY=n1,n2,n3 [units] count the number of queries for a session that fall into each of four intervals for the DBQLSummaryTbl. THRESHOLD[=n] [units] queries that run in n 'units' or less are counted in DBQLSummaryTbl; others are logged in DBQLogTbl. Without n: 5 'units' units may be: CPUTIME Summary or Threshold is based on CPU time where n,n1,n2,n3 is specified in hundredths of a second. CPUTIMENORM Summary or Threshold is based on normalized CPU time where n,n1,n2,n3 is specified in hundredths of a second. ELAPSEDTIME Summary or Threshold is based on elapsed time where n,n1,n2,n3 is specified in hundredths of a second. IOCOUNT Summary or Threshold is based on count of total IO's where n,n1,n2,n3 is specified in number of IO's. Without units: ELAPSEDSEC: Summary or Threshold is based on elapsed time where n,n1,n2,n3 is specified as seconds. LIMIT - SUMMARY cannot be specified with THRESHOLD. - Options cannot be specified with SUMMARY. - If options are specified with THRESHOLD, the allowed options are SQL, STEPINFO and OBJECTS. USECOUNT - This option is exclusive to databases. No other logging or limit options may be used on databases or in conjunction with the USECOUNT option on databases. ON - Account string information can be entered for ALL or for a single user name. With a list of users, no account string can be specified. With an application name or list, no user or account can be specified. |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
begin query logging on all; begin query logging with stepinfo, objects limit sqltext=1000 on username; begin query logging with stepinfo, objects, explain, xmlplan, sql on all account = 'accountname' begin query logging with verbose xmlplan on username; begin query logging with usecount on databasename, databasename2, databasename3; begin query logging with utilityinfo on username ; |
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:
|
1 2 3 4 5 6 7 8 9 |
replace query logging on all; replace query logging with stepinfo, objects limit sqltext=1000 on username; replace query logging with stepinfo, objects, explain, xmlplan, sql on all account = 'accountname'; replace query logging with verbose xmlplan on username; replace query logging with usecount on databasename, databasename2, databasename3; |
To flush one, several, or all database query log caches or Teradata workload management caches to disk, use “FLUSH QUERY LOGGING”.
Syntax:
|
1 |
FLUSH QUERY LOGGING WITH flush-option [ ... ,flush-option ]; |
where:
flush-option is
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
ALL perform option ALLDBQL and ALLTDWM. ALLDBQL this is equivalent to DEFAULT,SQL,STEPINFO,OBJECTS, EXPLAIN,XMLPLAN,SUMMARY,LOCK,PARAMINFO. DEFAULT flushes DBC.dbqlogtbl table cache. SQL flushes DBC.dbqlsqltbl table cache. PARAMINFO flushes DBC.dbqlParamtbl table cache. STEPINFO flushes DBC.dbqlsteptbl table cache. OBJECTS flushes DBC.dbqlobjtbl table cache. EXPLAIN flushes DBC.dbqlexplaintbl table cache. XMLPLAN flushes DBC.dbqlxmltbl table cache. LOCK flushes DBC.dbqlxmllocktbl table cache. SUMMARY flushes DBC.dbqlsummarytbl table cache. ALLTDWM this is equivalent to DEFAULT,SQL,TDWMHISTORY,TDWMEXCEPTION,TDWMEVENT TDWMHISTORY flushes DBC.tdwmeventhistory table cache. TDWMEXCEPTION flushes DBC.tdwmexceptionlog table cache. TDWMEVENT flushes DBC.tdwmeventlog table cache. |
Example:
|
1 2 3 |
flush query logging with all; flush query logging with objects; |
To stop query logging, use “END QUERY LOGGING”.
Syntax:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
END QUERY LOGGING { ALL [ ACCOUNT = ('account id' [ ... ,'account id' ] ) ] } ON { user-name [ ACCOUNT = ('account id' [ ... ,'account id' ] ) ] } { user-name [ ... ,user-name] } { database-name [ ... ,database-name ] } { APPLNAME = ('application-name' [ ... ,'application-name' ] ) } { ALL RULES } ; |
Example:
|
1 2 3 4 5 6 7 8 9 |
end query logging on all; end query logging on all rules; end query logging on username account = ('accountname'); end query logging with utilityinfo on username; end query logging with verbose xmlplan on username; |
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
