Download (direct link):
3 Grant EXECUTE privilege to your database administrator user: GRANT EXECUTE ON DBQLAccessMacro TO DBAdmin ;
4 Log off the DBC or SystemFE session.
5 Log on again as user DBAdmin.
6 Define query logging rules for one or more users and/or one or more accounts using Teradata SQL BEGIN QUERY LOGGING statements. (For full syntax, see Teradata RDBMS SQL Reference, Volume 4.)
7 Check the DBQLRules view to see if the rules are correct: SELECT * FROM DBC.DBQLRules ; If you find an error, submit an END QUERY LOGGING statement for that user and define a new BEGIN QUERY LOGGING statement.
When DBQL logging has begun, the rules are validated as follows:
IF . THEN DBQL .
a user logs on to start a session checks the logon string against the user and/ or account in the rules cache.
a match is found in the rules cache logs according to any options in the rules table.
14 - 10 Teradata RDBMS Database Administration
Chapter 14: Tracking Processing Behavior with the Database Query Log (DBQL)
Logging Implementation and Validation
IF . THEN DBQL .
a match is not found in the rules cache searches for a matching user and/or account in the Data Dictionary.
a match is found in the Data Dictionary creates an entry in the rules cache and logs according to any options in the rules table.
a match is not found in the Data Dictionary creates a rule in the rules cache but does not perform logging.
14 - 10
Teradata RDBMS Database AdministrationChapter 14: Tracking Processing Behavior with the Database Query Log (DBQL)
When you enable logging for a user, one row is logged in DBQLogTbl for each query during a session that is validated at logon time (unless you specified the SUMMARY or THRESHOLD option; for an explanation, see "DBQLSummaryTbl" on page 14-12).
The following table offers examples of the type of data that will be logged according to the rules you defined and the behavior of the query.
IF . THEN .
you log on as the authorized user DBC (or your DBA user) and attempt to DROP, UPDATE or ALTER any DBQL object the statement fails with an access error: Failure 3523 (username does not have statement permission)
you log on as an unauthorized user (not DBC or DBAdmin) and submit a BEGIN QUERY LOGGING statement the BEGIN QUERY LOGGING statement fails with an access error: Failure 3523 (username does not have statement permission)
you want to view all logged rows for a query use the CollectTimeStamp and ProcID fields to join DBQLogTbl rows with (depending on the rules for the user) DBQLObjTbl, DBQLStepTbl, and/or DBQLSQLTbl rows.
the cache becomes over 80% full • A flush cache routine is called that: - Saves the segment data - Timestamps the segment • DBQL logging continues
you disable query logging for a user running a session that is being logged no more rows are cached for that session or that user.
you abort a session that is being logged the AbortFlag value is set to T in the DBQLogTbl row for the query.
you begin query logging with no options for a user, and that user subsequently logs on and runs a query a default row is logged for that user in DBQLogTbl.
you begin query logging for a specific account (define a rule) for a user, and that user logs on under an account that does not match the rule no rows are logged for any queries run during that session.
Teradata RDBMS Database Administration
14 - 23Chapter 14: Tracking Processing Behavior with the Database Query Log (DBQL)
you begin query logging for a specific account for a user, but the account does not exist
you begin query logging for a specific account for a user, and the account includes ASE codes
you begin query logging for a user and that user runs the same query twice during one session
you define a rule for a user specifying OBJECTS
you define a rule with OBJECTS and the user runs a query that causes the Optimizer to reference the same object twice
you create a rule specifying SQLTEXT=nnn and the user executes a stored procedure query
you begin logging for a user with no options and the user runs a query with more than 200 characters
you create a rule for a user that specifies SQLTEXT= 0 and the user runs a query
the BEGIN QUERY LOGGING statement is accepted (accounts do not need to exist).
DBQLRules shows a rule for the user, but queries run by that user are not logged because the sessions never match the user/account pair.
both the input account string and the expanded account string are logged. (For details on ASE codes, see "Using Account String Expansion (ASE)" on page 5-27.)
two rows are logged in DBQLogTbl.
In the second row, the value in the CacheFlag field is set to T.
if the user runs a SELECT that joins two tables owned by the same database:
• One row for the query is logged in DBQLogTbl
• Rows are logged in DBQLObjTbl as follows:
- A row for the database