Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 164 165 166 167 168 169 < 170 > 171 172 173 174 175 176 .. 218 >> Next

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.

Rules Validation

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 Administration Chapter 14: Tracking Processing Behavior with the Database Query Log (DBQL)

Logging Scenarios

Logging Scenarios

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 - 23 Chapter 14: Tracking Processing Behavior with the Database Query Log (DBQL)

Logging Scenarios

IF .

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

THEN .

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
Previous << 1 .. 164 165 166 167 168 169 < 170 > 171 172 173 174 175 176 .. 218 >> Next