<\/p>\n
Following query will verify if any DBQL rule exists:<\/p>\n
locking table DBC.DBQLRuleTbl for access<\/p>\n
select * from DBC.DBQLRuleTbl;<\/pre>\n<\/p>\n
Note:<\/strong> View DBC.DBQLRules OR DBC.DBQLRulesV can also be used in above query.<\/p>\n
<\/p>\n
To review existing DBQL rules you can either use “SHOW QUERY LOGGING”<\/p>\n
or<\/p>\n
Use DBC tables\/view mentioned earlier (e.g DBC.DBQLRuleTbl).<\/p>\n
Syntax:<\/strong><\/p>\n
SHOW QUERY LOGGING\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ ALL [ ACCOUNT = ('account id' [ ... ,'account id' ] ) ] }\r\n\r\nON\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ user-name [ ACCOUNT = ('account id' [ ... ,'account id' ] ) ] }\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ user-name [ ... ,user-name ] }\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ database-name [ ... ,database-name ] }\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ APPLNAME = ('application-name' [ ... ,'application-name' ] ) } ;<\/pre>\n<\/p>\n
Example:<\/strong><\/p>\n
show query logging on all;\r\n\r\nshow query logging on username;\r\n\r\nshow query logging on all account = 'accountname';\r\n\r\nshow query logging on username account = 'accountname';\r\n\r\nshow query logging on applname = 'multload';<\/pre>\n<\/p>\n
To enable DBQL logging use “BEGIN QUERY LOGGING”.<\/p>\n
Syntax:<\/strong><\/p>\n
BEGIN QUERY LOGGING\r\n\r\n\u00a0 \u00a0 \u00a0[ WITH logging-option [ ... ,logging-option ] ]\r\n\r\n\u00a0 \u00a0 \u00a0[ MODE = m ]\r\n\r\n\u00a0 \u00a0 \u00a0[ LIMIT limit-option [AND limit-option ] ]\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ ALL [ ACCOUNT = ('account id' [ ... ,'account id' ] ) ] }\r\n\r\nON\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ user-name [ ACCOUNT = ('account id' [ ... ,'account id' ] ) ] }\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ user-name [ ... ,user-name ] }\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ database-name [ ... ,database-name ] }\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ APPLNAME = ('application-name' [ ... ,'application-name' ] ) } ;<\/pre>\n<\/p>\n
where:<\/p>\n
logging-option is<\/p>\n
OBJECTS database, table, column and index information in DBQLObjTbl.\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0SQL log the full text of all SQL statements in DBQLSqlTbl.\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0STEPINFO log AMP step-level information in DBQLStepTbl.\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0EXPLAIN generate and log the 'Explain' for the query in DBQLExplainT\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0XMLPLAN log the query plan in XML format in DBQLXMLTbl.\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0LOCK=n log any lock contention that exceeds centiseconds\u00a0in XML format in DBQLXMLLOCKTbl. Minimum acceptable value for n is 5.\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0STATSUSAGE log query optimizer statistics usage and recommendations\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0PARAMINFO log parameter information and data parcels into DBQLParamTbl\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0USECOUNT log object use counts.\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0UTILITYINFO log utility information in DBQLUtilityTbl.\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0VERBOSE log verbose explain and related information in XML format,is a sub-option to XMLPLAN.\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0DETAILED log summary stats details from the interval zero of the hist\u00a0for the existing stats on all objects referenced in the query\u00a0plan.It is a sub-option to STATSUSAGE option.\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0NO COLUMNS Sub-option to the OBJECTS logging rule to control the logging\u00a0of column information.\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0ALL log object, step information, explain, and SQL.\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0NONE do not enable DBQL for specified application(s), user(s) or\u00a0account(s).\r\n\r\nLogging option ::= (ALL | EXPLAIN | [NO COLUMNS] OBJECTS | SQL | STEPINFO\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 PARAMINFO | [VERBOSE] XMLPLAN | [DETAILED] STATSUSAGE)\r\n\r\nMODE = m Use the CPU\/IO Collection algorithm specified by \"m\"<\/pre>\n<\/p>\n
0 – Use default for this release<\/p>\n
1 – Use classic V2R6 algorithm 1 for step adjustments<\/p>\n
2 – Use AMP algorithm 2 (mainly diagnostic)<\/p>\n
3 – Use AMP algorithm 3 (aborted & parallel steps)<\/p>\n
If MODE is specified, “m” (an integer) is required.<\/p>\n
If MODE is not\u00a0specified, mode is controlled by DBSControl general parameter for\u00a0“DBQL CPU\/IO Collection”.<\/p>\n
An ERRTEQDBQLMODENOTALLOWED (9573) is\u00a0returned on the SQL request if the user is not authorized to use.<\/p>\n
limit-option is<\/p>\n
\u00a0SQLTEXT[=n]\u00a0 \u00a0 \u00a0 \u00a0 \u00a0maximum number of SQL text characters in DBQLogTbl.\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Without n: 10,000 characters, n=0 no SQL.\r\n\r\n\u00a0 \u00a0 \u00a0SUMMARY=n1,n2,n3 [units]\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0count the number of queries for a session that fall\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0into each of four intervals for the DBQLSummaryTbl.\r\n\r\n\u00a0 \u00a0 \u00a0THRESHOLD[=n] [units]\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0queries that run in n 'units' or less are counted in\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0DBQLSummaryTbl; others are logged in DBQLogTbl.\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Without n: 5 'units'\r\n\r\n\u00a0 \u00a0 \u00a0\u00a0 \u00a0 \u00a0 \u00a0units may be:\r\n\r\n\u00a0 \u00a0 \u00a0CPUTIME\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Summary or Threshold is based on CPU time\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0where n,n1,n2,n3 is specified in hundredths of\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0a second.\r\n\r\n\u00a0 \u00a0 \u00a0CPUTIMENORM\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Summary or Threshold is based on normalized CPU time\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0where n,n1,n2,n3 is specified in hundredths of\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0a second.\r\n\r\n\u00a0 \u00a0 \u00a0ELAPSEDTIME\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Summary or Threshold is based on elapsed time\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0where n,n1,n2,n3 is specified in hundredths of\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0a second.\r\n\r\n\u00a0 \u00a0 \u00a0IOCOUNT\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Summary or Threshold is based on count of total IO's\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0where n,n1,n2,n3 is specified in number of IO's.\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0Without units:\r\n\r\n\u00a0 \u00a0 \u00a0 ELAPSEDSEC:\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Summary or Threshold is based on elapsed time\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0where n,n1,n2,n3 is specified as seconds.\r\n\r\nLIMIT - SUMMARY cannot be specified with THRESHOLD.\r\n\r\n\u00a0 \u00a0 \u00a0 - Options cannot be specified with SUMMARY.\r\n\r\n\u00a0 \u00a0 \u00a0 - If options are specified with THRESHOLD, the allowed options are\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 SQL, STEPINFO and OBJECTS.\r\n\r\nUSECOUNT - This option is exclusive to databases. No other logging or limit\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0options may be used on databases or in conjunction with the\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0USECOUNT option on databases.\r\n\r\nON - Account string information can be entered for ALL or for a single\r\n\r\n\u00a0 \u00a0 \u00a0user name. With a list of users, no account string can be specified.\r\n\r\n\u00a0 \u00a0 \u00a0With an application name or list, no user or account can be specified.<\/pre>\n<\/p>\n
Note:<\/strong> 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.<\/p>\n
<\/p>\n
Example:<\/strong><\/p>\n
begin query logging on all;\r\n\r\nbegin query logging with stepinfo, objects limit sqltext=1000 on username;\r\n\r\nbegin query logging with stepinfo, objects, explain, xmlplan, sql on all account = 'accountname'\r\n\r\nbegin query logging with verbose xmlplan on username;\r\n\r\nbegin query logging with usecount on databasename, databasename2, databasename3;\r\n\r\nbegin query logging with utilityinfo on username ;<\/pre>\n<\/p>\n
To replace query logging i.e replacing an existing rule, use “REPLACE QUERY LOGGING”.<\/p>\n
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.<\/p>\n
<\/p>\n
Example:<\/strong><\/p>\n
replace query logging on all;\r\n\r\nreplace query logging with stepinfo, objects limit sqltext=1000 on username;\r\n\r\nreplace query logging with stepinfo, objects, explain, xmlplan, sql on all account = 'accountname';\r\n\r\nreplace query logging with verbose xmlplan on username;\r\n\r\nreplace query logging with usecount on databasename, databasename2, databasename3;<\/pre>\n<\/p>\n
To flush one, several, or all database query log caches or Teradata workload management caches to disk, use “FLUSH QUERY LOGGING”.<\/p>\n
Syntax:<\/strong><\/p>\n
FLUSH QUERY LOGGING WITH flush-option [ ... ,flush-option ];<\/pre>\n<\/p>\n
where:<\/p>\n
flush-option is<\/p>\n
ALL perform option ALLDBQL and ALLTDWM.\r\n\r\n\u00a0 \u00a0 \u00a0 ALLDBQL this is equivalent to DEFAULT,SQL,STEPINFO,OBJECTS,\r\n\r\n\u00a0 \u00a0 \u00a0 EXPLAIN,XMLPLAN,SUMMARY,LOCK,PARAMINFO.\r\n\r\n\u00a0 \u00a0 \u00a0 DEFAULT flushes DBC.dbqlogtbl table cache.\r\n\r\n\u00a0 \u00a0 \u00a0 SQL flushes DBC.dbqlsqltbl table cache.\r\n\r\n\u00a0 \u00a0 \u00a0 PARAMINFO flushes DBC.dbqlParamtbl table cache.\r\n\r\n\u00a0 \u00a0 \u00a0 STEPINFO flushes DBC.dbqlsteptbl table cache.\r\n\r\n\u00a0 \u00a0 \u00a0 OBJECTS flushes DBC.dbqlobjtbl table cache.\r\n\r\n\u00a0 \u00a0 \u00a0 EXPLAIN flushes DBC.dbqlexplaintbl table cache.\r\n\r\n\u00a0 \u00a0 \u00a0 XMLPLAN flushes DBC.dbqlxmltbl table cache.\r\n\r\n\u00a0 \u00a0 \u00a0 LOCK flushes DBC.dbqlxmllocktbl table cache.\r\n\r\n\u00a0 \u00a0 \u00a0 SUMMARY flushes DBC.dbqlsummarytbl table cache.\r\n\r\n\u00a0 \u00a0 \u00a0 ALLTDWM this is equivalent to DEFAULT,SQL,TDWMHISTORY,TDWMEXCEPTION,TDWMEVENT\r\n\r\n\u00a0 \u00a0 \u00a0 TDWMHISTORY flushes DBC.tdwmeventhistory table cache.\r\n\r\n\u00a0 \u00a0 \u00a0 TDWMEXCEPTION flushes DBC.tdwmexceptionlog table cache.\r\n\r\n\u00a0 \u00a0 \u00a0 TDWMEVENT flushes DBC.tdwmeventlog table cache.<\/pre>\n<\/p>\n
Example:<\/strong><\/p>\n
flush query logging with all;\r\n\r\nflush query logging with objects;<\/pre>\n<\/p>\n
To stop query logging, use “END QUERY LOGGING”.<\/p>\n
Syntax:<\/strong><\/p>\n
END QUERY LOGGING\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ ALL [ ACCOUNT = ('account id' [ ... ,'account id' ] ) ] }\r\n\r\nON\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ user-name [ ACCOUNT = ('account id' [ ... ,'account id' ] ) ] }\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ user-name [ ... ,user-name] }\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ database-name [ ... ,database-name ] }\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ APPLNAME = ('application-name' [ ... ,'application-name' ] ) }\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{ ALL RULES } ;<\/pre>\n<\/p>\n
Example:<\/strong><\/p>\n
end query logging on all;\r\n\r\nend query logging on all rules;\r\n\r\nend query logging on username account = ('accountname');\r\n\r\nend query logging with utilityinfo on username;\r\n\r\nend query logging with verbose xmlplan on username;<\/pre>\n<\/p>\n
Additional Information:<\/strong><\/p>\n
1. Grant the EXECUTE privilege on the special macro DBQLAccessMacro to enable\/disable query logging.<\/p>\n
2. Options for Flushing the DBQL Cache<\/p>\n
Before Teradata Database writes DBQL data to Data Dictionary tables, it holds the data in cache until either:<\/p>\n
\u2022 The cache is full.<\/p>\n
\u2022 You end query logging.<\/p>\n
\u2022 The number of seconds has elapsed that you define in the DBS Control utility field DBQLFlushRate.<\/p>\n
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.<\/p>\n
\u2022 You flush the DBQL cache manually.<\/p>\n
3. Teradata Release 16.20 onwards :<\/p>\n
\u2022 The DBQL cache size is now adjustable from 64 KB to 16 MB, allowing to decide how much data the cache holds before\u00a0writing to the DBQL Data Dictionary tables.<\/p>\n
\u2022 The default DBQL table cache size increases from 64 KB to 2 MB.<\/p>\n
\u2022 The default block size increases from 64 KB to 1 MB. This value is not tunable.<\/p>\n
\u2022\u00a0 To adjust the DBQL cache size, change the value for the DBS Control parameter DBQLDefCacheSize\u00a0and restart Teradata Database.<\/p>\n
\u2022\u00a0FLUSH QUERY LOGGING request now supports the USECOUNT option.<\/p>\n
4.\u00a0DBC.DBQLRulesVX view does not exist by default and can be created using TSET with below steps:<\/p>\n
Start TSET<\/p>\n
goto to Tools->options<\/p>\n
Check on use X views (DBC and QCD) and press OK<\/p>\n\n
<\/div>\n